One of the things that Microsoft Jet hides from your program is the concept of connections. Every operation on an ODBC database requires a mechanism for conversing with the database server , and connections provide this mechanism. The first time you issue a command that requires access to the server, for instance, when you open a server table, Microsoft Jet opens a connection to the server. When you finish the operation, Microsoft Jet keeps the connection open in anticipation of the next operation. Similarly, if you have a connection open for a long time but aren’t doing anything with it, Microsoft Jet will silently close it and then reopen it when you resume working.
In some situations, you may want the connection to be opened at a time you control rather than when the first operation occurs. For example, if your program has a startup or initialization routine already in place, you may want to establish connections in that routine. This front-loads the execution time of your application, making it easier to avoid delays later in your application. Also, connections to your particular database might take a long time to establish if, for example, the database server is across the country or you’re connected by a modem.
One way to minimize delays in opening forms or populating tables is to connect to the database server when your application starts. This technique, called preconnecting, allows you to set up the connection to the database before the user requests data. Preconnecting effectively speeds up the first routine data retrieval operation in your application while causing only a slight delay during application startup.
There are two simple techniques for establishing a connection at application startup. The first technique applies to applications that can use different ODBC data sources; the second technique is used when the ODBC data source name is known and can be hard-coded into the application. Although each technique uses the database’s Close method, Microsoft Jet does not close the connection to the ODBC data source immediately. It caches the connection for the period of time specified by the ConnectionTimeout setting in the Windows Registry.
For example, the BookSales sample application opens a connection early in the program with the following code:
' Note that the sample uses the conConnect constant to provide the
' connection string. The constant has been replaced with
' full connection information in this code example.
Set dbs = OpenDatabase("", False, False, _
"ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs")
dbs.Close
Although it appears that this operation won’t do anything, Microsoft Jet caches the connection for a time controlled by the ConnectionTimeout setting in the Windows Registry.
See Also For more information on Registry settings, see Appendix C, “Registry Settings.”