So I have an Access database and a new Azure SQL Server that I'm trying to connect to (via a linked table) for a proof of concept. First off, I can create a linked table right now. That's obviously not why I'm posting. I'm posting because I much prefer to create linked tables using a "DSN-less" method, because it just works better IMO (MS would seem to agree by reading their article): https://learn.microsoft.com/en-us/office/troubleshoot/access/create-dsn-less-connection-linkted-table This method works great for a local SQL Server on our network. However, for some reason it just does not seem to want to work if the SQL Server is Azure. Even when I KNOW I have the correct connection string, because it's working if I use it to create a DSN, I still get "Could not find installable ISAM" if I attempt to use the exact same connection string and the method in the above article. Also, I'm using the ODBC connection string directly from the "Connection Strings" tab found in the Azure portal for the DB in question. Any idea why the "DSN-less" method fails when used with a SQL Azure DB?

For Azure SQL Database you need to use a newer ODBC driver than the old Windows "{SQL Server}" driver. I tried that code and it generated multiple errors (which I had to use ODBC Trace to see) when trying to create the tableDef. like DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Reference to database and/or server name in 'master..sysdatabases' is not supported in this version of SQL Server. (40515) DIAG [S0002] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MSysConf'. (208) But when I changed the connection string to use the latest ODBC driver, it worked fine. eg stConnect = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword

Access DSN-Less connection SQL Azure - "Could not find installable ISAM"

Accepted answer

David Browne - msft 3,846 Reputation points

2020-12-18T23:17:30.707+00:00
For Azure SQL Database you need to use a newer ODBC driver than the old Windows "{SQL Server}" driver. I tried that code and it generated multiple errors (which I had to use ODBC Trace to see) when trying to create the tableDef. like

DIAG [37000] [Microsoft][ODBC SQL Server Driver][SQL Server]Reference to database and/or server name in 'master..sysdatabases' is not supported in this version of SQL Server. (40515) DIAG [S0002] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'MSysConf'. (208)

But when I changed the connection string to use the latest ODBC driver, it worked fine. eg

stConnect = "ODBC;DRIVER={ODBC Driver 17 for SQL Server};SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
Please sign in to rate this answer.

2 people found this answer helpful.
JG 1 Reputation point

2020-12-18T23:35:34.08+00:00

Thank you for this, David Browne. I was just getting ready to reply that I'd figured out it will work with "ODBC Driver 17 for SQL Server" & "SQL Server Native Client 11.0," but I couldn't get it to work for the old "SQL Server" driver. It's nice to know that it wasn't just me. Thanks, again!

Kris L 26 Reputation points

2020-12-21T21:08:21.03+00:00

SO close. Switching to the newer ODBC driver version did resolve the "Could not installable ISAM issue" error. Thanks again for the help with that!

I will mark your answer since it is a resolution for my initial problem but I'm hoping you might have some insight as to why I now have issues with it finding my table name (I swear I have the table name right). The error is number 3011 and is long but basically says it can't find the object.

Here is a snippet from my ODBC trace... hopefully it's relevant:

DIAG [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (0) DIAG [01S00] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0) DIAG [01000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'master'. (5701) DIAG [01000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed language setting to us_english. (5703)

David Browne - msft 3,846 Reputation points

2020-12-21T21:28:42.17+00:00

Perhaps it's not connected to your Database. Double-check your connection string, and ensure that the table is in the dbo schema.

Kris L 26 Reputation points

2020-12-22T13:49:12.727+00:00

Do you mean I don't have the front end connected to the database? Keep in mind that I can do all of this successfully through the New Data Source > From Database > From Azure Database wizard and select a DSN I have made. My problem only exists when attempting to create a DSN-less connection, which I think eliminates a lot of permissions or other possible issues. I have even verified that I can add/edit records through the linked table created from the wizard.

Thinking maybe I just needed to do "dbo_tblTestAzure" for the name, I even ran the code in the following post (1000 char limit is brutal) in an effort to get a name that worked:

The query returns the following, which does not work when used to try to create the link through VBA:
[dbo].[tblTestAzure]

Kris L 26 Reputation points

2020-12-22T13:49:23.717+00:00

strConnection = "Driver={ODBC Driver 17 for SQL Server};Server=MyServerName,1433;Initial Catalog=MyTestDBName;Uid=XXXX;Pwd=XXXX;" strSQL = "SELECT QUOTENAME(S.name) + '.' + QUOTENAME(T.name) AS table_name " & _ "FROM sys.tables T " & _ "INNER JOIN sys.schemas S ON T.schema_id = S.schema_id ;" Set objAzureTablesRS = New ADODB.Recordset objAzureTablesRS.ActiveConnection = strConnection objAzureTablesRS.CursorLocation = adUseClient objAzureTablesRS.CursorType = adOpenStatic objAzureTablesRS.LockType = adLockReadOnly objAzureTablesRS.Open strSQL Do Until objAzureTablesRS.EOF = True Debug.Print objAzureTablesRS.Fields("table_name") objAzureTablesRS.MoveNext Loop

David Browne - msft 3,846 Reputation points

2020-12-22T15:28:05.097+00:00

Have you tried just creating the DNS-less linked tables in the Linked Table Manager UI by leaving the "Data source name" blank and hitting "Next"? If that works you can examine the TableDef objects that creates and copy those if you really need to do this in code.

Kris L 26 Reputation points

2020-12-22T16:12:59.95+00:00

Ok sorry that did help but I think the critical change for me (which I had tried, but not when I had everything else correct, it seems) was switching the "Initial Catalog" to "Database" in my connection string. This string works for me:
"ODBC;Driver={ODBC Driver 17 for SQL Server};Server=tcp:MyServerNamt,1433;Database=MyDBName;Uid=XXXX;Pwd=XXXX"

While this fails:
"ODBC;Driver={ODBC Driver 17 for SQL Server};Server=tcp:MyServerName,1433;Initial Catalog=MyDBName;Uid=XXXX;Pwd=XXXX"

I think the "Initial Catalog" is the "Invalid connection string attribute" from my ODBC trace. If that attribute fails, I connect to the server but fail to connect to the database I want, and thus it can't find the table. Makes sense in hindsight, like many technical problems :) :).

Thanks again for all of your help, it's much appreciated!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Access DSN-Less connection SQL Azure - "Could not find installable ISAM"

0 additional answers

Your answer