How to Modify the Timeout Value Applicable for Access SQL Queries

In this article we look at changing the default timeout value placed in Ms Access for running queries

Often while using queries in the MS Access application as well as SQL Server, there are instances when your query might time out, before you can get the query results. Many users of the applications are unaware of the possibility of modifying query timings in both the applications. Although both the applications greatly vary in their capacities to contain data, similar kind of procedure is used in both for modification of query timings. There are three variants of queries that can be modified

Pre-Existing Query

The Query Design Interface (View) in MS Access and SQL View in SQL Server contain few pre – built queries, with default timeout value as 60 seconds. This value can be changed through the following steps:

1. Open the database you want to enter the query in, select the query you want to change the default timeout value for, and select Design View from drop down.

2. From the Design View Window, select View Icon, and click on SQL View in the drop down menu.

3. From the SQL View Window, select View from the Menu Bar, and click on Properties in the drop down menu.

4. In the Query Properties Window, select a new time value for ODBC Timeout field. If you make the value as 0, the query will not time out all.

5. Next close the interface window and end by saving the new timeout value.

Raw SQL Queries that are Encompassed in VBA Code

For modifying query timeout for a single query or several queries in a bunch of other raw SQL queries in VBA code, you should make use of the Code Editor. It contains two types of database objects ADO and DAO, both of these use a specific code to modify timeout values for raw SQL queries in VBA code.

Query Timeout Value Present in the Registry

Follow these steps for changing query timeout value present in the registry:

1. Open Registry Editor. Start > Run – type ‘Regedit’ in command box.

2. Open Registry Editor. Look for the registry entry present for ODBC. Its location will differ in all editions.

3. Select the Query Timeout entry for modifying the Query Timeout. Double click on the entry and enter the Decimal value you want to set.

4. Save the newly entered timeout value by clicking on OK, and close the Registry Editor.

It is important to keep in mind that, if you are changing the query timeout in an open Access database, you are required to close the database and open it again before you modify query timeout.

This provision of changing Query timeout in both, MS Access and MS SQL Server is a very useful one. This does not keep the user tensed over the query timing out soon after being launched, maybe even before it can fetch the results. Also when queries do not get executed due to short timeout times, users may suspect they are dealing with a corrupted mdb file while the issue may be completely different. By increasing the query timeout value for database applications, a user can enter queries in a more relaxed manner, without any fear of the query timing out or getting unduly worried about database health.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair mdf database damage and excel recovery software products. For more information visit www.datanumen.com