Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Examining the ADO Model

Figure 14.1 shows an overview of the Microsoft ADO model. Unlike the DAO model, the ADO object model is not hierarchical.

Figure 14.1. The ADO object model.

The Connection Object

The Connection object defines a session for a user for a data source. Although the ADO object model is not considered to be hierarchical, the Connection object is considered the highest-level ADO object. After you have established a Connection object, you can use it with multiple recordsets. This improves performance and greatly simplifies your programming code.

You must declare a Connection object before you use it. The declaration looks like this:

Dim cnn as ADODB.Connection

NOTE

Notice that the declaration specifies ADODB.Connection rather than just Connection. This process is called

disambiguation . The process of disambiguating a reference ensures that you create the correct type of object. For example, both the ADO and DAO object libraries have Recordset objects. By disambiguating the reference, you explicitly designate the type of Recordset object you want to create. If you do not disambiguate the reference, the object library with priority in Tools, References is assumed.

NOTE

Listing 14.1 and most of the code in this chapter is located in the Chap14Ex.mdb file included with this book's CD-ROM.

After you have declared the Connection object, you must instantiate a new Connection object. The code looks like this:

Set cnn = New ADODB.Connection

The Connection must then be opened. The Open method of the Connection object receives a connection string, and optionally a user ID, password, and options as a parameter. The following is an example of the simplest use of the Open method:

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Persist Security Info=False;" & _ "User ID=Admin;" & _ "Data Source=" & CurrentProject.Path & _ "\Chap14Ex.mdb;"

The connection string contains three pieces of information:

• The OLEDB Provider that you want to use (in this case, JET 4.0)

• Standard ADO connection properties (for example, User ID)

• Provider-specific connection properties

Table 14.1 lists the most commonly used connection string properties used by the Jet OLEDB provider.

Table 14.1. Connection String Properties Used by the Jet OLEDB Provider

Property Name	Description
Jet OLEDB:Database Locking Mode	Can be set to 0 for page-locking and 1 for row-locking.
Jet OLEDB:Database Password	Used to designate the password for a password-protected database (database security rather than user-level security).
Jet OLEDB:System Database	Full path and filename to the system database (when user-level security is used).
Jet OLEDB:Lock Delay	Used to indicate the number of milliseconds to wait before attempting to acquire a lock after the previous attempt has failed.
Jet OLEDB:Lock Retry	Used to designate how many times an attempt to access a locked page is repeated.

The complete routine required to establish a connection appears in Listing 14.1.

Listing 14.1 Creating a Connection Object

Sub CreateConnection() 'Declare and instantiate the connection Dim cnn As ADODB.Connection Set cnn = New ADODB.Connection 'Open the connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Persist Security Info=False;" & _ "User ID=Admin;" & _ "Data Source=" & CurrentProject.Path & _ "\Chap14Ex.mdb;" 'Close the connection cnn.Close 'Destroy the Connection object Set cnn = Nothing End Sub

TIP

All the examples in this chapter first declare a variable using the keyword Dim, and then instantiate it using the keyword Set. You can remove the Set statement by specifying the New keyword in the Dim statement. For example, you could use

Dim rst as New ADODB.Recordset

Although this works, it is not considered desirable. This is because you have little control over when the object is placed in memory. For example, if the variable is public, Access places it in memory the moment anything in the module is referenced. Separating the Dim and Set statements allows you to declare the variable wherever you like, and place it in memory when you need to.

The Recordset Object

A Recordset object is used to look at records as a group. A Recordset object refers to the set of rows returned from a request for data. As with a Connection object, to use a Recordset object, you must first declare it. The code looks like this:

Dim rst as ADODB.Recordset

After you have declared the Recordset object, you must instantiate it. The code looks like this:

Set rst = New ADODB.Recordset

As with a Connection object, you use the Open method to point the Recordset object at a set of records. The code looks like this:

rst.Open "Select * From tblClients", CurrentProject.Connection

The first parameter of the Open method is the source of the data. The source can be a table name, a SQL statement, a stored procedure name, a Command object variable name, or the filename of a persisted recordset. In the example, the source is a SQL Select statement.

The second parameter of the Open method must be either a valid connection string, or the name of a Connection object. In the example, the Connection property of the CurrentProject object returns a reference to a copy of the connection associated with the current project. The reference supplies the connection for the Recordset object. The completed code appears in Listing 14.2.

