Reducing Connection Use

Jet shares connections whenever possible. Jet will share connections in which the DSN value and the DATABASE value are identical. (Omission of the DATABASE value in the connect strings qualifies as identity.) A chunk-fetching operation on two tables, each with its own active connection, can be conducted over a third shared connection. One connection is devoted to managing the keys (bookmarks) for each of the two simultaneously active tables, and the third connection is used to retrieve or send row data corresponding to the active fetch or update operation.

Some servers support powerful connections that can handle multiple partially completed queries at the same time (ORACLE Server is one example). Other servers provide connections requiring fewer resources, but each connection can process only one query at a time (Microsoft SQL Server and SYBASE SQL Server are examples). However, regardless of the server's capabilities, the Jet database engine allows multiple queries and manages your connections automatically.

If your server supports multiple queries on a single connection, you generally won't have to worry about connection usage because Microsoft Jet uses just one connection to the server for the entire application. However, if your server doesn't support such queries, you should try to reduce the number of connections your application requires. This section describes ways to limit your use of connections.

A server database that permits only a single connection from a client and does not support multiple statements on the connection can return rows only to non-updatable Snapshot Recordset objects, unless the set has less than 100 records.

Limiting Dynasets to 100 Records

If your server uses multiple connections to process more than one query at the same time (for example, SQL Server), you can reduce connection use by limiting dynasets to 100 records. A dynaset containing 100 or fewer records requires only a single connection. In contrast, a dynaset containing more than 100 records requires two connections: Microsoft Jet uses one connection to fetch the key values from the server and another to fetch the data associated with those keys for the records visible on screen. Other dynasets can share the second connection but can't share the first, because not all key values may have been retrieved from the server.

As an example, three dynasets each containing more than 100 records require a total of four connections. When all of the key values for any of these dynasets have been fetched, or when the dynaset is closed, the corresponding connection is released. In contrast, three dynasets each containing 100 or fewer records require only one connection.

Note that if your server allows multiple statements on a single connection (for example, Oracle), then the above does not apply.

Releasing Connections

Another way to reduce the number of connections is to close the connections your application is no longer using. You can cause a recordset to finish execution and thereby close connections by doing one of the following:

• In Microsoft Access, in the Open event procedure of a form based on the recordset, add the following statement:

Me.RecordsetClone.MoveLast

In Microsoft Basic, you would leave the RecordSource of the form blank, and place the following code in the Load event procedure of the form:


Dim MyRS As Dynaset

Data1.RecordSource = "SELECT ... FROM ..."
Data1.Refresh
Set MyRS = Data1.Recordset.Clone

MyRS.MoveLast
MyRS.Close


• Use a TOP 100 PERCENT query to return 100 percent of the records. (This is especially useful for filling list boxes and combo boxes.) For more information, search Help for TOP (SQL) in the Microsoft Access help file.

  Although they reduce connection usage, these techniques cause forms to open more slowly. This technique isn't recommended if your recordsets are very large.