Share via

Visual Basic Concepts

  • Article

Caching DAO ODBC Connections with Jet

A key part of connection management is that Jet caches either one or two connections, depending on the server. For servers such as Oracle, which allow pending results on a connection, Jet caches one connection. For servers such as Microsoft SQL Server, which do not allow pending results on a connection, Jet caches two connections.

Note   SQL Server's server-side cursors support multiple operations on a single connection as implemented with ODBCDirect and RDO. However, there is no support for server-side cursors with Jet.

When Jet needs to open a connection, it first checks its internal connection cache. If there is a connection in the cache that uses the same DSN and database parameters, and there are no uncompleted queries pending on the connection, it is reused. Back-end database systems that support pending results on a single connection may not need additional connections to perform simultaneous read/write operations.

Note   Jet caches the user ID and password along with the connection, so that you’re not repeatedly prompted. This means that if your application needs to log on to the server with a different user ID and password, you will be unable to do so unless you force the closure of any existing connections.

Jet ages each connection based on elapsed time and its activity. After a configurable connection timeout period (which defaults to 10 minutes), Jet automatically closes and drops any dormant connections. For a connection to be considered dormant, it must have no open Database or Workspace objects associated with it. Jet will not close connections if there are uncommitted transactions, or queries with unfetched results. Since Jet automatically closes connections, this implies that Jet automatically re-opens connections as needed.

Note   The ConnectionTimeout setting can be adjusted by accessing the Windows system registry.

If your application needs access to a connection that Jet has timed out and closed, the connection is automatically reopened. Assuming that the connection is re-established, this should not cause a problem with your application.

In some cases, if a shared DSN is identical, queries against a second Database object might be blocked while Jet waits for the DSN to become available.