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.

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