Knowledge Base |

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