Click here to Skip to main content
15,884,099 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 4-Jan-24 9:28am
v2

1 solution

If you want to add linked table to MS SQL Server, you need to add ODBC data source first: Administer ODBC data sources - Microsoft Support[^]

How to link table? Please read this: Import or link to data in an SQL Server database - Microsoft Support[^]

On the other hand, you don't need to link tables. You can use ACE.Oledb to be able to provide any CRUD operation. See: Access connection strings - ConnectionStrings.com[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900