Software

Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion

Locked

ACCESS 2000 LINKED TABLE

By GW0001 ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by henry.lasher In reply to ACCESS 2000 LINKED TABLE

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

Related Discussions

Related Forums