Another ODBC Call Failed Topic

Question

I am running Access 2010 FE and SQL Server 2005 BE. I can execute pass through queries to my SQL Server succesfully by using DSNless connections.

During my testing phase sometimes I need to restore my database to get back to my original records so I can rerun my pass through queries. What I have found is when I run a pass through query, it creates an active connection on my SQL Server. I see the connection via the SQL Server Management Console under the MANAGEMENT | SQL Server Logs | Activity Monitor, select view processes. There I can see which process ID is being used and who is using it when I run my pass through query.

Now the only way for me to restore my database is to KILL the PROCESS e.g. Active connection

Now when I have my restored database in place and re-run the pass through query, I receive a ODBC -- Call Failed message box. I have attempted to run a procedure to refresh my querydefs but to no avail, I will still get the ODBC-- Call Failed message box when I click on those objects.

Now there are two options on how to fix this problem, which in either case I find not USER Friendly.

• Restart my Access Application
• Wait approx 5-10 minutes to rerun the Pass Through Query

I created a function to trap my ODBC Errors and this is what appears:

ODBC Error Number: 0
Error Description: [Microsoft][ODBC SQL Server Driver]Communication link failure

ODBC Error Number: 3146
Error Description: ODBC--call failed.

So if for some reason, I need to restart my SQL server or kill a process (Active Connection) on my SQL server while the Access Application is currently connected via ODBC, the objects created via ODBC will not perform properly till I execute the 2 workaround solutions as stated above.

Can anyone shed some advice on a solution? I appreciate any insight.

Answer 1

I asked a similar question some time ago, and never got a satisfactory answer. My original question is here: Force SET IDENTITY_INSERT to take effect faster from MS Access

There is a registry setting documented here for ACE that controls the timeout behavior:

ConnectionTimeout: The number of seconds a cached connection can remain idle before timing out. The default is 600 (values are of type REG_DWORD).

So as a third workaround (in addition to the two you already listed) you can change that registry setting to a shorter timeout (like 10 seconds). This is the approach I took in my answer. One caveat is that shortening the timeout may cause performance or other issues. Your mileage may vary.

See my full answer to the original question for more info.