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]