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)
    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
            'Provide user info on screen
            Forms![frmSystemConfiguration].lblStatus.caption = "Linking " & BlockName & ": " & strSourceName
        End If
    Set rst = Nothing
    'Tell the user the relink is complete
    Forms![frmSystemConfiguration].lblStatus.caption = "Linking tables now complete."
    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
    'Link the table
    DoCmd.SetWarnings False
    DoCmd.TransferDatabase acLink, "Microsoft Access", strSourceDatabase, acTable, strSourceName, strLocalName, False
    DoCmd.SetWarnings True
    Set dbs = Nothing
    Exit Function
    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:


Let me know if you have any questions.

Offsite Related Information:

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Tags: , ,