MS Access – Link tables with VBA

Just posting this here because I can never remember it myself. I can also never find it online either so it might be simple and handy to others too…

Sub LinkTables()
    Dim tbl As New TableDef
    Dim ConnectString As String

    ConnectString = "ODBC;DRIVER=SQL Server;SERVER=myServerName;DATABASE=myDatabaseName;uid=myUserID;pwd=myPa$$w0rd"

    Set tbl = New TableDef
    On Error Resume Next

    tbl.Name = "destinationTableName"
    tbl.SourceTableName = "sourceTableName"
    tbl.Connect = ConnectString
    CurrentDb.TableDefs.Append tbl
End Sub
Advertisements

One thought on “MS Access – Link tables with VBA

  1. And for mulltiple tables:

    Sub LinkTables()
    Dim tbl As New TableDef
    Dim ConnectString As String
    Dim Tables As String
    Dim TablesArray As Variant
    Dim x As Integer

    ‘CONFIG————————–
    ConnectString = “ODBC;DRIVER=SQL Server;SERVER=myServerName;DATABASE=myDatabaseName;uid=myUserID;pwd=myPassword”
    Tables = “tableName1,tableName2,tableName3,andSoOn”
    ‘——————————–

    TablesArray = Split(Tables, “,”)

    For x = LBound(TablesArray) To UBound(TablesArray)

    Set tbl = New TableDef
    On Error Resume Next

    tbl.Name = TablesArray(x)
    tbl.SourceTableName = “dbo.” & TablesArray(x)
    tbl.Connect = ConnectString
    CurrentDb.TableDefs.Append tbl

    Debug.Print “Linked table: ” & TablesArray(x)

    Next
    End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s