ODBC from Access to SQL Server database - query timeout expired

[danevald]

I am having issues connecting to a database located on a SQL Server 2022.

When creating the connection all test gives OK. But when trying to connect, it fails with:

ODBC--call failed

"Query timeout expired" 

If I don't define the database, then Access connects to master db and lists the tables that I can access. So it seems the connection to the server itself is ok.

Tried ODBC SQL server driver, SQL Server 17 and 18 odbc driver, all same results.

Same database on SQL Server 2014 also does not work for my user, but another user has previously been able to connect to old database, but seems something is missing either client-side or server option in the new setup?

[#2]

This sounds like a problem with credentials. Depending on which Logins and Users you have given permission to, they'll be able to connect to one or more databases on the server. Do you have a DBA for the SQL Server with whom you can sort this out?

[#3]

I agree with George. Use SSMS to test your credentials and rights to connect to certain databases. Only after that is successful would you turn to Access to do the same.

[#4]

@Tom_van_Stiphout @George_Hepworth credentials work great with SSMS, all tables are visible - still not with ODBC.

[#5]

Use the ODBC applet (under Control Panel) to create a File DSN using the same ODBC driver. On the last page of the wizard you can test the connection. Does that work?

[#6]

It's the same as creating it through Access, and the test completes successfully.

[#7]

@danevald 

I don't recall. Did you ask the DBA for this SQL Server for assistance? In fact, we ought to be clear on where this SQL Server is located, local or remotely hosted.

One other thing comes to mind, which I probably should have thought of previously. Depending on where this SQL Server is, local or hosted, it may be that you have to specify that the connection needs to trust the Server Certificate to which it's connecting.

Note the elements of this connection string for a table linked in Access, for example, especially the last one.

DRIVER=ODBC Driver 18 for SQL Server; SERVER=YourServerNameGoesHere;UID=YourUserIDGoesHere;PWD=YourPasswordGoesHere;Trusted_Connection=No;APP=LocalApplicationName;DATABASE=YourSSDatabaseNameGoesHere;Encrypt=Yes;TrustServerCertificate=Yes;

TrustServerCertificate is being required by more hosts, as well as by SQL Azure. I have been caught out by it at least a couple of times in the last 6 months.

[#8]

Found a solution now, the querytimeout had to be extended a lot...
I did not work to change the setting in Access, Options, Client settings, OLE/DDE timeout, I had to do it in registry:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Microsoft\Office\16.0\Access Connectivity Engine\Engines\ODBC\QueryTimeout

I only have access to 1 table, but still it takes at least 1½-3 minutes to open the connection. I'm told because of the number of tables this might be as expected.

[#9]

I have opened an Access FE with ODBC links to a few dozen tables among 4000 (!) tables in the on-prem SQL Server BE, and it was instantaneous.

The timeout can also be set on the QueryDef object, if you're running a PT query.