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.

Answer 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"