Listing 14.2 Creating a Recordset Using a Connection String

Sub CreateRecordset1() 'Declare and instantiate the recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Open the recordset rst.Open "Select * From tblClients", CurrentProject.Connection 'Print its contents Debug.Print rst.GetString 'Close and destroy the recordset rst.Close Set rst = Nothing End Sub

Notice that after you open the recordset, the code prints the result of the GetString method of the Recordset object to the Immediate window. The GetString method of the Recordset object builds a string based on the data contained in the recordset. For now, this is a simple way of verifying that your code works as expected. Also note that the code uses the Close method of the Recordset object to close the recordset. The Close method, when applied to either a Connection object, or to a Recordset object, has the effect of freeing the associated system resources. The Close method does

not eliminate the object from memory. Setting the Recordset object equal to Nothing eliminates the object from memory.

Although this syntax works quite well, I prefer to set the parameters of the Open method as properties of the Recordset object, before the Open method is issued. You will see that this makes your code much more readable as you add parameters to the Open method. The code appears in Listing 14.3.

Listing 14.3 Creating a Recordset Using the ActiveConnection Property

Sub CreateRecordset2() 'Declare and instantiate the recordset Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset 'Set the connection of the recordset to the connection 'associated with the current project rst.ActiveConnection = CurrentProject.Connection 'Open the recordset and print its contents rst.Open "Select * From tblClients" Debug.Print rst.GetString 'Close and destroy the recordset object rst.Close Set rst = Nothing End Sub

Finally, you can use a Connection object, rather than a copy of the Connection object associated with the CurrentProject object, to provide a connection for the recordset. In fact, you can use the same Connection object for multiple recordsets. The code appears in Listing 14.4.

Listing 14.4 Creating a Recordset Using a Connection Object

Sub CreateRecordset3() 'Declare and instantiate one Connection object 'and two Recordset objects Dim cnn As ADODB.Connection Dim rst1 As ADODB.Recordset Dim rst2 As ADODB.Recordset Set cnn = New ADODB.Connection Set rst1 = New ADODB.Recordset Set rst2 = New ADODB.Recordset 'Point the Connection object 'to the connection associated with the CurrentProject object Set cnn = CurrentProject.Connection 'Utilize the connection just opened as the connection for 'two different recordsets rst1.ActiveConnection = cnn rst1.Open "Select * From tblClients" rst2.ActiveConnection = cnn rst2.Open "Select * From tblPayments" 'Retrieve data out of the recordsets Debug.Print rst1.GetString Debug.Print rst2.GetString 'Close the recordsets and the connection and destroy the objects rst1.Close rst2.Close cnn.Close Set rst1 = Nothing Set rst2 = Nothing Set cnn = Nothing End Sub

Notice that both rst1 and rst2 use the same Connection object.

The Command Object

The ADO Command object represents a query, SQL statement, or stored procedure that is executed against a data source. Although not always necessary, a Command object is particularly useful when executing parameterized queries and stored procedures. Just as with the Connection object and the Recordset object, you must declare a Command object before you use it:

Dim cmd as ADODB.Command

Next, you must instantiate the Command object:

Set cmd = New ADODB.Command

After you instantiate the Command object, you must set its ActiveConnection property and its CommandText property. As with a Recordset object, the ActiveConnection property can be either a connection string or a reference to a Connection object. The CommandText property is the SQL statement or stored procedure used by the Command object. The ActiveConnection and the CommandText properties look like this:

cmd.ActiveConnection = CurrentProject.Connection cmd.CommandText = "tblClients"

The completed code appears in Listing 14.5.

Listing 14.5 Using a Command Object

Sub CommandObject() 'Declare a Recordset and a Command object Dim rst As ADODB.Recordset Dim cmd As ADODB.Command 'Instantiate the Command object Set cmd = New ADODB.Command 'Designate where the data comes from cmd.CommandText = "Select * from tblClients" 'Establish the connection information cmd.ActiveConnection = CurrentProject.Connection 'Use the Execute method to return a result set 'into the recordset object Set rst = cmd.Execute 'Display the resulting data Debug.Print rst.GetString 'Close the recordset and destroy the objects rst.Close Set cmd = Nothing End Sub

The example instantiates the Command object. It sets the CommandText property to a SQL Select statement, and points the ActiveConnection property to the connection associated with the current database. It uses the Execute method of the Command object to return the results of the SQL statement into the Recordset object.