Link SQL tables with Access 2003

Question

I need to link SQL tables that I have in SQL Server Management Studio with Access 2003 application. Before that, I need to delete those access tables first. The code that I have for linking SQL tables is:

Function LinkODBC()

    Dim db As DAO.Database
    Dim tableDef As DAO.tableDef
    Dim connString As String
    Dim SQLTableName As String
    Dim AccessTableName As String
   
    Set db = CurrentDb()
    SQLTableName = "dbo.tblARInvoiceDetail"
    AccessTableName = "tblARInvoiceDetail"
    connString = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=192.168.0.4;Database=sanford;Trusted_Connection=Yes;UID=sa;PWD=tv$akP4O30HM1TO2!9lI2z6c"
 
    ' Deletes Access table if exsists (Uses Access Table Name)
    db.TableDefs.Refresh
    For Each tableDef In db.TableDefs
        If tableDef.Name = AccessTableName Then
            db.TableDefs.Delete tableDef.Name
            Exit For
        End If
    Next tableDef
   
    Set tableDef = db.CreateTableDef(AccessTableName)
   
    ' If the password is provided in the ODBC Connection string save it.
    If InStr(connString, "PWD=") Then
        tableDef.Attributes = dbAttachSavePWD
    End If
   
    ' Actually link table
    tableDef.SourceTableName = SQLTableName
    tableDef.Connect = connString
    db.TableDefs.Append tableDef

End Function

I need to loop through all the tables that are listed in one table: SysTrafficLinkTbls, and there are more than 500 tables.

Answer 1

Could be as simple as:

Sub LinkODBC()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim connString As String
    Dim rs As DAO.Recordset
   
    Set db = CurrentDb()
    connString = "ODBC;Driver={ODBC Driver 17 for SQL Server};Server=192.168.0.4;Database=sanford;Trusted_Connection=Yes;UID=sa;PWD=tv$akP4O30HM1TO2!9lI2z6c"
    Set rs = db.OpenRecordset("SELECT FieldWithTableName FROM SysTrafficLinkTbls")
    Do While Not rs.EOF
        ' Deletes Access table if exists
        db.TableDefs.Delete rs!FieldWithTableName
        ' Link table
        Set td = CurrentDb.CreateTableDef(rs!FieldWithTableName, dbAttachSavePWD, "dbo." & rs!FieldWithTableName, connString)
        db.TableDefs.Append td
        rs.MoveNext
    Loop
End Sub