The Connection object

The Connection object establishes a link to a database. You always use a Connection object either implicitly or explicitly when you work with a database. When you explicitly create one, you can efficiently manage one or more connections and reassign the roles that they serve in an application. By implicitly creating one you can shorten your code. Each new object that you create with an implicit connection consumes more resources. If your application has only one or two objects that each requires its own connection, implicit connections might serve your needs best. ADO lets you choose how to create and manage connections as you see fit.

Unlike DAO, ADO is a general data access language, so not all of its properties and methods are appropriate for the Jet engine. There is, however, a special OLE DB provider for Jet 4, which is the latest version of Jet that ships with Access 2000. Since Connection objects depend critically on provider specifications, the ability to set a Connection parameter that references the Jet 4 provider is valuable. This custom provider allows ADO to reflect many of the special strengths that Jet offers. When you refer to a database in another file, you might want to include a Data Source parameter, which points to the physical location of a database when it is not in the current project.

The following simple code sample opens the familiar Northwind database. Notice that a Dim statement declares and creates a reference to cnnNorthwind as a Connection object. The use of the Open method on cnnNorthwind makes the database available to the rest ofthe procedure. Notice that the Provider and Data Source parameters appear within a single pair of double quotes. The Provider parameter points to the Jet 4 OLE DB provider and the Data Source parameter points to the physical location of the Northwind database.

Sub OpenMyDB()

Dim cnnNorthwind As New Connection

Dim rsCustomers As Recordset

'Create the connection.

    cnnNorthwind.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

'Create recordset reference and set its properties.

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.CursorType = adOpenKeyset

    rsCustomers.LockType = adLockOptimistic

'Open recordset and print a test record.

    rsCustomers.Open "Customers", cnnNorthwind, , , adCmdTable

    Debug.Print rsCustomers.Fields(0).Value, rsCustomers.Fields(1).Value

    rsCustomers.Close

    cnnNorthwind.Close

End Sub

After creating a reference to the connection, the code creates a Recordset object. It sets a reference to the object variable denoting the recordset, and then it assigns values to a couple of properties for the recordset. The last block of code opens the recordset and prints a couple of fields from the first record. The Open method for a Recordset object can reference a connection to a database and some source of records in the database. The code above selects all of the records from the Customers table in the Northwind database. The Open method initially makes the first record available to an application.

The final two lines in the last block of code close the recordset and then the connection. Closing a connection makes all objects that reference it, such as a Recordset object, inoperable. Any attempt to set properties or invoke methods for a recordset that references a closed connection generates a run-time error. For this reason, implicitly creating a connection might be a better choice because the object has use of the connection for its lifetime.

The following code also opens a recordset based on the Customers table in the Northwind database and prints the first record. However, it uses fewer lines of code and the code is less complicated because it implicitly creates a connection and accepts more default settings.

Sub OpenFast()

Dim rsCustomers As Recordset

    Set rsCustomers = New ADODB.Recordset

'Less code, but potentially greater resource consumption

    rsCustomers.Open "customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

    Debug.Print rsCustomers.Fields(0), rsCustomers.Fields(1)

    rsCustomers.Close

End Sub

Since there is no explicit connection, the OpenFast procedure does not need to declare a connection object (and therefore doesn't have to open one or close one). As you can see, the Open method for a recordset object can include the essential connection information of a provider and a data source. The code above has only one other parameter-the source for the recordset, which is the Customers table. The Open method relies on the default CursorType and LockType settings, which are, respectively, forward-only and read-only. These settings provide for very fast operations, but they do not offer a lot of functionality. Nevertheless, if they suit your needs and let you divert your attention to other aspects of application development, they might be the best choice.

The Mode property By default, the Connection object's Open method creates a database for shared access. However, you can set the Connection object's Mode property to any of seven other settings that grant various degrees of restricted access to a database. These mode settings pertain to all the recordsets and commands that assign a connection to their ActiveConnection property. The following code shows the impact of the read-only mode setting on the ability to update a recordset.

Sub OpenLookOnly()

Dim cnn1 As New Connection

Dim rsCustomers As Recordset

'    cnn1.Mode = adModeRead

    cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

    Set rsCustomers = New ADODB.Recordset

    rsCustomers.Open "Customers", cnn1, adOpenKeyset, _

        adLockPessimistic

'An adModeRead setting for cnn1.Mode causes an error in this procedure.

'Remove the comment from the cnn1.Mode line to see an error here.

    rsCustomers.Fields("CustomerID") = "xxxxx"

    rsCustomers.Update

    Debug.Print rsCustomers.Fields("CustomerID")

    rsCustomers.Close

End Sub

The OpenLookOnly procedure declares a new Connection object in its first line. The third line, if uncommented, sets the connection's Mode property to adModeRead for read-only access. Two more lines into the procedure, an Open method makes the rsCustomers recordset available. The next pair of lines attempts to update the value of the CustomerID field for the first record. If you remove the comment in the third line these updates will cause an error because you can't update a read-only database.

The following table describes the eight constants that you can use to set a connection's Mode property. You can use these constants to control the type of editing that one or more users can do through a connection to a database.

Constants Used to Set the Connection Object's Mode Property

*Constant*	*Value*	*Behavior*
adModeUnknown	0	Permissions not set or determined
adModeRead	1	Read-only permission
adModeWrite	2	Write-only permission
adModeReadWrite	3	Read/write permission
adModeShareDenyRead	4	Prevents others from opening record source with read permissions
adModeShareDenyWrite	8	Prevents others from opening record source with write permissions
adModeShareExclusive	12	Prevents others from opening the connection
adModeShareDenyNone	16	Shared access (default)

The OpenSchema method The Connection object's OpenSchema method lets an application browse the objects in the collections available through a connection without enumerating the elements in a list. The output from the OpenSchema method can contain information about tables, views, procedures, indexes, and more. The specific details depend on how a given OLE DB provider implements the general capabilities of the method. The following code uses the OpenSchema method with the Jet 4 provider to list the views available through a connection.

Public Sub OpenSchemaX()

Dim cnn1 As New ADODB.Connection

Dim rstSchema As ADODB.Recordset

    cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

        "Data Source=C:\Program Files\Microsoft Office\Office\" & _

        "Samples\Northwind.mdb;"

    Set rstSchema = cnn1.OpenSchema(adSchemaTables)

'Print just views; other selection criteria include

'TABLE, ACCESS TABLE, and SYSTEM TABLE.

    Do Until rstSchema.EOF

        If rstSchema.Fields("TABLE_TYPE") = "VIEW" Then

            Debug.Print "View name: " & _

            RstSchema.Fields("TABLE_NAME") & vbCr

        End If

        rstSchema.MoveNext

    Loop

    rstSchema.Close

    cnn1.Close

End Sub

The procedure starts by declaring a connection and a recordset. The recordset holds the output from the OpenSchema method. The argument for the OpenSchema method indicates that elements of the Tables domain for the database schema will make entries in the records. However, the OpenSchema method tracks several types of tables, including views, normal user tables, special system tables, another table of Access objects, and linked tables. The code above prints the output from the method just for views.