Everything You Need to Know About ADO Recordsets

Russell Sinclair

The title of this article is almost certainly a lie. As I’ll show you, the number of methods, properties, and events in the Recordset object makes it impossible to discuss everything in one article. However, I want to emphasize the similarities and differences between the DAO and ADO Recordsets. If you’ve used the DAO records to perform record-by-record processing, you need to understand what’s special about the ADO Recordset object to use it effectively, so you might append "…For DAO Programmers" to the end of this article’s title.

The Recordset object provides access to individual records and fields in a data store. It allows navigation through data and editing of data. You can create a Recordset by using either of the Command or Connection objects’ Execute methods. You can also create a Recordset with VBA’s New keyword and then use the object’s Open method to populate the object with records. All of these code snippets create the same Recordset object:

Dim conn As Connection
Dim cmd As Command
Dim rs As Recordset

'use a Connection object
Set conn = New Connection
conn.Open "File Name=MyDataLink.UDL"
Set rs = conn.Execute "Select * From Authors"

'use a Command object
Set cmd = New Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "Select * From Authors"
Set rs = cmd.Execute

'create a Recordset
Set rs = New Recordset
rs.Open "Select * From Authors",,, adAsyncFetch

The Open method of the Recordset object can be synchronous (control stays with ADO until the data is returned to the Recordset) or asynchronous (as soon as the Open method is issued, your code continues processing). The Options parameter of the Open method controls whether the Open is executed synchronously or asynchronously. In my example, I’ve chosen an asynchronous fetch.

Before plunging into the details of the Recordset object, take a moment to review the sidebar "Client- and Server-Side Cursors."

Differences and similarities

As you can see in >Table 1, the list of properties that the Recordset object has is a long one. However, those of you with experience programming in DAO will recognize many similarities. Bookmarks work almost identically to the way they do in Jet, for instance. A Bookmark on a record will allow you to move throughout a Recordset and still return to exactly the same record. The BOF and EOF properties can still be checked to ensure that you’re not trying to move past the first or last records, and the Sort property allows you to specify which fields to sort a Recordset on.

Table 1. Recordset properties.

On the differences side, the RecordCount property isn’t quite the same as it is in DAO. In DAO, the RecordCount property reflects the number of records accessed, so, to get the total number of records in a Recordset, you had to move to the last record in the Recordset. In ADO, the RecordCount property, if available, always contains the total number of records in the Recordset, no matter how many records you’ve accessed. Unlike DAO, in ADO the RecordCount property isn’t always available. Not all data providers support the property (though Jet does), nor do certain types of Recordsets (for instance, the forward-only Recordset).

Unlike DAO, ADO doesn’t support the Find method if it has more than one criterion. For example, this code works fine under DAO but will fail under ADO:

rst.Find "CustomerName = 'My Friend' AND " & _
  "ContactTitle = 'Manager'"
strCustomerID = rst!CustomerID

One way to convert this code to work with an ADO Recordset would be to filter the Recordset and find the record in the filtered data, like this:

rst.Filter = "CustomerName = 'My Friend' " & _
  " AND ContactTitle = 'Manager'"
rst.MoveFirst
strCustomerID = rst!CustomerID
rst.Filter = adFilterNone

As with any technique that depends on fetching more records than you need into a Recordset and then selecting the ones that you actually want, you’re farther ahead to issue a query that retrieves just the records that you needed in the first place.

Brand-new properties include the MaxRecords property. Setting this property limits the number of records that can be returned by a Recordset object. You can use this property when you let your users run their own customized queries and so control your users’ ability to impact other users by hogging the server with a long-running query. You have to set this property before calling the Recordset’s Open method.

Cursors and locks

The CursorType property defines how the data in the Recordset is synchronized with the database and must also be set prior to executing the Open method. The different cursor types include:

You should be aware that the CursorType you request isn’t always the one that you’ll get. Since ADO is just a mechanism for making requests to a data provider, you’re dependent upon what each data provider can supply. For instance, the Jet provider for ADO doesn’t support dynamic cursors.

