ACC: How to Set the QueryTimeout Value for ODBC Connections

ID: Q153756

The information in this article applies to:

Microsoft Access versions 7.0, 97

SUMMARY

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, you should first make a backup copy of the registry files (System.dat and User.dat). Both are hidden files in the Windows folder.

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to set the QueryTimeout property for queries run against ODBC data sources.

MORE INFORMATION

When you're using an ODBC database, such as Microsoft SQL Server, there may be delays because of network traffic or heavy use of the ODBC server. Rather than waiting indefinitely, you can specify how long to wait before the Microsoft Jet database engine produces an error. The default QueryTimeout property setting is 60 seconds. This setting may not be long enough to allow some queries to run against ODBC data sources. The following two methods demonstrate how to change the QueryTimeout property.

NOTE: When using Method 1 to change the QueryTimeout property, the value will be set for a specific QueryDef object. When you create a new query through the user interface within Microsoft Access 97, the QueryTimeout property will default to 60.

NOTE: When using the steps contained within Method 2 to set the QueryTimeout value within the registry, any new and existing query within Microsoft Access will continue to display a value of 60 for the QueryTimeout property. If the value set within the registry is greater than the value defined within the individual query, Microsoft Access will use the value within the registry to determine how long to wait before a QueryTimeout occurs.

Method 1

Create a subroutine to set the property.

Create and run the following subroutine when you open your database:


   Sub SetTimeout()
      Dim Mydb as Database
      Set Mydb=CurrentDB
      MYdb.QueryTimeout=120
  End Sub

Once this property is set, it overrides any Windows Registry setting or default value.

NOTE: If you are having any problems with the QueryTimeout property, you may need to upgrade to the most current version of the Microsoft Jet database engine for your version of Microsoft Access. Until then, if you are having problems with CurrentDB.QueryTimeout, you could also try using DBEngine (0)(0). However, it is recommended that you have the current version of the Microsoft Jet database engine.

For information on how to obtain Microsoft Jet 3.51, please see the following article in the Microsoft Knowledge Base:

Q172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download

Example Using DBEngine


  Sub mytest()
     Dim mydb As Database
     Set mydb = DBEngine(0)(0)
     mydb.QueryTimeout = 120
     MsgBox mydb.QueryTimeout
  End Sub

Method 2

Set the property in the Registry.

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall Windows. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

For information about how to edit the registry, view the Changing Keys And Values online Help topic in Registry Editor (Regedit.exe). Note that you should make a backup copy of the registry files (System.dat and User.dat) before you edit the registry.

Using Microsoft Access 97

Go to the following registry key:


     HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.5\Engines\ODBC

Select QueryTimeout, click Edit, and then modify. Change the DWORD value to your desired setting. The value represents the number of seconds to timeout.

Using Microsoft Access version 7.0

Create the following registry key:


    HKEY_LOCAL_MACHINE\Software\Microsoft\Access\7.0\Jet\3.0\Engines\ODBC

Click Modify, and then select DWORD Value. Add QueryTimeout and set the timeout to an integer value. This value will represent the number of seconds to timeout.

Note: If this change to the registry was made while Microsoft Access was running, you must first close, then re-open Microsoft Access before change is recognized.

REFERENCES

For more information about backing up the system registry, please see the following article in the Microsoft Knowledge Base:

Q135120 Configuration Backup Tool for Backing Up the Registry

Keywords : kbusage OdbcHowto
Version : 7.0 97
Platform : WINDOWS
Issue type : kbhowto