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.

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

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

Listing 14.1 Creating a Connection Object

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

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

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

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

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.