The LockType property controls when records will be locked against changes. Setting this property to adLockPessimistic will lock the record as early as possible. Locking a record early prevents two users from making changes to the same record by keeping the second user from editing the record. Using adLockOptimistic defers locking the record until the data is actually being changed. This allows two users to be updating a record at the same time. The adLockReadOnly setting is self-explanatory, but the final setting–adLockBatchOptimistic–is more interesting. As the name implies, adLockBatchOptimistic defers locks until the record is actually updated. However, this setting also defers all updates to the database until you use the Recordset object’s UpdateBatch method.

Recordset methods

The Recordset methods, shown in Table 2, are just as numerous as the Recordset properties. They’re also very similar to the methods of the Recordset object in DAO, but, again, there are some important differences. One, which you might have already noticed, is that there’s no Edit method in ADO. In ADO, the Edit method isn’t necessary–you just modify the fields as necessary and then save the changes back to the Recordset by calling the Update method. It isn’t even necessary to call the Update method either if you use any of the Recordset’s Move methods. Calling a Move method will implicitly call the Update method before moving to the new record. This code, which would lose your changes in DAO, works in ADO:

rst!MyField="new value"
rst.MoveNext

Table 2. Recordset methods.

You might have noticed that there are three Cancel methods and two Update methods. The Cancel method itself is used only when you’re opening a Recordset using client-side cursors and an asynchronous Open call. Calling the Cancel method will stop the retrieval of the data in much the same way that the Cancel method of a Connection object will stop the Connection from opening.

The CancelUpdate command cancels any changes you’ve made to the current record, including the creation of a new record. This can be used at any time, with server- or client-side Recordsets, to ensure that modifications to a record aren’t saved back to the database.

If you use a Recordset with its LockType property set to adLockBatchOptimistic, updates that you make to records aren’t immediately sent back to the data source. Instead, you must explicitly send data back to the server when you’re done. To do this, you call the UpdateBatch method. Before calling this method, it can be useful to set the Recordset’s MarshalOptions property. Setting this property to the ADO constant adMarshalModifiedOnly will tell ADO only to send modified records to the database, thereby reducing the load on your network. With this scenario, if you don’t want to save any records back to the database, use the CancelBatch method to tell the ADO provider that you won’t be sending the data back to the server. This frees up resources used by the system to track the data that you’re currently using.

One function of the Update method that can be used in any Recordset is the ability to modify values in a record in a single line of code. The Update method can take arguments for fields and values. These arguments must be passed as arrays, and the simplest way to do this is to use the VBA Array function. For example, if you wanted to update the City, Region, and Country in a record in tblCustomers, you could do it with the following code:

rst.Update Array("City", "Region", "Country"), _
  Array("Baltimore", "MA", "USA")

This code updates these three fields in a single line, possibly saving you a few lines of code and some time, since it reduces the number of calls to the Recordset object (this technique also works with the AddNew method). This technique can make your code difficult to read if you rely on it too heavily.

ADO provides both a Resynch method and a Requery method for refreshing your Recordset with data from the server after you’ve made some changes to it. The Resynch method allows you to refresh the data for either the current record or for all records in the Recordset without having to re-execute the whole query that the data is based upon. Instead, the method checks the values of the currently held records and their fields and simply updates any changes to these records from the database. Calling the Requery method, on the other hand, is functionally equivalent to closing the Recordset and re-opening it. Another thing to keep in mind with these two methods is that the Resynch method can be called at any time and can be used to overwrite the modified contents of records that are currently being edited. If you run the Requery method while you’re editing or adding a record, you’ll receive an error. Also, the Requery method can only be used against read-only client-side Recordsets.

There are two methods in the Recordset object that move data from the Recordset to standard VBA variables. The GetRows methods will return specified records and place them directly into a two-dimensional array. The GetString method copies records to a string, separating the fields and records with specified delimiters. This can be useful for saving data to a text file or preparing it for publication on a Web site.

Another neat property is the CompareBookmarks method. This method gives you the ability to compare two Recordset bookmarks and determine where they lie in the Recordset in relation to each other: before, after, equal, or not comparable.

