Software

Ask something
Discussion Locked

ACCESS 2000 LINKED TABLE

0 helpful
1 reply

by gw0001

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can anyone tell me how to link to a table in another database that may get moved and automatically update link. Both databases will stay together in the same directory, but the two of them will get moved occasionally.

Thanks

1 reply

by henry.lasher

Call the following function either as an Autoexec macro or as an event procedure on a form.

Function AttachTable()
Dim msg As String, DBType As String
Dim MyDB As Database, tbl As TableDef
Dim MyNew_connect As String, my_dir As String
Dim ss As Integer, ii As Integer, LL As Integer, xx As Integer
DoCmd.Hourglass True
On Error GoTo Attach_tbl_Err
Set MyDB = DBEngine(0)(0)
ss = 1
Do Until ss = 0
LL = ss + 1
ss = InStr(LL, MyDB.Name, "\")
Loop
my_dir = Mid$(MyDB.Name, 1, LL - 1)
For ii = 0 To MyDB.TableDefs.Count - 1
Set tbl = MyDB.TableDefs(ii)
If tbl.Connect <> "" Then ' Skip My DB tables.
ss = InStr(tbl.Connect, "=") 'find end of db type
DBType = Left$(tbl.Connect, ss)
Do Until ss = 0
LL = ss + 1
ss = InStr(LL, tbl.Connect, "\")
Loop
MyNew_connect = DBType & my_dir & Mid$(tbl.Connect, LL)
If (MyNew_connect) = tbl.Connect Then GoTo sKip_refreshlink:
tbl.Connect = MyNew_connect
tbl.RefreshLink
End If
sKip_refreshlink:
Next ii
Attach_tbl_Exit:
DoCmd.Hourglass False
Exit Function
Attach_tbl_Err:
Resume Next
End Function

This discussion is closed to new replies.