Object Invalid or no longer set (3420) error refreshing links

[Dale Fye]

I'm relatively new to 2007, so this may be an issue I'm unfamiliar with.

I've got some code in my application that links tables from a backend, if
the tables are not already present, but refreshes the link if the table is
present. The linking part is working fine. But I get the error mentioned
above on the line marked with an asterisk in the following code, if the table
exists.

Set db = DBEngine.OpenDatabase(strBackend)
DoCmd.Hourglass True

'Loop through each table in the backend that starts with 'tbl_'
For Each tdfSource In db.TableDefs

If InStr(tdfSource.Name, "tbl_") = 1 Then

'On Error Resume Next
If TableExists(tdfSource.Name) Then
Set tdfDest = CurrentDb.TableDefs(tdfSource.Name)
* tdfDest.Connect = ";DATABASE=" & strBackend
tdfDest.RefreshLink
Else
Set tdfDest = CurrentDb.CreateTableDef(tdfSource.Name)
tdfDest.Connect = ";DATABASE=" & strBackend
tdfDest.SourceTableName = tdfSource.Name
CurrentDb.TableDefs.Append tdfDest
End If
End If
Next

I've used code similar to this many times in the past, but cannot figure out
what is causing this error.

--
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Douglas J. Steele]

Try changing all your uses of CurrentDb to db.

 

[Dale Fye]

Doug,

db is the backend database.

In one of Allen Brownes posts from a while back, he indicated that it
actually speeds up the process of linking if you open the backend and keep it
open until the process is complete.

I'll try declaring another database object and assigning it to the currentdb.

--
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Thanks, Doug

Don't know why, but creating another database variable and setting it equal
to Currentdb did the trick.

--
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.