Connection Strategies

The first time you issue a command that requires access to the server, you must establish a connection between the server and your application. Establishing a connection takes time and memory from both the client and the server. This section describes several ways you can limit connections, thus reducing the resources your application uses.

Preconnecting

By using the connection management capabilities of Microsoft Jet, you can log a user on to a server when your application starts, without waiting until the first form or datasheet opens. This is called preconnecting. Microsoft Access caches the connection and authentication information (user name and password), and reuses it as needed. Preconnecting may increase the time it takes your application to start, but it improves the performance of your application while it is running.

One way to preconnect is to create a pop-up form where users can enter their user name and password, construct a connection string that contains the values, and then use the OpenDatabase method. For example, the following procedure connects to a server called MyServer and opens a database:

Sub PreConnect (strUserName As String, strPassword As String)
	Dim wrkRemote As Workspace, dbsRemote As Database
	Dim strConnect As String
	
	strConnect = "ODBC;DSN=MyServer;DATABASE=MyDatabase;" & _
		"UID=" & strUserName & "PWD=" & strPassword & ";"
	Set wrkRemote = DBEngine.Workspaces(0)
	Set dbsRemote = wrkRemote.OpenDatabase("", False, False, strConnect)
	dbsRemote.Close			' Close database but keep connection.
End Sub

See Also For information on creating pop-up forms, search the Help index for “pop-up forms, creating.”

If you’re not sure which ODBC data source you should connect to, you can display the ODBC Data Sources dialog box by using "ODBC;" for the first argument in the connection string. This dialog box displays a list of all registered ODBC data sources available from your computer. You can then select a server and a database, and enter a user name and a password.

Once a connection is made, you can access the actual connection string by using the Connect property, which identifies your connection to the server. You can then use the strConnect variable when you create pass-through queries or link tables. For example:

Sub PreConnect()
	Dim wrkRemote As Workspace, dbsRemote As Database
	Dim strConnect As String

	Set wrkRemote = DBEngine.Workspaces(0)
	Set dbsRemote = wrkRemote.OpenDatabase("", False, False, "ODBC;")
	strConnect = dbsRemote.Connect
	dbsRemote.Close			' Close database but keep connection.
End Sub

In both examples, the Close method doesn’t disconnect you from the server—instead, Microsoft Access caches the connection locally.

Note Because connections are shared, once you establish a connection to a server with a given user name and password, the connection retains that identity, even if linked tables have different stored user IDs and passwords. If you need varying levels of security on multiple linked tables, configure the server’s security so that each individual user has the appropriate access permissions, instead of designing your application around multiple identities.

Reducing Connection Use

Some servers support powerful connections that can handle multiple partially completed queries at the same time. Other servers provide connections that require fewer resources, but each connection can process only one query at a time (for example, Microsoft SQL Server). Regardless of the server’s capabilities, Microsoft Jet allows multiple queries and manages your connections automatically.

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

Limiting Dynasets to 100 Records

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

For example, three dynasets, each containing more than 100 records, require a total of four connections. When all of the primary 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.

Releasing Connections

Another way to reduce the number of connections is to close connections that your application is no longer using. To cause a Recordset object to finish execution and close its connections, perform an action that retrieves all of the records in the Recordset. You can do this in one of three ways:

If you are using a form to manage the connection with a Recordset object, use the MoveLast method of the RecordsetClone property in the Open event procedure of the form. For example:
```
Me.RecordsetClone.MoveLast
```
If you are using code to manage the connection with a Recordset object, use the MoveLast method to retrieve any remaining rows. For example:
```
rst.MoveLast
```
Use a TOP 100 PERCENT query (or a query’s TopValues property) to return 100 percent of the records. (This is especially useful for filling list boxes and combo boxes.)
See Also For more information on TOP 100 PERCENT queries, search the Help index for “TOP predicate” or “TopValues property.”

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

Closing Idle Connections

Regardless of the type of server you’re using, if someone is running your application but hasn’t taken any action for a long time, you may want to close connections to the server. By default, connections are closed after 600 seconds (10 minutes) of idle time. You can change this interval in the HKEY_LOCAL_MACHINE\SOFTWARE \Microsoft\Office\8.0\Access\Jet\3.5\Engines\ODBC key of the Windows Registry by changing the ConnectionTimeout setting to the number of seconds you want to wait before closing idle connections. Microsoft Access closes connections even if datasheets and forms that display remote data are still open, and then automatically reconnects when the connection is needed again.

See Also For information on changing Windows Registry settings, search the Help index for “Windows Registry.”

Even if the idle time has expired, Microsoft Access can’t close a connection if:

A transaction is pending on the connection. You must commit or roll back the transaction before the connection can be closed.
The results of a query are pending. Microsoft Access doesn’t close a connection if it’s in the process of retrieving query results from a server.

You can cause timed-out connections to stay closed by changing the setting in the ODBC Refresh Interval box on the Advanced tab of the Options dialog box (Tools menu). This setting tells Microsoft Access how often to refresh a form or datasheet that contains remote data. The default setting is 1,500 seconds; this means that after 25 minutes of idle time, Microsoft Access refreshes the current screen of remote data from the server, which requires reestablishing a timed-out connection. If you want to reduce connection use by keeping timed-out connections closed longer, increase the ODBC Refresh Interval setting.

See Also For more information on the ODBC Refresh Interval setting, search the Help index for “refresh interval, ODBC.”