Although we have GUI tools to create Linked tables, at times, it is simply more convenient to do so via VBA and automate the process.

So I thought I’d quickly share my routine for doing so. I’ve made it primarily for 2 usage scenarios, creating linked tables to:

• Microsoft Access databases
• SQL Server/Azure SQL databases

The Code

Below is the procedure for creating linked tables in an Access database via VBA.

'---------------------------------------------------------------------------------------
' Procedure : LinkedTable_Create
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Create a linked table from code
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sSrcTable         : Name of the table in the source database, for ODBC use the
'                     'schema.tablename' format
' sConnect          : Connection string to use
'                     For Access databases, simply the filename 'C:\..\..\DbName.accdb'
' sAccessTableName  : Name of the table to be create within Access.  Omit if same as sSrcTable
' bIsAccessDb       : (True/False) Linking tables to an Access database back-end
' sAccessDbPwd      : If bIsAccessDb = True, the Access database password, if one exists
'
' Usage Examples:
' ~~~~~~~~~~~~~~~
' Link to a standard Access database table
' LinkedTable_Create "employees","C:\Users\Daniel\Desktop\Employees2K7.accdb", ,True
'
' Link to a standard Access database table, giving the linked table a new name
' LinkedTable_Create "employees","C:\Users\Daniel\Desktop\Employees2K7.accdb", "myEmployees", True
'
' Link to a standard encrypted Access database table
' LinkedTable_Create "titles","C:\Users\Daniel\Desktop\Employees2K7WPwd.accdb", , True, "TheDbPwd"
'
' Link to an Azure database table - always use the latest driver available!
' LinkedTable_Create "lst_titles", _
'                    "ODBC;Description=Azure_SQL_DSN;DRIVER=ODBC Driver 18 for SQL Server;" & _
'                    "SERVER=YourServer.database.windows.net;Trusted_Connection=No;APP=Microsoft® Windows® Operating System;" & _
'                    "DATABASE=YourDbName;Network=DBMSSOCN;Address=YourServer.database.windows.net,1433;Encrypt=Yes;"
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2001-01-17
' 2         2021-03-26              Mod for SQL Server/Azure
' 3         2024-05-13              Updated header and error handler
'---------------------------------------------------------------------------------------
Function LinkedTable_Create(ByVal sSrcTable As String, _
                            ByVal sConnect As String, _
                            Optional sAccessTableName As String, _
                            Optional bIsAccessDb As Boolean = False, _
                            Optional sAccessDbPwd As String) As Boolean
    On Error GoTo Error_Handler
    Dim oDB                   As DAO.Database 'SHOV!
    Dim oTdf                  As DAO.TableDef

    'If not alternate name is provided they should then match
    If sAccessTableName = "" Then sAccessTableName = sSrcTable
    'deal with server schema using . which isn't allowable in Access table names
    sAccessTableName = Replace(sAccessTableName, ".", "_")
    
    'Build the connection string for an Access database
    If bIsAccessDb Then
        sConnect = ";DATABASE=" & sConnect
        If sAccessDbPwd <> "" Then sConnect = "MS Access;PWD=" & sAccessDbPwd & sConnect
    End If

    Set oDB = CurrentDb
    Set oTdf = oDB.CreateTableDef(sAccessTableName)
    With oTdf
        .Connect = sConnect
        .SourceTableName = sSrcTable
    End With

    DoCmd.SetWarnings False
    oDB.TableDefs.Append oTdf

    LinkedTable_Create = True

Error_Handler_Exit:
    On Error Resume Next
    DoCmd.SetWarnings True
    Application.RefreshDatabaseWindow
    Set oTdf = Nothing
    Set oDB = Nothing
    Exit Function

Error_Handler:
    If Err.Number = 3110 Then
        Resume Error_Handler_Exit
    Else
        '3011         The Microsoft Access database engine could not find the object 'TableName'.
        '             Make sure the object exists and that you spell its name and the path name correctly.
        '             If 'TableName' is not a local object, check your network connection or contact the
        '             server administrator.
        '3012         Object 'TableName' already exists.
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Source: LinkedTable_Create" & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
        Resume Error_Handler_Exit
    End If
End Function

Usage Examples Of Creating Linked Tables With VBA

UnEncrypted Access Back-end

LinkedTable_Create "employees","C:\Users\Daniel\Desktop\SaleTracking.accdb", ,True

Encrypted Access Back-end

LinkedTable_Create "employees","C:\Users\Daniel\Desktop\SaleTracking.accdb", ,True, "TheDbPassword"

Azure SQL Database Back-end

LinkedTable_Create "dbo.employees", _
                     "ODBC;Description=Azure_SQL_DSN;DRIVER=ODBC Driver 18 for SQL Server;" & _
                     "SERVER=YourServer.database.windows.net;Trusted_Connection=No;APP=Microsoft® Windows® Operating System;" & _
                     "DATABASE=YourDbName;Network=DBMSSOCN;Address=YourServer.database.windows.net,1433;Encrypt=Yes;"

Whenever you are creating linked tables to other sources than an Access back-end always be sure to use the most recent ODBC driver.