Microsoft KB Archive/110227

= PRB: ODBC Database Remains Open After a Close Method Used =

Article ID: 110227

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



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 .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 .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.



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 .INI Settings
The following additional settings for the VB.INI or .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.

