How To Have Access VBA Link Tables Programmatically

This table relinking procedure is a really good example of how to use VBA to link your tables programmatically, and how to have MS Access refresh linked tables automatically.

It allows re-linking all tables to a selected drive source (the folder the backend database is in).

In this example, all of the tables that need to be relinked are in the “LinkTables” table, and the backend database is in the “C:\Interface\20180314” folder:

IMPORTANT: All Backend databases must reside in the same path, and name of file listed in table. If the tables are not in the same backend database, then the code needs to be modified.

I am interacting with this procedure from a form that looks like this:

Here’s the code for the form:

Private Sub btnLinkTables_Click()

     RefreshLink "C", Me.txtBackendPath
     
End Sub

Private Sub Form_Load()
    Me.txtBackendPath = "C:\Interface\backend"
    
End Sub

Here’s the code of how to call the “RefreshLink” procedure:

Option Compare Database

Public Sub RefreshLink(BackendDrive, BackendPath)

    Dim strSourceDatabase As String
    Dim strSourceName As String
    Dim strLocalName As String
    Dim strSQL As String
    Dim strDBName As String
    Dim rs As Recordset
        
    Dim intPos As Integer
    Dim DriveLetter As String
    Dim FullPath As String
    Dim BlockName As String
    
    DoCmd.Hourglass False
    
    DriveLetter = BackendDrive '"\\jlp-fs01\SB2\"
    
    strSQL = "SELECT SourceTableName, DestinationTableName, TableGroup, DateAdded, BackendDatabase, BackendDatabasePath, NetworkOnly FROM LinkedTables"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.MoveFirst
    
    Do Until rst.EOF
        strSourceName = rst.Fields("SourceTableName")
        strLocalName = rst.Fields("DestinationTableName")
        strBlockName = rst.Fields("TableGroup")
        strDBName = rst.Fields("BackendDatabase")
        strSourceDatabase = BackendPath & "\" & strDBName
        
        If Relink(strSourceName, strSourceDatabase, strLocalName, strLocalName) = False Then
            Set rst = Nothing
            Exit Sub
        Else
            'Provide user info on screen
            Forms![frmSystemConfiguration].lblStatus.caption = "Linking " & BlockName & ": " & strSourceName
            DoEvents
        End If
    
        rst.MoveNext
    Loop
    Set rst = Nothing
    
    'Tell the user the relink is complete
    Forms![frmSystemConfiguration].lblStatus.caption = "Linking tables now complete."
    DoEvents
        
 
    DoCmd.Hourglass False

End Sub

Function Relink(strTableName As String, strSourceDatabase As String, strSourceName As String, strLocalName As String) As Boolean
    On Error GoTo DoLink_Error:
    
    Dim dbs As dao.Database
    
    Relink = True
    
    'Open the backend database
    Set dbs = DBEngine.OpenDatabase(strSourceDatabase, False, False, ";pwd=abc")
    
    'Delete the table, if it exists
    CurrentDb.TableDefs.Delete strTableName
    Debug.Print "DELETE: " & strTableName
    CurrentDb.TableDefs.Refresh
        
    'Link the table
    DoCmd.SetWarnings False
    DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDatabase, acTable, strSourceName, strLocalName, False
    DoCmd.SetWarnings True
        
    Set dbs = Nothing
    Exit Function
    
DoLink_Error:
    Select Case Err.Number
        Case 3011
            MsgBox "Try Linking Again." & vbCrLf & Err.Description, vbCritical, APP_NAME
            Relink = False
            Exit Function
        Case Else
            Resume Next
    End Select
 
End Function

Here are the Front End and the Backend databases for you to practice with (Make sure your path is correct):

Here is the front end:
How To Have Access vba link tables programmatically.accdb

Here is the backend database:

Air.accdb

Let me know if you have any questions.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!