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:
