Relinking Tables Programmatically

[CrisW]

Hello, I have the following bit of code that partially works. It will relink
the first 6 tables (I have 10 in total), but then it errors out with "Invalid
Operation". Any thoughts?

Private Sub Command0_Click()
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim strConnect As String

strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & strConnect & "\be\be\funmaster_be.mdb"

Set db = CurrentDb
For Each tdf In db.TableDefs

tdf.Connect = strConnect
tdf.RefreshLink
Next tdf

Set tdf = Nothing
Set dbs = Nothing
End Sub

 

[Marshall Barton]

Hello, I have the following bit of code that partially works. It will relink
the first 6 tables (I have 10 in total), but then it errors out with "Invalid
Operation". Any thoughts?

Private Sub Command0_Click()
Dim db As dao.Database
Dim tdf As dao.TableDef
Dim strConnect As String

strConnect = CurrentProject.Path
strConnect = ";DATABASE=" & strConnect & "\be\be\funmaster_be.mdb"

Set db = CurrentDb
For Each tdf In db.TableDefs

tdf.Connect = strConnect
tdf.RefreshLink
Next tdf

Set tdf = Nothing
Set dbs = Nothing
End Sub

You should make sure the table is linked before relinking
it:

For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = strConnect
tdf.RefreshLink
End If
Next tdf

Note that will also avoid messing with the (hidden?) system
tables, which is probably why your code quit part way
through.

 

[CrisW]

Ah, that makes perfect sense now...particularly the system tables which are
very much out of sight, out of mind. Thank you very much!