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
