MS Access Access Table with VBA

Question

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

I basically just used VBA to replicate what the external data wizard does.

I searched for this and I see many examples of how to update or rewind tables and many examples of linking to SQL databases, but very few 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 option names, hoping it would be easier to keep track of which one is. But if this is 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" 

Answer 2

This is actually quite simple: you simply create a new tabledef and set its .connect property to the ODBC connection string, which refers to another 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 

Final function

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

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