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.
Enjoy.
' @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
LinkTable_Err: If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, cDialogTitle
Err.Clear
LinkTable = False
End If
If Not (dbSource Is Nothing) Then
dbSource.Close
Set dbSource = Nothing
End If
Set dbDestination = Nothing
Set tdf = Nothing
End Function