Share via

Visual Basic Concepts

  • Article

Managing DAO ODBC Connections with Jet

When you access an ODBC database through DAO or the Data control using Jet, the Microsoft Jet database engine and its connection management routines are automatically invoked. Jet will make every attempt to open as few connections as possible and share connections whenever possible.

Generally, the Jet database engine attempts to share or cache any connections established by your application. As a rule of thumb, if the Data Source Name (DSN) you request is referenced by more than one OpenDatabase call, then DAO and Jet attempt to re-use that connection. That is, if you already have a connection open to a specific DSN, Jet will not attempt to open another if it thinks it can share the existing connection. When you use the Close method on a DAO Database object, Jet does not close the connection immediately if it is the last connection to the specific DSN — it is cached in case you try to open the connection again.

Opening Connections

There are two primary ways to access remote ODBC data sources using DAO and Jet. Both techniques involve the OpenDatabase method against:

  • An ODBC data source. In this case, the connect string argument of the OpenDatabase method specifies a DSN that points to the ODBC data source.

  • A Jet database containing linked (attached) ODBC data source tables or views. In this case, the OpenDatabase dbName argument points to the path of a Jet .mdb database. The Jet database contains the DSN and, optionally, the user name and password.

Each technique has its advantages and disadvantages. However, using the OpenDatabase method against an ODBC DSN requires that Jet download a significant amount of descriptive information, as data definition language (DDL), about the target database. Because this information is not cached, this DDL fetch operation must be repeated each time the Database object is opened. This is one reason why Jet attempts to cache connections — so this process does not have to be repeated.

The process of attaching or linking to a table from an ODBC data source caches the DDL information in the Jet database, so it is not necessary to perform the DDL query when the connection is established or the database is opened. This can dramatically improve performance when opening a connection and creating a DAO Database object.

For More Information   For details about connection strategies, see "Caching DAO ODBC Connections with Jet" in this chapter.