Linked tables - changing the link
I am getting a strange error when trying to re-link tables using VBA. If I
address a table definition explicitly and individually I can alter the
connect property to point to a new database. However if I try and loop
through each table definition using a 'For Each' statement I get an
'Invalid operation' error on the tdf.connect = line.
The following works fine;
Public Sub Link_Table(strClient As String)
Dim dbs As Database, tdf As TableDef
Dim strPath As String
Dim strFile As string
strPath = ";Database=h:\Data base\"
strFile = strPath & "HMS_" & strClient & ".mdb"
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblWaves")
tdf.Connect = strPath & strFile
tdf.RefreshLink
Set dbs = Nothing
End Sub
however the following comes up with an 'Invalid Operation' error on the
'tdf.connect=.....' line.
Public Sub Link_Tables(strClient As String)
Dim dbs As Database, tdf As TableDef, lngFlag As Long
Dim strTblname As String
Dim strPath As String
Dim strFile As String
Set dbs = CurrentDb
strPath = ";Database=h:\Data base\"
strFile = "HMS_" & strClient & ".mdb"
For Each tdf In dbs.TableDefs
tdf.Connect = ";Database=H:\data base\" & strFile
tdf.RefreshLink
Next tdf
Set dbs = Nothing
End Sub
What am I doing wrong?
