Active Data Objects

OLE DB is interesting, but you can’t get to it from Access. To make OLE DB accessible to VBA programmers, Microsoft has provided ADO. Michael provides an introduction to this new technology.

Active Data Objects (ADO) are the gateway to OLE DB for Access programmers (or anyone using VBA or any scripting language). ADO is an easy-to-program wrapper around OLE DB. Although simpler than Data Access Objects (DAO), ADO provides an object model that allows you to do almost everything DAO does. This article focuses on those essential features of ADO that return a Recordset or produce an effect on data by manipulating underlying tables directly.

The ADO model

ADO is a rich and extremely flexible object model consisting of three primary objects listed below (see Figure 1 for the complete model). Although I’ll focus on the Recordset related methods, these three primary objects use dozens of methods and properties. The ADO help file installed when Active Server Pages (http://microsoft.com/iis/) or the OLE DB SDK (http://microsoft.com/oledb/) is installed is an excellent reference.

  • The Connection object — The primary method of this object, the Open method, defines the location of data by using a connection string (more about that later). Transactions are also controlled by the Connection object. The Execute method of this object can be used to set up the server environment or to call a stored procedure. The Execute method returns, by default, a highly specialized cursor that is read/forward-only (sometimes called a “fire-hose” cursor). The SQL statement that creates this cursor can’t use output parameters or require command preparation. This fire-hose cursor was chosen as the default type because it doesn’t require cursor type and lock type properties.
  • The Command object — This object’s main purpose is to communicate command text (such as SQL strings or directory names) to data providers. When supported by the data provider, this object also identifies stored procedures and querydefs along with their command parameters (both input and output). This object might or might not return a cursor.
  • The Recordset object — This is the richest of the ADO objects and implements dozens of methods and properties. This is where ADO does most of the work.

ADO objects are not strictly hierarchical, which is a departure from tradition. You can create a Connection object, associate it with a Command, fetch a Recordset with that Command object, and then disconnect the Command object from the Connection. With that done, you can use a different Connection object to create another Recordset with the same Command object.


Figure 1

Collecting with ADO

The next group of entities in the world of ADO is the group of collections tied to one or another of the primary objects as shown here:

Object Collection
Connection Errors
Command Parameters
Recordset Fields

Each of these collections will be familiar to Access developers, but each has its own new twist.

The Errors Collection, for example, has a distinctive ability to collect more than one Error object for each ADO-specific error created by the data provider. It’s the responsibility of the data provider to supply the Error objects when things go awry. If the data provider doesn’t produce multiple Error objects, then the routine COM error handlers take over. Don’t expect ADO to put anything in the Error object. In the OLE DB model, that’s up to the data provider.

The Parameters collection is also unconventional. With DAO, parameters were created and fixed at design time. In ADO, Parameter objects can be created at runtime. With the Parameters collection, I deal with each Parameter object explicitly before I invoke the Open method of the Recordset object. If I know the attributes of my querydef’s parameters, I duplicate those qualities with the arguments used in the CreateParameter method. If I don’t know the attributes of the parameters, I can invoke the Refresh method and let the data source generate parameter information. I can use the Parameters collection with SELECT, INSERT, UPDATE, and DELETE SQL statements to minimize the number of trips to the data source. By using a single SQL statement and the Parameters collection, I can have everything done with just one trip to the data source.

The Fields Collection is different in ADO than DAO because ADO must accommodate a wider variety of field types than those typically encountered in Access. Don’t worry, the old Field methods AppendChunk and GetChunk are still around to handle binary large objects. ADO has also made getting access to original field values (the value of a field before it was edited) easier by providing the new OriginalValue property of a Field object. This OriginalValue property is also added in DAO 3.5. With ADO (and DAO 3.5), you can also easily get the value of fields after updates (for ADO it’s the UnderlyingValue property, for DAO 3.5, the VisibleValue property). This UnderlyingValue property is handy if you want to ensure that a field has been updated or if you need to resolve conflicts after batch updates.

That’s an overview of the most important features of ADO. Now let’s take ADO for a spin.

ADO basics

In this section you’ll see examples of required syntax for each of the primary objects of ADO: the Connection, Command, and Recordset objects. I’ll include alternative syntax examples in code snippet comments.

The Connection object connects you to your data provider. In the same way that you need to know the phone number of someone you want to call, the Connection object needs to know the “phone number” of the data source. In ADO, this number is called the Connection object’s ConnectionString property.

If you use the ODBC Provider in ADO, there are two ways to look up this number: through a provided Data Source Name (DSN) or through a detailed string of argument=value pairs separated by semicolons. In either case, the resulting ConnectionString property tells ADO the name of the correct OLE DB provider (and, if necessary, an ODBC driver) needed to access the data source, the location and name of the database, security information when accessing a secure database, and special customization properties exposed by the data provider.

A DSN can come in several forms. A System DSN is created in the Control Panel of the ODBC32 Administrator. When you go this route, the connection string information is stored in the Windows Registry in a key accessible to all users who log in to this computer. A User DSN, on the other hand, is stored with individual user profiles and is not visible to all users. A File DSN, also created in the ODBC32 Administrator, records the same connection information as the other two but stores the information in the file system. If a System DSN is used to define the ConnectionString property, the DSN must exist in the Registry of the machine hosting ADO; a common mistake is to define it on the client machine.

In the case of ADO, there’s an implicit argument in the ConnectionString property called the Provider argument that identifies which data provider to invoke. For ADO, the Provider argument has a default value of “MSDASQL.” Other OLE DB data providers might use other Provider arguments. After intercepting the Provider argument, OLE DB passes the connection string information on to the next layer in data access technology. When going after Access data, that will be the ODBC Provider. The ODBC Provider Users Guide (included with the OLE DB SDK) lists all the keywords recognized by the ODBC Provider. Arguments not recognized by the ODBC Provider are ignored and used by the next layer in the system, the ODBC driver. Individual drivers recognize their own collection of keywords, so you should refer to the ODBC help files for each data source for details.

Getting connected

In the following code, I copied the connection string information from a file DSN:

'Declare constants or INCLUDE the adovbs.inc that

'ships with the Adventure Works Sample app.

'See the ASP Roadmap documentation for details.

CONST adCmdText = 1

CONST adCmdTable = 2

CONST adOpenKeyset = 1

CONST adLockOptimistic = 3

CONST adCmdStoredProc = 4



Set objConn = _

Server.CreateObject("ADODB.Connection")

'Be sure the path in the dbq argument below

'matches the path to the data on your computer or

'use a Universal Naming Convention address.

objConn.Open _

"driver={Microsoft Access Driver (*.mdb)}; " & _

"dbq=c:\data\book\intranet.mdb"

Set objRstConn=objConn.Execute("tblPhoneMessage" _

,, adCmdTable)

Notice the second argument, dbq. ODBC drivers might call this argument by other names (for example, SQL Server uses “database,” though FoxPro sticks with “dbq”). If you’re using ODBC, you’ll never get the connection string wrong if you use the ODBC32 Administrator to create a file DSN and then copy the resulting arguments to your connection string.

This snippet creates that “fire-hose” cursor I mentioned earlier. The cursor is inside the objRstConn object variable. I use the name objRstConn to remind myself that I’m dealing with an object that is a Recordset generated by a Connection object. I do this because a Recordset object can also be created from the Command object. Although using the Connection object is convenient, I don’t have full access to the entire Recordset object when I use it.

In other routines in this article, I use the objRst object variable instead. Here, my choice of variable names designates a Recordset object. The name in this case indicates that I’ve created the Recordset object from the Command object. This gives me full access to the Recordset object so that I have complete control in how that Recordset object is defined.

If you forget to invoke the Open method of the Connection object, it’s like talking on the phone before you dial. ADO will complain with the message “Invalid operation on closed object.” All ADO programs must have something like the first two commands in this routine.

Here’s the code to create a Recordset from the Command object in ADO. Not all data providers can use commands, so the Command object is optional. When it’s used, the Command object assumes a Connection object has been created, so this code should be regarded as a continuation of the previous routine:

Set objComm=Server.CreateObject("ADODB.Command")

Set objComm.ActiveConnection=objConn

'The following two alternative connect strings are

'from a File DSN and a System DSN respectively

' objComm.ActiveConnection = _

'  "filedsn=c:\Program Files\Common Files\" & _

'  "ODBC\Data Sources\intranet.dsn"

' objComm.ActiveConnection = "intranet"



objComm.CommandText = _

"SELECT message FROM tblPhoneMessage"

Set objRstComm = objComm.Execute(,,adCmdText)

If you forget to use Set in the second statement in this routine, you’ll create a copy of the connection string in a second connection object (I prefix object variables with “obj” just to remind me to use Set).

Notice some fine points here. The Command object requires you to set two properties before you can invoke the Execute method. The Command object must know where to look for the command, so the ActiveConnection property must be set to a Connection object or to a connect string provided through a System DSN, a File DSN, or directly. The Command object must also know what the command is through the CommandText property. This is set either to the name of a statement in the database or to the actual text of the command to be sent to the database. If you forget either of those properties, you’ll receive one of the following ADO errors:

Missing Property Resulting error
ActiveConnection Invalid operation on object with a closed connection reference.
CommandText No command has been set for the Command object.

Keen-eyed readers will have noticed that the only argument I passed to the Execute method of the Command object was the third one. The adCmdText constant tells the data provider not to bother looking for database objects and that the CommandText is just that — text. If you look closely at the first routine, you’ll see that I passed the constant adCmdTable to the Execute method of the Connection object. This sets the CommandType type property in the Command object to “table.” Either way, the data provider doesn’t have to waste time figuring out how to implement the command. You can also call a querydef or stored procedure by setting the CommandType property of the Command object to adCmdStoredProc.

Remember that the Command object includes the Parameters collection. Parameter objects deserve coverage beyond the scope of this article, for they’re a powerful and convenient resource for busy developers.

Recordsets

The final object that actually yields a Recordset is the Recordset object. Like the Command object, the Recordset object needs a Connection object for the Recordset’s ActiveConnection property. It also needs a Source property that is the equivalent of the CommandText property in the Command object. Here are the error messages that will crop up if you omit either of these parameters:

Missing Open Argument or Recordset Property Resulting Error
ActiveConnection Invalid operation on object with a closed connection reference.
Source No command has been set for the Command object.

I haven’t included all the combinations of syntax you can use with the Open method of the Recordset object. I have, however, demonstrated the two basic styles: the “shortcut” technique of using Open method arguments (for the fire-hose cursor), and the “explicit” strategy of setting Recordset object properties (for the keyset cursor).

Here’s the code to create a fire-hose cursor and a keyset cursor:

' To create fire-hose cursor

'Set objRst = _

'  Server.CreateObject("ADODB.RecordSet")

'objRst.Open _

' "SELECT caller FROM tblPhoneMessage", "intranet"



' To create updateable, scrollable keyset cursor

Set objKset =  _

Server.CreateObject("ADODB.RecordSet")

'Define the properties of the Recordset object

objKset.Source = "qryPhoneMessagesActionForMPC"

objKset.ActiveConnection = objConn

objKset.CursorType = adOpenKeyset

objKset.LockType = adLockOptimistic

' Open the Recordset

objKset.Open ,,,, adCmdStoredProc

This routine uses two new properties, CursorType and LockType. In Access, it was easy to ignore these properties and let DAO make the decisions for you. In ADO, you’re in complete control, and if you don’t specify a CursorType or LockType, you get the default values that produce the fire-hose cursor.

Also, note that the Open method in the Recordset object has five arguments against the three used by the Connection object. Because I wanted to specify that the Source property was a querydef, the only way I could do that was through the fifth argument in the Open method. There’s no separate property equivalent to the CommandType property of the Command object. Using the querydef gives you all the performance optimizations available at the data provider.

What’s next

This article has covered a few essential features of ADO. However, this new technology has many more capabilities that developers should seek to learn. Since this is the wrapper for the next step in Microsoft’s data access strategy, it’s very important for you to become as familiar with ADO as you are with DAO.

More:

An ADO Tutorial: What is ADO?

 

This entry was posted in Old Material. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.