MS Access ODBC Connetion Timeout Error

by Jonathan on February 4, 2010

There are some of us unfortunate DBAs out there that are forced to support MS Access connections to our database. I am one of those poor souls.

The Problem

I manage a Microsoft Dynamics NAV database with over 26,000 tables. When one of our users was trying to create a linked table in Access 2007 he was presented with a ODBC–call failed Timeout expired (#0) error.

ODBC--Call Failed Timeout Error

Aside: I am always so appreciative of MS descriptive error messages.

I was unable to reproduce this error on my system. The main difference is that I am a member of the sysadmin fixed server role and he is not. I didn’t expect this to be my issue but ran a quick test. Low and behold when I set his user as a member of sysadmin he was able to list all of the table.

Having anyone other than a DBA as a member of the sysadmin fixed server role is unacceptable so I engaged Microsoft for some assistance.

The Environment

Physical Server HP DL380 Windows Server 2008 Enterprise SQL Server 2008 SP1 Enterprise 12 GB of RAM Lefthand Networks iSCSI SAN

The Solution

Disable the ODBC Timeout value

On the client computer, click Start -> Run -> type “regedit” (without double quotes) and press Enter, locate the registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\ODBC

In Access

Click to highlight QueryTimeout, and then on the Edit menu, click Modify. Change the value to 0 (zero).

Close and re-open Access

Tagged as: Access, MS Dynamics NAV, SQL Server, SQL Server 2008, Untitled

Article by Jonathan Gardner

Jonathan has written 72 articles.

Jonathan is a PMP, Technical Manager and MCITP: Database Administrator 2008 working with Insource Technology in Houston, TX. He has over 8 years of experience with a wide range of IT solutions including Database Administration, Project Management, Virtualization, SAN Administration, and Windows Server environments. He writes from his website, jonathanagardner.com and is syndicated on SQLServerPedia.com.

  • Nick Sedgwick

    A bit complicated and scary. . . and the registry setting would have to be changed on every PC the Access database has to be used from.

    Instead, the QueryDef object within the Access database has an “ODBC Timeout” property, which can be set manually in the query’s property sheet. Set this to 0 (weird font! that is supposed to be a zero). That’ll sort it and will work on any PC without having to change registry settings.

    • http://www.jonathanagardner.com Jonathan Gardner

      Nick, I will have to give that a shot. This actually was the solution that Microsoft gave me.

  • Nick Sedgwick

    A bit complicated and scary. . . and the registry setting would have to be changed on every PC the Access database has to be used from.

    Instead, the QueryDef object within the Access database has an “ODBC Timeout” property, which can be set manually in the query’s property sheet. Set this to 0 (weird font! that is supposed to be a zero). That’ll sort it and will work on any PC without having to change registry settings.

    • http://www.jonathanagardner.com Jonathan

      Nick, I will have to give that a shot. This actually was the solution that Microsoft gave me.

  • Jack Summers

    I believe (and have found in practice) that the registry value supercedes the querydef property. i.e. the querydef ODBCTimeout Property has no effect. (Then why is it there?)

    • http://www.jonathanagardner.com Jonathan Gardner

      I have found this to be the case as well. I don’t have an answer about why it is there. I try to stay away from Access as much as I possibly can. I am on a personal mission at one of my clients to rid it from all computers in the organization completely.

      • Chris Laurent

        Thanks for this, 1 year on and i have found this very useful.

        After some tesing, It seems QueryDef can be shorter then the registry setting but longer.

        Registry is like the master setting, (and will always be adhered if reached), and QueryDef is a sub-setting, (which will be adhered if reached, but will not be reached if it is set longer than registry setting), for specific queries etc. 

        i.e. whichever threshold is reached 1st will be implented when a query is run.

        This is why setting QueryDef longer than the registry setting will appear to have no effect if you are trying to extend the time beyond the registry setting.

        For this reason its always useful to be aware of both settings, and set-up in a way that all QueryDef settings sit within the registry setting .

        If anyone can confirm with this is correct or not please do.  I suspect anyone working or forced to work with access may come across this issue.

        Thanks all

  • Jack Summers

    I believe (and have found in practice) that the registry value supercedes the querydef property. i.e. the querydef ODBCTimeout Property has no effect. (Then why is it there?)

    • http://www.jonathanagardner.com Jonathan

      I have found this to be the case as well. I don’t have an answer about why it is there. I try to stay away from Access as much as I possibly can. I am on a personal mission at one of my clients to rid it from all computers in the organization completely.

Previous post:

Next post: