There are numerous instances in which it can be useful to be able to retrieve a linked table’s path and filename, things like:

• for linkings at the startup
• for locking down the database so it will only run from certain instances
• etc…

The good news is that this is pretty easy to achieve once you know that the each table’s definition stores this information within the Connect property or that you can query the MSysObjects table for this information.

Extracting the Linked Table Path from the Connect Property

Below is an example of a function that can be used which extracts the path and filename from the linked table’s definition Connect property.

'---------------------------------------------------------------------------------------
' Procedure : GetLinkedTablePath
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the Linked Table BE path and filename
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName: Table name to retrieve the back-end path and filename of
'
' Usage:
' ~~~~~~
' GetLinkedTablePath("Contacts")
'   Returns -> C:\Databases\Master\ERP.accdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2014-10-16              Initial Release
' 2         2020-12-05              Updated Error Handler and Header
'---------------------------------------------------------------------------------------
Public Function GetLinkedTablePath(sTableName As String) As String
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim tdf                   As DAO.TableDef
    Dim aConnection()         As String
    Dim i                     As Long
 
    Set db = CurrentDb
    Set tdf = db.TableDefs(sTableName)
    aConnection = Split(tdf.Connect, ";")
    For i = 0 To UBound(aConnection)
        If Left(aConnection(i), 9) = "DATABASE=" Then
            GetLinkedTablePath = Mid(aConnection(i), 10)
        End If
    Next i
 
Error_Handler_Exit:
    On Error Resume Next
    If Not tdf Is Nothing Then Set tdf = Nothing
    If Not db Is Nothing Then Set db = Nothing
    Exit Function
 
Error_Handler:
    If Err.Number <> 3265 Then    'Err.Numer = 3265 Table doesn't exist
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: GetLinkedTablePath" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Function

Extracting the Linked Table Path from the MSysObjects table

Below is an example of a function that can be used which extracts the path and filename from the MSysObjects table.

'---------------------------------------------------------------------------------------
' Procedure : GetLinkedTablePath_MSysObjects
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Return the Linked Table BE path and filename
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Late Binding  -> none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName: Table name to retrieve the back-end path and filename of
'
' Usage:
' ~~~~~~
' GetLinkedTablePath_MSysObjects("Contacts")
'   Returns -> C:\Databases\Master\ERP.accdb
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2014-10-16              Initial Release
' 2         2020-12-05              Updated Error Handler and Header
'---------------------------------------------------------------------------------------
Public Function GetLinkedTablePath_MSysObjects(sTableName As String) As String
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String

    Set db = CurrentDb
    sSQL = "SELECT MSysObjects.Database " & vbCrLf & _
           "FROM MSysObjects " & vbCrLf & _
           "WHERE MSysObjects.Name = '" & sTableName & "' " & vbCrLf & _
           "GROUP BY MSysObjects.Database " & vbCrLf & _
           "HAVING (((MSysObjects.Database) Is Not Null));"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    With rs
        If .RecordCount <> 0 Then
            GetLinkedTablePath_MSysObjects = ![Database]
        End If
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetLinkedTablePath_MSysObjects" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

So as you can see, it is very straightforward to retrieve the path and filename associated with a linked table within Access!