ODBCTimeout Property

Applies To QueryDef object.

Description

Indicates the number of seconds to wait before a timeout error occurs when a QueryDef is executed on an ODBC database.

Settings and Return Values

The setting or return value is an Integer representing the number of seconds to wait before a timeout error occurs.

When the ODBCTimeout property is set to –1, the timeout defaults to the current setting of the QueryTimeout property of the Connection or Database object that contains the QueryDef. When the ODBCTimeout property is set to 0, no timeout error occurs.

Remarks

When you're using an ODBC database, such as Microsoft SQL Server, delays can occur because of network traffic or heavy use of the ODBC server. Rather than waiting indefinitely, you can specify how long to wait before returning an error.

Setting the ODBCTimeout property of a QueryDef object overrides the value specified by the QueryTimeout property of the Connection or Database object containing the QueryDef, but only for that QueryDef object.

Note In an ODBCDirect workspace, after setting ODBCTimeout to an explicit value you can reset it back to the default (i.e., –1) only once during the life of the QueryDef object. Otherwise, an error will occur.

See Also QueryTimeout property.

Example

This example uses the ODBCTimeout and QueryTimeout properties to show how the QueryTimeout setting on a Database object sets the default ODBCTimeout setting on any QueryDef objects created from the Database object.

Sub ODBCTimeoutX()

    Dim dbsCurrent As Database
    Dim qdfStores As QueryDef
    Dim rstStores As Recordset

    Set dbsCurrent = OpenDatabase("Northwind.mdb")

    ' Change the default QueryTimeout of the Northwind
    ' database.
    Debug.Print "Default QueryTimeout of Database: " & _
        dbsCurrent.QueryTimeout
    dbsCurrent.QueryTimeout = 30
    Debug.Print "New QueryTimeout of Database: " & _
        dbsCurrent.QueryTimeout

    ' Create a new QueryDef object.
    Set qdfStores = dbsCurrent.CreateQueryDef("Stores", _
        "SELECT * FROM stores")
    qdfStores.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"

    ' Change the ODBCTimeout setting of the new QueryDef
    ' object from its default setting.
    Debug.Print "Default ODBCTimeout of QueryDef: " & _
        qdfStores.ODBCTimeout
    qdfStores.ODBCTimeout = 0
    Debug.Print "New ODBCTimeout of QueryDef: " & _
        qdfStores.ODBCTimeout

    ' Execute the query and display the results.
    Set rstStores = qdfStores.OpenRecordset()

    Debug.Print "Contents of recordset:"
    With rstStores
        Do While Not .EOF
            Debug.Print , .Fields(0), .Fields(1)
            .MoveNext
        Loop
        .Close
    End With

    ' Delete new QueryDef because this is a demonstration.
    dbsCurrent.QueryDefs.Delete qdfStores.Name
    dbsCurrent.Close

End Sub

Example (Microsoft Access)

The following example sets the ODBCTimeout property to 120 seconds, creates a query, and runs it on a database on an ODBC server:

Sub SetTimeout()
    Dim dbs As Database
    Dim qdf As QueryDef, rst As Recordset

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new QueryDef object.
    Set qdf = dbs.CreateQueryDef("All Cust", _
        "SELECT * FROM Customers;")
    qdf.Connect = "ODBC;DSN=HumanResources;SERVER=HRSRVR: " _
        & "UID=Smith; PWD=Sesame"
    ' Log on to server and run query.
    qdf.ODBCTimeout = 120
    Set rst = qdf.OpenRecordset()
    ' Perform operations with recordset.
    .
    .
    .
    rst.Close
    Set dbs = Nothing
End Sub