1 - Create a table tblLinkTables with the columns LinkTableName and LinkPath
(example shown below). You can also put this in an Excel workbook and save
it in the same directory as the Access application (tblLinktables.Xls). If
you choose this option, uncomment the section of code below that imports this
file.
tblLinkTables
LinkTableName LinkPath
tblAccounts \\server\dir1\dir2\dir3\LinkMdb1.Mdb
tblAcctType \\server\dir1\dir2\dir3\LinkMdb1.Mdb
tblAdjustment \\server\dir1\dir2\dir3\LinkMdb2.Mdb
tblAvailView \\server\dir1\dir2\dir3\LinkMdb3.Mdb
tblCCY \\server\dir1\dir2\dir3\LinkMdb4.Mdb
2 - Create a public subroutine RelinkAllTables and enter the code below
Public Sub RelinkAllTables
' Declarations
Dim sSrcFile As String
Dim db As Database
Dim rst As Recordset
Dim lMsgResp As Long
On Error GoTo RelinkAllLinkTables_Err
DoCmd.SetWarnings False
'DoCmd.RunSQL ("DELETE * FROM tblLinkTables")
'DoCmd.SetWarnings True
' Import the data
'sSrcFile = AppPath & "\" & "tblLinkTables.xls"
'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
"tblLinkTables", sSrcFile, True
' Go through the data and relink all tables.
Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM tblLinkTables", dbOpenDynaset)
rst.MoveLast: rst.MoveFirst
If rst.RecordCount = 0 Then
lMsgResp = MsgBox("There are no records in tblLinkTables",
vbCritical + vbOKOnly)
Else
rst.MoveFirst
While Not rst.EOF
If TableExists(rst.Fields("LinkTableName").Value) Then
DoCmd.DeleteObject acTable, rst.Fields("LinkTableName").Value
DoCmd.TransferDatabase acLink, "Microsoft Access",
rst.Fields("LinkPath").Value, acTable, _
rst.Fields("LinkTableName").Value,
rst.Fields("LinkTableName").Value
rst.MoveNext
Wend
End If
RelinkAllLinkTables_Exit:
Set rst = Nothing
Set db = Nothing
Exit Sub
RelinkAllLinkTables_Err:
MsgBox "Error #: " & Err.Number & vbLf & _
"Error Description: " & Err.Description, vbOKOnly + vbExclamation,
"RelinkAllLinkTables"
Resume RelinkAllLinkTables_Exit
End Sub
3 - Run this subroutine. It will relink all of the tables you have
specified in tblLinkTables.