Knowledge Base

PRB: ODBC Database Remains Open After a Close Method Used

Article ID: 110227

Article Last Modified on 10/29/2003

APPLIES TO

This article was previously published under Q110227

SYMPTOMS

Using a Close method on a database opened with Open Database Connectivity (ODBC) drivers doesn't close the database. The ODBC database process keeps running. To close the connection successfully, you must end the Visual Basic application.

CAUSE

After Visual Basic executes the Close method on an ODBC connection, the Microsoft Access engine in Visual Basic maintains a persistent connection in case the user reopens the database later in the program. This makes the program more efficient.

RESOLUTION

If a Visual Basic program does not reopen the ODBC connection after doing a Close method, a time-out occurs and the connection closes automatically.

You can control the time-out period by placing the following line in your VB.INI or <vb_exe_app_name>.INI file, where x is the number of seconds: 
   [ODBC]
   ConnectionTimeout=x

The default ConnectionTimeout is usually 600 seconds (10 minutes). The lowest supported ConnectionTimeout value is 1 second. A ConnectionTimeout value of 0 says to never cause a time-out.

To enforce the fastest possible time-out, you can set ConnectionTimeout to 1. In addition, you can add the following code after you close the database to make sure the connection is terminated: 
   db.Close      ' Close database, using database object variable (db).
   Start = Timer
   Do            ' This loop pauses a second to allow a time-out
      FreeLocks  ' Tell Microsoft Access engine that program is idle.
      DoEvents   ' Tell Windows to do any pending events.
   Loop While Timer <= Start + 1

This loop delays for a second after the db.Close. The FreeLocks statement tells the database engine that the user is idle. If you run the Visual Basic program with ConnectionTimeout set to 1 in your VB.INI or <vb_exe_app_name>.INI file, the database engine will disconnect the one-second-old connection to the server.

STATUS

This behavior is by design for all ODBC database connections.

MORE INFORMATION

Reproducing the Behavior

For example, using a Close method on a database opened via an ODBC connection to a Sybase SQL server leaves the Sybase session open. You can confirm this by executing sp_who on the Sybase server.

As another example, assume you have a Visual Basic application on a timer that regularly checks SQL Server version 4.2a on OS/2. To avoid wasting an SQL user connection between the timed checks, you might want the Close method to release the user connection. However, the Close method doesn't release the user connection.

NOTE: You can create Dynaset or Snapshot objects against ODBC databases, but you cannot use the OpenTable method to directly open ODBC tables.

Additional VB.INI or <vb_exe_app_name>.INI Settings

The following additional settings for the VB.INI or <vb_exe_app_name>.INI initialization file are useful for handling ODBC databases and time-outs:

Entry for [Debug]: 
   Section              Value   Effect
   ----------------------------------------------------------------
   RmtTrace             0      Use asynchronous query execution
                               if possible; no ODBC API tracing
                               (default).

                        8      Trace ODBC API calls in ODBCAPI.TXT
                               in the Microsoft Access directory.

                       16      Force synchronous query execution.

                       24      Trace ODBC API calls; force
                               asynchronous query execution.

Entries for [ODBC]: 
   Section              Value   Effect
   ----------------------------------------------------------------
   TraceSQLMode         0      No tracing of SQL queries (default).

                        1      Trace SQL queries sent to ODBC in
                               SQLOUT.TXT in the Microsoft Access
                               directory.

   QueryTimeout         S      Wait S seconds for queries sent to
                               ODBC, and then stop trying to process
                               the query results (for asynchronous
                               queries only). (Default: 60 seconds).

   LoginTimeout         S      Wait S seconds for ODBC login response,
                               and then stop trying to connect to a
                               server. (Default: 20 seconds).

   ConnectionTimeout    S      Wait S seconds, and then close idle
                               ODBC connections. (Default: 600
                               seconds).

   AsyncRetryInterval   M      Retry asynchronous queries every M
                               milliseconds. (Default: 500
                               milliseconds).

   AttachCaseSensitive  0      Attach the first table whose name
                               matches the specified string,
                               regardless of case.

                        1      Attach a table only if its name
                               exactly matches the specified string.

   AttachableObjects  string   A list of object types you can attach.
                               (Default: 'TABLE', 'VIEW', 'SYSTEM TABLE',
                               'ALIAS', 'SYNONYM'.)

   SnapshotOnly         0      Get index information when tables are
                               attached so that dynasets are allowed
                               (default).

                        1      Ignore index information when tables
                               are attached so that only snapshots
                               are allowed.

REFERENCES

"Microsoft Visual Basic for Windows Professional Features Book 2:
Data Access Guide," pages 149-154 in Appendix C.

Additional query words: jet caching

Keywords: kbinterop kbprb KB110227


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.