In updating an application shared across a LAN recently, I came upon a need to dynamically re-link table(s) amongst back-end DB's. Having consulted (searched) online with June7, Albert Kallal and Dirk Goldgar, I developed a VBA general module for the app that I thought I'd share here for anyone that might be interested or have a need. (Just wanting to "give back")
Error situations were not encountered during development or after distributing app's mde on the LAN computers, so a heads-up about that.
Code:
Option Compare Database
Option Explicit
Public Sub ReLink(FileName As String, TableName As String)
Dim tdf As TableDef
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Re-link the table "TableName" from the current back-end DB to the DB whose name is
' passed as "FileName". ("FileName" must be fully qualified.)
'
' Note that any of the back-end files involved in the link process must be within the
' same folder.
'
' Also note that it is essential that it must be verified that the table at hand in fact
' be a "linked table" before it is deleted, lest the table and all of its data be deleted.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Don't delete "TableName" table UNLESS IT IS A LINKED TABLE.
On Error GoTo Err_Deletion
If Len(CurrentDb.TableDefs(TableName).Connect) > 0 Then
DoCmd.DeleteObject acTable, TableName
End If
' Okay. Link to table "TableName" on specified back-end DB "FileName".
On Error GoTo Err_Linking
Set tdf = CurrentDb.CreateTableDef(TableName)
tdf.SourceTableName = TableName
tdf.Connect = "; DATABASE=" & FileName
CurrentDb.TableDefs.Append tdf
Exit Sub
Err_Deletion:
MsgBox "Error encountered deleting the link to table " & TableName & vbNewLine & _
"in database " & CurrentDb.Name & vbNewLine & _
"Error #: " & Err.Number & ": " & Err.Description & vbNewLine & _
"Will attempt to continue processing."
Resume
Err_Linking:
MsgBox "Error encountered linking to table " & TableName & " in" & vbNewLine & _
"back-end database file: " & FileName & vbNewLine & _
"Error #: " & Err.Number & ": " & Err.Description & vbNewLine & _
"Sorry, cannot run application until this issue is resolved."
DoCmd.Quit
End Sub