Automatic reconnect of data tables in Access

Use Access to do the application, often made of "data" and "program" two databases, the "data" database only data tables, "program" database to store all the form,query,report,module, etc., through the table join the "program" Database can access all "data" The table in the database.

When our application replaces the storage location, it often loses the correct table join, causing a run error. The following section of the program can automatically retrieve the table join. In my Application The "program" database name is Stockmgr.mdb, the "data" database name is Stock-data.mdb
Note: This program is limited to "program" and "Data" stored in the same directory and corresponds to a single "data" file.

Function reattachtable ()
Dim MyDB as Database, mytbl as TableDef
Dim CPath as String
Dim datafiles as String, I as Integer

On Error Resume Next
Set MyDB = CurrentDb
CPath = Trimfilename (currentdb.name)
datafiles = "Stock-data.mdb"
Docmd.hourglass True
For i = 0 to Mydb.tabledefs.count-1
Set mytbl = mydb.tabledefs (i)
If mytbl.attributes = db_attachedtable and Left (mytbl.connect, 1) = ";" Then
Mytbl.connect = ";D atabase=" & CPath & Datafiles
Mytbl.refreshlink
If ERR Then
If vbno = MsgBox (Err.Description &), continue? ", vbYesNo) Then Exit for
End If
End If
Next I
Docmd.hourglass False
MsgBox "Tables relink finish."
End Function

' Remove filename from absolute path, return path
Function Trimfilename (fullname As String) as String
Dim Slen as long, I as long
Slen = Len (fullname)
For i = Slen to 1 Step-1
If Mid (FullName, I, 1) = "\" Then
Exit for
End If
Next
Trimfilename = Left (fullname, i)
End Function

Called in the Program startup or button action.