question

KrisL-6127 avatar image
0 Votes"
KrisL-6127 asked KrisL-6127 commented

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

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://docs.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?

azure-sql-database
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KrisL-6127

Can you please confirm if you are using Azure SQL (PaaS service) or the SQL Server installed on the Azure VM?

Thanks
Navtej S

0 Votes 0 ·

Ok so let's keep in mind I'm just starting to build my Azure knowledge. I really don't think it's PaaS. I had to look up what that even was and it sounds WAY more complex than what I did. I don't remember creating an Azure VM, but maybe that's just handled behind the scenes... I mean the SQL Server has to live somewhere, right? :) All that I did was click the "SQL Databases" option found on the top of the portal home page and went through the wizard.

So, this should be just your standard SQL Server on Azure, not PaaS.

0 Votes 0 ·

@KrisL-6127

We are trying to find it from our end but it would be great to raise the issue here as well - https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev

This will give a fresh set of eyes to this question, If you find the answer, please do post here.

Thanks
Navtej S

P.S. - It appears you are using Azure SQL PaaS service if you went through the wizard and got the connections strings in portal etc.

0 Votes 0 ·
Show more comments

1 Answer

DavidBrowne-msft avatar image
1 Vote"
DavidBrowne-msft answered KrisL-6127 commented

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


· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

0 Votes 0 ·

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) 
0 Votes 0 ·

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

0 Votes 0 ·

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]

0 Votes 0 ·
Show more comments