Recordset events

See Table 3 for a complete list of the Recordset object’s events. The events whose names are prefixed with "Will" are fired before an activity begins (but after you’ve called the method that invokes the activity). The events whose names end in "Complete" are fired after the activity completes. Many events in ADO will be raised even if an error has occurred. As with the Connection object, you should always check the value of the adStatus parameter that’s passed in each event to make sure that everything is okay before executing any code in your event procedure.

Table 3. Recordset events.

Further changes to the record don’t raise the RecordChange events. Instead, only the FieldChange events will be raised. When you send the data back to the database, the RecordChange events will be raised once for each record that’s been modified. However, the order of events will be a WillChangeRecord for each modified record, followed by a RecordChangeComplete. You should check the value of the argument adReason in these events to determine why the event has been raised. In my previous example, for instance, the first time the events are raised, adReason returns a value corresponding to the constant adRsnFirstChange. The second time through, the value is adRsnUpdate.

Fields collection

Every Recordset object has a Fields collection. Each object in the Fields collection represents one field in a record in the Recordset. The various properties of an individual Field object are shown in Table 4.

Table 4. Field properties.

The Fields collection behaves much like any other collection in VBA. It has Append, Delete, Refresh, and Count methods and properties. However, unlike the Fields collection in DAO, it’s not necessary to create a new Field object in code and then add it to the collection. In ADO, you can create and append a Field to the collection in a single line of code. For example, to add a 50-character text field (type adVarChar in ADO) to a Recordset, you could simply use the following code:

mrst.Fields.Append "MyField", adVarChar, 50

You can’t add a Field to a Recordset that’s bound to a data source, which might have you asking when you would use this feature. One of the most interesting functions in ADO is the ability to create and completely define your own Recordsets in code. This example creates a Recordset and then adds two fields to it:

Set mrst = New Recordset
mrst.Fields.Append "FirstName", adVarChar, 15
mrst.Fields.Append "LastName", adVarChar, 25

These Recordsets are free from any attachment to a database and can be used much like an array of user-defined data types. However, they’re much more versatile. If you look at frmRecordsetWithEventsFreeForm in the sample database, you’ll see that this form isn’t bound to any data source and uses values that have been filled in on the fly. This can be very useful in those places where you’d normally use a temporary table. With Access 2000, you can even bind that Recordset to a form to have it display your data.

The ADO Recordset is a tremendously powerful object, offering features and abilities beyond those built into the DAO version of the object. With the information in this article, you’re ready to start using the object effectively in your own applications.

Sidebar: Client- and Server-Side Cursors

When working with the ADO Recordset object, it’s important that you understand the difference between client-side and server-side Recordsets. This is especially true if you switch between using Microsoft Access with flat file databases (like Jet’s MDB files) and remote databases like SQL Server, Oracle, and Informix.

In ADO, you control where your Recordset is located by setting the Recordset’s CursorLocation property to adUseClient or adUseServer. You must set this property before using the Open method.

With client-side Recordsets, all data manipulation, including tracking of indexes and records, takes place on the client machine. Server-side Recordsets, on the other hand, maintain a constant connection to the database. Updates to data are transmitted to the database as soon as a record is updated in the client. All of the processing of data takes place on the server, and only the resulting data is sent back to the client.

DAO doesn’t provide a choice on where your processing will take place. Access and DAO treat all databases–even remote databases–as if they were local, flat file databases. When you use a database from Access, changes to records are immediately transmitted to the database. Also, table and record locks are tracked using the LDB file that resides in the same directory as the MDB file, updated by the client machine. Since there’s so much action going on between the client machine and the database file, DAO can use up a great deal of network resources.

Using client-side ADO Recordsets with a remote database allows you to break this network connection while you’re manipulating the data, saving on network access load and data access time. With large amounts of data, you’ll definitely notice a difference when using client-side Recordsets from Access. As I’ve noted in previous articles, this discussion applies when you’re using server databases, not when you’re using Jet. With Jet, you should stick to server-side cursors for all of your data retrievals.