ACC2: ODBCTimeout Ignores MSACC20.INI ODBC Setting (113883)

SYMPTOMS

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

The ODBCTimeout property in new queries defaults to 60 seconds, regardless of the value set in the [ODBC] section of your MSACC20.INI file.

In databases converted from Microsoft Access version 1.x, the ODBCTimeout property defaults to 0, regardless of the value set in the [ODBC] section of your MSACC20.INI file.

RESOLUTION

You can use the sample subprocedure listed below to set the ODBCTimeout property for all the queries in your database. Note that the ODBCTimeout property is ignored in queries that do not use attached tables.

Sample Subprocedure

   Sub ChangeAllQueryTimeOuts (timeinterval As Integer)
      Dim db As Database, qd As QueryDef, i As Integer
      Set db = dbengine.workspaces(0).databases(0) 'Use current database.
      BeginTrans            'Start a transaction.
                            'Loop through Querydefs and change the
                            'ODBC timeout.
         For i = 0 To db.querydefs.count - 1
            Set qd = db.querydefs(i)
            qd.odbctimeout.value = timeinterval 'Set ODBC timeout.
         Next i
      CommitTrans           'Finished: commit the transaction.
      db.querydefs.Refresh  'Update the Querydefs collection.
   End Sub

How to Run the Subprocedure

To run the ChangeAllQueryTimeOuts subprocedure, type the following in the module's Immediate window, and then press ENTER:

ChangeAllQueryTimeOuts <time value>

Substitute a value, in seconds, for <time value> in the sample line above.

MORE INFORMATION

The QueryTimeout setting in the [ODBC] section of the MSACC20.INI file applies only to queries that do not have a setting for the ODBCTimeout property. If a query's ODBCTimeout property is blank, then the QueryTimeout setting is used.

An ODBCTimeout value of 0 means that there is no timeout. If the ODBC server does not respond, the query will never timeout and return control.

In Microsoft Access version 1.x, The QueryTimeout and ODBCTimeout values are set by the QueryTimeout setting in the [ODBC] section of the MSACCESS.INI file. This value applies to all the ODBC queries run in the current Microsoft Access session.

In Microsoft Access version 2.0, these values can differ. Each query can have its own ODBCTimeout setting.

Steps to Reproduce Problem

Start Microsoft Access and open the sample database NWIND.MDB.
Open any query in Design view.
Set the query's ODBCTimeout property to null (that is, delete any existing value for the property).
Save and then close the query.
Reopen the query in Design view. Note that the ODBCTimeout property is set to 0.