Trouble Accessing Linked Tables in Access 2021 via OLEDB Connection: 'Could Not Find Installable ISAM' Error (1 Viewer)

wardah

New member
Joined
Dec 18, 2023
Messages
6
I'm currently working on pulling linked tables from a cloud-hosted SQL Server into Access 2021 using an OLEDB connection. While the connection appears successful, I've hit a roadblock—I can retrieve and print the names of the tables from the connected database onto the immediate screen, but I'm encountering an issue when attempting to access the actual tables within my Access application. Unfortunately, I keep encountering an error message stating "Could not find the installable ISAM." Below is the code I'm currently using:

Code:
Public Sub SQLServerConnection()
     Dim db As Object
    Dim conn As Object
    Dim rs As Object
    Dim strConn As String
    Dim strSQL As String
     Dim tableName As String
      Dim tdf As Object
   
    ' SQL Server OLEDB connection string
    strConn = "Provider=SQLOLEDB;Server=14.29.209.10;Database=data;UID=sa;PWD=pass;"
   
           
    ' Create a new connection
    Set conn = CreateObject("ADODB.Connection")
    conn.Open strConn
   
     ' Open Access database
    Set db = CurrentDb()
   
    ' Query to retrieve table names
    strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';"
   
    ' Execute the query
    Set rs = conn.Execute(strSQL)
   
    ' Loop through the recordset to get table names
    Do While Not rs.EOF
        'Debug.Print rs("TABLE_NAME").value ' Output table names to the Immediate Window (Ctrl + G to view in VBA Editor)
         tableName = rs("TABLE_NAME").value
       
        ' Create linked tables in Access
        db.TableDefs.Refresh
        'db.TableDefs.Delete tableName ' Delete existing linked table if it exists
       
        ' Create linked table using DoCmd.TransferDatabase
        DoCmd.TransferDatabase acLink, "ODBC Database", strConn, acTable, tableName, tableName
       
        rs.MoveNext
       
    Loop
   
    ' Close the connection and clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
     MsgBox "Linked tables created successfully!"
   
End Sub
 
Last edited:
Try downloading and installing the MSODBC18 driver, and using that instead of the SQLOLEDB driver.

Make sure you choose the one that matches your bitness of Access (ie if you use 32bit Access choose the x86 version, if you use 64bit Access then go for the x64 version).

Your connection string will look more like:
Code:
ODBC;DRIVER={ODBC Driver 18 for SQL Server};PROVIDER=MSDASQL;SERVER=124.29.208.110;DATABASE=ERP_Remoxin;UID=sa;PWD=zeetech@123;
 
Thanks for prompt response.
But i do not want to use ODBC driver as we will have to install and configure the correct ODBC driver on clients machine as well. We intend to distribute the frontend as a single installable file with Access runtime.
 
Thanks for prompt response.
But i do not want to use ODBC driver as we will have to install and configure the correct ODBC driver on clients machine as well. We intend to distribute the frontend as a single installable file with Access runtime.
Have a look at this.
It does allow for the installation of other files?

However only Free for non-commercial use.
 
But i do not want to use ODBC driver as we will have to install and configure the correct ODBC driver on clients machine as well.
You need an ODBC driver for linked tables. OLEDB only runs via ADODB.

Code:
' SQL Server OLEDB connection string
    strConn = "Provider=SQLOLEDB;Server=14.29.209.10;Database=data;UID=sa;PWD=pass;"
' SQL Server ODBC connection string
    strOdbcCon = "ODBC;DRIVER={SQL Server};DATABASE=data;SERVER=14.29.209.10;UID=sa;PWD=pass;"
' {SQL Server} ... this is the old ODBC driver that comes with Windows.

[...]
        DoCmd.TransferDatabase acLink, "ODBC Database", strOdbcCon, acTable, tableName, tableName

BTW: "UID = sa" ... is ideal for hackers. ;)
 
You need an ODBC driver for linked tables. OLEDB only runs via ADODB.

Code:
' SQL Server OLEDB connection string
    strConn = "Provider=SQLOLEDB;Server=14.29.209.10;Database=data;UID=sa;PWD=pass;"
' SQL Server ODBC connection string
    strOdbcCon = "ODBC;DRIVER={SQL Server};DATABASE=data;SERVER=14.29.209.10;UID=sa;PWD=pass;"
' {SQL Server} ... this is the old ODBC driver that comes with Windows.

[...]
        DoCmd.TransferDatabase acLink, "ODBC Database", strOdbcCon, acTable, tableName, tableName

BTW: "UID = sa" ... is ideal for hackers. ;)

Thanks for the response... Let me explore this solution further.

P.S I made a horrible mistake... "UID = sa" :(
 

Users who are viewing this thread

Top Bottom