Closing DAO ODBC Connections with Jet

When you close a Recordset or Database object, or when these objects are no longer in scope, the connections they use are released to the connection cache. For example, if you declare a Recordset object in a procedure, and that procedure ends, the recordset is automatically closed and any connections needed to support it are released to the cache.

When your code visits the last record of a Recordset object, as when you execute the MoveLast method, the connection used to populate the recordset is released to the cache. A single connection is maintained to perform updates or other action queries against all open Recordset objects.

When your code closes a Database object or the object loses scope, Jet closes the Database and any associated Recordset objects. Any connections associated with those objects are released to the cache.

Each Data control functions like an OpenRecordset method. That is, each Data control creates one or two connections (depending on the size of the result set and the functionality of the server being accessed) when they are initialized. Visual Basic automatically populates Recordset objects created by the Data control to release connections as quickly as possible. This happens during idle time, and at a configurable rate determined by the MSysConf table settings. Generally, Jet maintains a single connection to perform updates, but until the result set associated with each Data control is fully populated, a second connection must remain open to return the rows. When your code positions the recordset to the last row, as when you execute a MoveLast method, this extra connection is no longer needed and is returned to the pool.