Comparing DAO and ADO Recordsets

This month, Russell turns his attention to the Recordset object and shows the similarities and differences between the DAO and ADO Recordsets.

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.

Property Purpose
AbsolutePage Specifies which page the current record inhabits
AbsolutePosition Location of the current record in the Recordset
ActiveCommand The Command object from which the Recordset gets its data
ActiveConnection The Connection object or string that the Recordset object uses
BOF, EOF Flags specifying that the current position is the beginning (BOF) or end (EOF) of the Recordset
Bookmark Marker to uniquely identify a record in the Recordset
CacheSize Determines how many records will be saved locally during modification or viewing
CursorLocation Where processing of the data will take place, on the server or on the client
CursorType Type of cursor to use with the Recordset
DataMember, DataSource Used by Visual Studio Data Environment to bind Recordsets to controls
EditMode Edit state of the current record
Filter Filter applied to the Recordset to limit records
LockType Database locking schema setting share rights for data
MarshalOptions Specifies which records will be returned to the data store after modification, all or modified only
MaxRecords Limits number of records that a Recordset can return to the client
PageCount Total number of pages in a Recordset
PageSize Size of a record in paging
RecordCount Number of records in a Recordset
Sort Sorting to use to organize data in Recordset
Source Original SQL string or command string upon which the Recordset is based
State Indicates the current state or action of the Recordset object
Status Status of the current record with respect to modifications and updates
StayInSync Used with hierarchical Recordsets to set whether child Recordsets should move as movement occurs in the parent Recordset

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:

  • Static: You retrieve all of the data for all of the records that meet your query. Changes to the data made by other users after you retrieve the data are hidden from you (adOpenStatic).
  • Forward Only: This cursor permits you only to use the MoveNext method. You’ll see changes to records and additions to the recordset made by other users (adOpenForwardOnly, the default).
  • Keyset: You retrieve only the primary keys for the records that meet your query. As you process the records, the data for each record is retrieved. As a result, you see changes to the records made by other users but not any additions (adOpenKeyset).
  • Dynamic: Your query is rerun as you process the Recordset. You see the current data for each record and also pick up any new records added after you issued your query (adOpenDynamic).

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.

Method Purpose
AddNew Adds a record to the Recordset and makes it the current record
Cancel Cancels a pending asynchronous Open or Execute call
CancelBatch Cancels a pending batch update
CancelUpdate Cancels changes made to the current record
Clone Creates a cloned copy of the Recordset
Close Closes the Recordset
CompareBookmarks Compares two bookmarks to determine their relationship to one another
Delete Deletes the current record
Find Finds a record matching a specific criterion
GetRows Copies a specified number of rows from a Recordset to an array
GetString Copies a specified number of rows from a Recordset to a string
Move Moves to a specified record in the Recordset
MoveFirst, MoveLast, Moves to another record in the Recordset relative to the MoveNext, MovePrevious current position
NextRecordset Returns a Recordset object representing the next Recordset in multiple-Recordset returning functions
Open Opens the Recordset with the specified options
Requery Requeries all records in the Recordset by re-running the original command
Resynch Refreshes data in the Recordset from the data source
Save Saves the Recordset to disk
Supports Checks a provider to determine what Recordset functionality is supported
Update Saves changes to the current record to the data source
UpdateBatch Updates records in the Recordset when using batch updating

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.

Event Purpose
EndOfRecordset End of the Recordset has been reached
FetchComplete Asynchronous fetch operation has completed
FetchProgress Fetch progress continuing in a long asynchronous operation
FieldChangeComplete Field or fields have changed
MoveComplete Move to another record has completed
RecordChangeComplete Record or records have changed
RecordsetChangeComplete Asynchronous Recordset change has completed
WillChangeField Field or fields will change
WillChangeRecord Record or records will change
WillChangeRecordset Asynchronous Recordset change will occur
WillMove Current position in the Recordset will change

While Microsoft recommends that you don’t use events with the Jet data provider, the sample database available in the accompanying Download file includes events so that you can see how they work. To see a typical sequence of events, in the sample database, open the Debug window and then open the form frmRecordsetWithEvents. This form has a Debug.Print command in each event of the Recordset object in the form’s code so that you can see the order of the events. You’ll notice that the first time you edit a record, the events happen in the following order: WillChangeRecordset, WillChangeField, FieldChangeComplete, and then RecordChangeComplete. The RecordChange event occurs because ADO places a flag on the record to indicate that the record has been modified in some way. This flag enables ADO to identify modified records when the data is sent back to the database.

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.

Property Purpose
ActualSize Actual length of the data contained in a field in a record
Attributes Characteristics of the field
DefinedSize Maximum defined length of the field in the database
Name Name of the field object
NumericScale The number of decimal places to which a number in a field will be resolved
OriginalValue Value originally stored in the field when the data was retrieved from the database
Precision The maximum number of digits that are used for a number in a field
Type Data type of the field: one of the ADO DataTypeEnum constants
UnderlyingValue Value currently stored in the database for a field
Value Value assigned to the field object in the current context

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.

 

Your download file is called Sinclair_ADO.accdb

 

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.

 

Other Related Articles include

Everything About Using Parameters from Code

About Russell Sinclair

Russell Sinclair is a Program Manager at Microsoft working on Microsoft Access. He has over 14 years of experience as an Access developer, is the author of "From Access to SQL Server" and was a Contributing Editor to Smart Access.
This entry was posted in VBA. 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.