Relinking Tables Programmatically

  • Thread starter Thread starter CrisW
  • Start date Start date
C

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
 
CrisW said:
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
Click to expand...


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.
 
Ah, that makes perfect sense now...particularly the system tables which are
very much out of sight, out of mind. Thank you very much!
 
Back
Top