Access 2003 VBA Programmer's Reference

7.3. Connecting to a Data Source

The Connection object is considered to be the top-level object in the ADO object model. Although it doesn't contain all the other ADO objects, like the DAO DBEngine object, you must specify the connection that the other ADO objects will use to carry out their functions.

The Connection object represents a single connection to an OLE DB provider, but, of course, you can create several connection objects, each connecting to a different provider. There are two ways to create a connection: implicitly and explicitly. To create an implicit connection, you supply the connection string when creating a child object, such as a Recordset:

rsADO.Open strSQL, strConnectionString

This creates a temporary connection that is destroyed when you close the recordset. To create an explicit connection, you must declare it, instantiate it, supply it with the various properties it needs, and then open it:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

With cn
    .ConnectionString = CurrentProject.Connection
    .CursorLocation = adUseClient
    .Atributes = .Attributes Or adXactCommitRetaining
    .Open
End With

The above example used the CurrentProject.Connection property to supply the connection string information for the connection. In DAO, you would have used DBEngine(0)(0) or CurrentDb(). Since ADO does not have a Database object, use the CurrentProject object instead.

If you are creating a connection within an ADP to the default data store, that's all you need, and it ...