Relinking Tables Programmatically

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
 
M

Marshall Barton

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


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.
 
C

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top