Recently I had to link tables from one access file in another access file.

I did not intend to document such a trivial task on my webpage, but since the function I copied from the internet ran into an endless loop after I only provided an invalid file path I decided to implement something myself.


' @Author
'   Alexander Bolte
' @ChangeDate
'   2014-07-29
' @Description
'   Linking given table from given source Access database in CurrentDb under provided target name.
'   If a linked table already exists under given name, the link will be broken and replaced with the new one.
' @Param strDatabaseSource
'   String providing the full path of the source access database.
' @Param strTableSource
'   String providing the source table name in the source access database.
' @Param strTableDestination
'   String providing the target table name in CurrentDB.
' @Return
'   True, if linkage succeeded, else false.
Public Function LinkTable( _
        ByVal strDatabaseSource As String, _
        ByVal strTableSource As String, ByVal strTableDestination As String _
    ) As Boolean
    Dim dbSource As DAO.Database
    Dim dbTarget As DAO.Database
    Dim dbDestination As DAO.Database
    Dim tdf As DAO.TableDef
On Error GoTo LinkTable_Err

    Call unlinkTable(strTableDestination)
    Set dbSource = DBEngine.Workspaces(0).OpenDatabase(strDatabaseSource)
    Set dbDestination = CurrentDb
    Set tdf = dbDestination.CreateTableDef(strTableDestination)
    tdf.Connect = ";DATABASE=" & strDatabaseSource
    tdf.SourceTableName = strTableSource
    dbDestination.TableDefs.Append tdf
    LinkTable = True
    If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, cDialogTitle
LinkTable = False
    End If
    If Not (dbSource Is Nothing) Then
        Set dbSource = Nothing
    End If
    Set dbDestination = Nothing
    Set tdf = Nothing
End Function