Access™ 2007 VBA Programmer's Reference

7.3. Connecting to a Data Source

The Connection object is considered the top-level object in the ADO object model. Although it doesn't contain all the other ADO objects, as the DAO DBEngine object does, 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. The following example opens the connection implicitly when opening a Recordset:

Function OpenRecordsetADO() As ADODB.Recordset

    ' Define Variables
    Dim strSQL As String
    Dim rs As New ADODB.Recordset

    ' Set the SQL for the Recordset
    strSQL = "SELECT [CONTACTS].* FROM [CONTACTS];"

    ' Open the Recordset using to Contacts connection
    rs.Open strSQL, CurrentProject.Connection
    Set CreateConnectionADO = rs

End Function

This creates a temporary connection that is destroyed when you close the Recordset object.

To create an explicit connection, you must declare it, instantiate it, supply it with the various properties it needs, and then open it. The following function creates a connection to the current database and returns it to the caller:

Function CreateConnectionADO() As ADODB.Connection ' Define Variables Dim strConnectionString As String ...