Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code

489

FIGURE 13.9

Referencing the ADO library

In the following code examples, notice that all the ADO object variables are referenced as ADODB

object types. Although not entirely necessary, prefixing object type names with a library reference

clears up any ambiguity that Access might have regarding the type of object referenced by the VBA

statements. For example, both ADO and DAO support a Recordset object. Unless the object

type declaration is prefixed with either ADODB or DAO, Access may misunderstand which type of

recordset is referenced in a VBA statement.

The ADO Connection object

As its name suggests, the Connection object provides a connection to a data source. Access to a

data source is necessary for any data operation, so the Connection object is required in virtually

any scenario involving ADO.

After the ADO library has been referenced, creating a Connection object is simple (the ADO

library is referenced as ADODB in VBA code):

Dim cnn as ADODB.Connection

Set cnn = New ADODB.Connection

These two statements are typical of VBA’s approach to object-oriented programming. In the first

statement, an object variable (cnn) is established as an ADODB.Connection object type. This

means that VBA recognizes cnn as a Connection, with all the properties and methods associated

with Connection objects, as defined by the ADO library. However, at this point cnn is just a

placeholder — it doesn’t yet exist in memory.

The second statement instantiates the cnn object variable. As this statement executes, VBA creates

a Connection object in the computer’s memory, points the cnn variable to the object in mem-

ory, and prepares it for use.