MS Access Link Table With VBA

Question

How can I link a table from one MS Access Database (*.mdb or *.accdb) to another Access database in VBA?

Basically I just was to use VBA to replicate what the External Data wizard does.

I've googled this and I see many examples of how to update or relink tables and many examples of linking to SQL databases, but very few of simple linking tables between Access databases.

Solution 1

You can use the DoCmd.TransferDatabase Method to create a link to a table in another Access database.

DoCmd.TransferDatabase TransferType:=acLink, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:="C:\share\Access\Example Database.accdb", _
        ObjectType:=acTable, _
        Source:="Addresses", _
        Destination:="Addresses_link"

I included the option names hoping that would make it easier to track which option is which. But if that seems too verbose, you can omit the option names and do it all on one line:

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\share\Access\Example Database.accdb", acTable , "Addresses", "Addresses_link"

Solution 2

It's actually pretty easy--you just create a new tabledef and set its .connect property to an ODBC connection string that links to the other Access database.

Private Function LinkTable(LinkedTableName As String, TableToLink As String, connectString As String) As Boolean

    Dim tdf As New dao.TableDef

    On Error GoTo LinkTable_Error

    With CurrentDb

        .TableDefs.Refresh

        Set tdf = .CreateTableDef(LinkedTableName)
        tdf.Connect = connectString
        tdf.SourceTableName = TableToLink
        .TableDefs.Append tdf
        .TableDefs.Refresh


    End With

    Set tdf = Nothing
End Function

The connection string would look something like this (taken from connectionstrings.com):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Persist Security Info=False;