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.