Establishing DAO ODBC Connections with Jet

Jet requires at least one connection when fetching data from a remote data source. If you indicate that the result set is to be updated, Jet attempts to open an additional connection unless an existing connection can be used. That is, one connection is used to populate the result set, and another to update it. However, once the result set is fully populated — as when you use the MoveLast method — the first connection can be closed or returned to the connection cache.

Providing User ID and Password

To gain access to a remote ODBC data source, you usually have to provide a valid user ID and password combination. These values can be provided in the Connect property of the Data control or in the connect string that is supplied as an argument to the OpenDatabase method. If these values are not supplied, the ODBC Driver Manager exposes a dialog to collect the user name, password, and other missing information needed to establish the connection. There is no way to disable this dialog with DAO and Jet. However, by using the ODBCDirect or RDO prompt arguments, this dialog can be disabled and your code can intercept a trappable error.

Opening Connections Directly

Jet follows these guidelines when managing connections to Microsoft SQL Server:

• When using the OpenDatabase method, Jet opens a new connection, or attempts to re-use an existing connection if an identical DSN exists in the cache. The connection remains open after the Database object is closed (in anticipation of later use), unless there is already a cached connection to that server available. Only one connection for each DSN remains open in the cache.
  
  
• When you open a connection directly using the OpenDatabase method, the DAO/Jet model is forced to query the database to determine the name of each available table there. This information is cached in the Database object and exists (does not have to be refetched) as long as the Database object remains instantiated.
  
  
• With OpenRecordset, Jet tries to share an existing connection, reuse a cached connection, or, failing both of those, opens a new connection to the server and executes a query based on the source argument (or the SQL property of a QueryDef object). As soon as a MoveNext method is executed, Jet fetches the first 100 rows. If this does not complete the query, an additional connection is opened to support updates. The first connection must remain open until the recordset is fully populated or closed to support updates.

Opening Connections Indirectly

It usually more efficient to open connections to a remote data source by having Jet perform the operation. This is accomplished by simply opening a Jet database that contains linkages to remote database tables or views. When you access these attached (linked) objects, Jet establishes the connection using cached connection information that you provided when creating the attachments. However, if Jet is unable to complete the connection for whatever reason, the ODBC driver manager exposes a series of dialogs to attempt to collect logon and DSN information so the connection can be established. Using DAO with Jet, there is no way to disable these dialogs.

Another alternative is to provide the needed connection information to DAO and Jet by setting the Connect property on an open DAO/Jet Database object. Using this technique, you can then use SQL PassThrough queries just as if you had opened the connection directly.

For More Information   For code examples illustrating the OpenDatabase and OpenRecordset methods, see “OpenDatabase” and “OpenRecordset” in the Language Reference".