VBA Code to Re-link MS Access Link Tables

VBA Code to Re-link MS Access Link Tables

In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location

Excel Tricks

'This function loop through all linked tables in MS Access database
'and re-link them to new database path
Public Function UpdateLinkTables()
    '
    Dim strPath As String
    Dim objDatabase As Object
    Dim tblDef As TableDef
    '
    strPath = "E:\Work\ExcelSirJi\Database.accdb"
    '
    'Set the object to current database
    Set objDatabase = CurrentDb
    '
    'Go through each table in the database and check if it is linked table
    'if yes, then update new database path
    For Each tblDef In objDatabase.TableDefs
        If tblDef.SourceTableName <> "" Then
            'If it is hidden table then make it a visible table
            tblDef.Properties("Attributes").Value = 0
            'Change the database path to new path
            'if database requires password then you can un-comment password section in below code
            tblDef.Connect = ";DATABASE=" & strPath '& "; PWD=1234"
            'Refresh the table
            tblDef.RefreshLink
        End If
    Next
    '
    'Close the objects
    Set tblDef = Nothing
    Set objDatabase = Nothing
    '
End Function

 

To use this code in your MS Access tool, follow below steps:

1. Open the MS Access tool where you want to use this code
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module

Excel Tricks

5. In the above code I have hardcoded new database path in strPath variable but you can make this variable as input parameter of the function by making below changes highlighted in the screenshot (Red are deletion, Yellow are addition):

Excel Tricks

Author:
Excelsirji.com was started with the thought of making people experience easy while working with Excel, data analytics, vba, automations etc.

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you a Data Enthusiast?

Join us for a ride on your data and automate your stuffs

Business Analytics
Data Analyst
Programming for Data Science
Predictive Analytics for Business