Help with Dynamic Table rRe-Linking

L

LarryP

MSAccess 2003 on Windows XP Pro.

Function below fails at the <tdfLinked.Connect = > step. Throws a "runtime
error 3420, Object invalid or no longer set." FYI, SessionFilePath is a
global variable containing the first part of the filepath for the table link,
while rsLinkedTables!xxxxxxxxxx is the second part of the filepath, down to
the actual filename. (E.g., SessionFilePath = "\\aaaa\bbb\cccccccc\", and
rsLinkedTables!xxxxxxxxx = "ddd\eeeee\fff ggggg.mdb") Who can tell me
what I'm doing wrong?


Public Function SetLinksBySite()
Dim tdfLinked As TableDef, rsLinkedTables As Object
DoCmd.RunSQL "UPDATE tblLinkedTables SET OffsiteLinkSet = False"
Set rsLinkedTables = CurrentDb.OpenRecordset("tblLinkedTables")
rsLinkedTables.MoveFirst
Do While Not rsLinkedTables.EOF
Set tdfLinked = CurrentDb.TableDefs(rsLinkedTables!TableName)
If SessionSite = "Tucson" Then
tdfLinked.Connect = "DATABASE=" & SessionFilePath &
rsLinkedTables!TucsonTableLink
Else
tdfLinked.Connect = "DATABASE=" & SessionFilePath &
rsLinkedTables!OffsiteTableLink
End If
tdfLinked.RefreshLink
rsLinkedTables!TableLinkRefreshed = True
rsLinkedTables.MoveNext
Loop
End Function
 
D

Dirk Goldgar

LarryP said:
MSAccess 2003 on Windows XP Pro.

Function below fails at the <tdfLinked.Connect = > step. Throws a
"runtime
error 3420, Object invalid or no longer set." FYI, SessionFilePath is a
global variable containing the first part of the filepath for the table
link,
while rsLinkedTables!xxxxxxxxxx is the second part of the filepath, down
to
the actual filename. (E.g., SessionFilePath = "\\aaaa\bbb\cccccccc\", and
rsLinkedTables!xxxxxxxxx = "ddd\eeeee\fff ggggg.mdb") Who can tell me
what I'm doing wrong?


Public Function SetLinksBySite()
Dim tdfLinked As TableDef, rsLinkedTables As Object
DoCmd.RunSQL "UPDATE tblLinkedTables SET OffsiteLinkSet = False"
Set rsLinkedTables = CurrentDb.OpenRecordset("tblLinkedTables")
rsLinkedTables.MoveFirst
Do While Not rsLinkedTables.EOF
Set tdfLinked = CurrentDb.TableDefs(rsLinkedTables!TableName)
If SessionSite = "Tucson" Then
tdfLinked.Connect = "DATABASE=" & SessionFilePath &
rsLinkedTables!TucsonTableLink
Else
tdfLinked.Connect = "DATABASE=" & SessionFilePath &
rsLinkedTables!OffsiteTableLink
End If
tdfLinked.RefreshLink
rsLinkedTables!TableLinkRefreshed = True
rsLinkedTables.MoveNext
Loop
End Function
Click to expand...


You need to get your TableDef object from a declared Database object, not
from CurrentDb directly, because the object returned by CurrentDb goes out
of scope and is destroyed on the next line if no object variable is set to
it. Do this:


Dim db As DAO.Database
Dim tdfLinked As DAO.TableDef
Dim rsLinkedTables As DAO.Recordset

Set db = CurrentDb

DoCmd.RunSQL "UPDATE tblLinkedTables SET OffsiteLinkSet = False"

Set rsLinkedTables = db.OpenRecordset("tblLinkedTables")
With rsLinkedTables
Do Until .EOF
Set tdfLinked = db.TableDefs(!TableName)
If SessionSite = "Tucson" Then
tdfLinked.Connect = _
"DATABASE=" & SessionFilePath & !TucsonTableLink
Else
tdfLinked.Connect = _
"DATABASE=" & SessionFilePath & !OffsiteTableLink
End If
tdfLinked.RefreshLink
.Edit
!TableLinkRefreshed = True
.Update
.MoveNext
Loop
.Close
End With
 
L

LarryP

That came close, got me past the .Connect line problem, but now at the
<tdfLinked.RefreshLink> point it's complaining about not finding an
installable ISAM. I searched on that but none of the threads offered
anything I could relate to my specific situation. Any thoughts? I just got
a brand new computer and brand new install of Office XP Pro, so I should
think it has all the DLLs, add-ins, etc., that are currently available.
 
D

Dirk Goldgar

LarryP said:
That came close, got me past the .Connect line problem, but now at the
<tdfLinked.RefreshLink> point it's complaining about not finding an
installable ISAM. I searched on that but none of the threads offered
anything I could relate to my specific situation. Any thoughts? I just
got
a brand new computer and brand new install of Office XP Pro, so I should
think it has all the DLLs, add-ins, etc., that are currently available.
Click to expand...

I didn't look that closely before, but I think this:
tdfLinked.Connect = "DATABASE=" & SessionFilePath & ...
Click to expand...

needs to be this:

tdfLinked.Connect = ";DATABASE=" & SessionFilePath & ...
Click to expand...

Note the leading semicolon.
 
Top