Ho to connect Microsoft access 2021 to a SQL server and create linked tables, using VBA.

See more:

VBA

Access

Hello all,

Seeking guidance on linking Microsoft Access 2021 to SQL Server via VBA. While successfully retrieving table names, encountering error 3170 ("Could not find installable ISAM") when attempting to create linked tables. Any insights on resolving this issue?
below is my code:

What I have tried:

Sub ConnectToSQLServer()
    Dim conn As Object
    Dim rs As Object
    Dim strSql As String
    Dim strConn As String
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim tableName As String
    
    ' Connection string to connect to SQL Server
    strConn = "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=TestDataBase;User ID=user;Password=password;"
    
    ' Create a connection object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Open the connection
    conn.Open strConn
    
     ' Access Database object
    Set db = CurrentDb
    
    ' SQL query to retrieve table names
    strSql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"
    
    ' Create a recordset object and execute the query
    Set rs = conn.Execute(strSql)
    
    ' Loop through the recordset to get table names
    'Do While Not rs.EOF
        ' Display table names (replace MsgBox with desired processing)
      '  MsgBox rs("table_name")
     '   rs.MoveNext
  '  Loop
    
    ' Loop through the recordset to create linked tables in Access
    Do While Not rs.EOF
        tableName = rs.Fields(0).value
        
        ' Check if the table doesn't exist in Access
        'If Not TableExists(tableName, db) Then
            Set tdf = db.CreateTableDef(tableName)
            tdf.SourceTableName = tableName
            tdf.Connect = conn.connectionString
            db.TableDefs.Append tdf
       ' End If
        
        rs.MoveNext
    Loop
    
    
    ' Close the recordset and connection
    rs.Close
    conn.Close
    
    ' Clean up objects
    Set rs = Nothing
    Set conn = Nothing
    Set db = Nothing
    
    Exit Sub
    
    
    
End Sub