ACC2: ODBCTimeout Set to Zero for New Pass-Through Queries

• Microsoft Access version 2.0

SYMPTOMS

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

When you create a SQL pass-through query using Access Basic, the ODBCTimeout property setting for the query is set to zero, despite the timeout setting you specify.

RESOLUTION

When you use Access Basic to create a SQL pass-through query, set the ODBCTimeout property after you set the Connect property for the query. If you do not do this, the ODBCTimeout property is set to zero.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

MORE INFORMATION

The ODBCTimeout property setting specifies the number of seconds Microsoft Access waits before a timeout error occurs on a query involving an ODBC database. When this property is set to zero, no timeout occurs.

Steps to Reproduce Problem

NOTE: This example assumes that you have an ODBC data source (such as Microsoft SQL Server) available.

1. Open the sample database NWIND.MDB.

2. Create a new module and enter the following sample procedure:

         Sub CreateSPTQuery()
            Dim db As Database, q As QueryDef
            Set db = dbengine(0)(0)
            'Create a new QueryDef.
            Set q = db.CreateQueryDef()
            q.Name = "MySPTQuery"
            'Modify the line below to select from an existing ODBC table.
            q.SQL = "Select * From authors;"
            q.ODBCTimeout = 60
            q.Connect = "ODBC;"
            q.ReturnsRecords = True
            'Add the QueryDef to the QueryDefs collection.
            db.QueryDefs.Append q
            db.QueryDefs.Refresh
            'Print the ODBCTimeout setting to the Immediate window.
            Debug.Print db.QueryDefs("MySPTQuery").ODBCTimeout
         End Sub
   
   

3. In the Immediate window, test this procedure by typing:

         CreateSPTQuery
   

REFERENCES

Microsoft Access "User's Guide," version 2.0, Chapter 12, "Advanced Queries," pages 291-294