MS Access Link Table With VBA

13
votes

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.

3
ms-accessvbams-access-2010ms-access-2007linked-tables
I don't know the answer and I'm not trying to dissuade you, but is this so you can just click a button and sync the data as opposed to running the wizard? I would be hesitant to do this because of the nice built-in validation that comes natively with Access that you (may?) have to also code for. – Mark C.
Yes, that's exactly the reason for doing this. I have automated almost all my database manipulations in VBA except this one. I can just use the wizard but it seems like I'm at the last hurdle! – Mark

3 Answers

18
votes

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"
5
votes

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;
0
votes

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]