MS Access Link Table With VBA

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"

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 = NothingEnd 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;

DataWriter, are you not mixing DAO with ADO. That's like water & oil. The connectionstring is OLEDB (which is ADO) and TableDef is from CurrentDb (which is DAO).

The Connect Property of a TableDef is in the form of:

;DATABASE=[Full path to Db]\[Db Name]