Custom Search
 


How to Change Query Timeout Value for MS Access SQL Queries



By default, query timeout value in MS Access is 60 seconds. This value can be changed at three different levels. Each level is effective for a specific scope of queries.

You may have experienced Microsoft Access query timeout. This often happens to

  1. Change query timeout for a pre-built query.
  2. Change query timeout for raw SQL queries embedded in VBA code.
  3. Change query timeout in Registry.

1. Change query timeout for a pre-built query. Quite a few types of queries can be pre-built in Access in query Design View or hand-code SQL in SQL view. After a query is created, a default query timeout value (60 seconds) is assigned to the query. To change this value, we use Northwind database as the example. Follow these steps:

  1. Open the Access database and right click the query Employee Sales by Country. Click Design View in the drop down menu.

    MS Access Context menu for Query Design View

  2. In the Design View window, click View icon in top left corner. And then select SQL View in the drop down.

    MS Access SQL View

  3. When the SQL View is opened, click View in the menu bar across the top. And then select Properties in the drop down.

    MS Access SQL View Properties

  4. In ODBC Timeout field, type in a new value.

    You can use the ODBCTimeout property to specify the number of seconds Microsoft Access waits before a time-out error occurs when a query is run on an Open Database Connectivity (ODBC) database. By setting this property to zero (0), no time-out will occur.

    For example, 120 will make this query to timeout in 120 seconds if it does not complete after runs for 120 seconds.

    Set MS Access SQL Query Timeout in Properties

  5. Close the window and save the query.

2. Change query timeout for raw SQL queries embedded in VBA code. Sometimes, you write raw SQL queries in VBA code and you want to control timeout value for a specific query or a bunch of queries. You can do it in code editor by using either ADO or DAO Database object.

For ADO, use this code to change timeout value:

Dim objDB as Database
Set objDB = CurrentDB
objDB.QueryTimeout = 120

For DAO, use this code to change timeout value:

Dim objDB As DAO.Database
Set objDB = CurrentDb()
objDB.QueryTimeout = 120

Note that, the timeout value changed here is only effective to hard-coded SQLs in VBA. If you call a pre-built query by VBA, the timeout value set in the query's properties window takes precedence.

3. Change query timeout value in Registry. For raw SQL hard-coded in VBA or SQL coded in Access form control properties, if you don't specify a timeout value by using Database object, the value in Registry will be used.

To change query timeout value in Registry, follow these steps:

  1. Open Registry Editor.

    Click Start -> Run and then type regedit in the command box.

    Open Windows Run command

    Open Windows Registry window

  2. Open Registry Editor.

    Locate the registry entry for ODBC.

    Depending on what version of your Access is, you should find the entry in the following location:

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

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC

  3. Edit QueryTimeout value.

    Double click the QueryTimeout entry and change its Decimal value to whatever suits you.

    Change ODBC Query Timeout value in Registry

  4. Click OK to save the new QueryTimeout value and close Registry editor.

Note that, if the QueryTimeout change in Registry is made while Microsoft Access is running, you must close and then re-open your Microsoft Access database before the change takes effect.

Also note that, the timeout value set in the properties of a re-built query takes precedence over the registry value.


Copyright© GeeksEngine.com



Other Recent Articles from the MS Access category:

1.Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
2.How to easily get a list of field names in MS Access
3.How to count distinct records in MS Access
4.How to do transaction based processing in MS Access
5.How to open a document (local/network file or web page) from MS Access
6.How to use ADOX to create unique composite index - the VBA approach
7.How to do cross-table update queries in MS Access - the right way
8.Three efficient ways to get the number of records by using VBA
9.How to create a composite unique index (not as a primary key) in MS Access
10.Use VBA to get the correct number of records in a Recordset object
11.Disable Access Prompt when a record is changed, table deleted, or action queries run
12.How to hide and unhide a MS Access object
13.How to return multiple values from a VBA function (Part 3)
14.How to return multiple values from a VBA function (Part 2)
15.How to return multiple values from a VBA function (Part 1)
16.Three ways to programmatically duplicate a table in MS Access by VBA
17.Create a DLL by CSharp or VB.Net for VBA
18.How to correctly reference and call a DLL
19.How to register a C# or VB.Net DLL
20.Email address validation by Regular Expressions using VBA
21.Fix MS Access error: Query must have at least one destination field
22.How to unselect radio buttons in MS Access after it has been selected
23.What is Northwind Traders database

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy