Understanding ADO's Default Cursor Type

If ADO recordsets make you feel like you should return to kindergarten, you're not alone. ADO recordsets may be more flexible than their DAO counterparts, but mastering that flexibility may take a bit of time because the ADO recordset object is quite a bit different from what we're used to with DAO. In addition, it's hard to find consistent documentation because there are so many ways to do the same thing. One change that might cause you considerable trouble is the recordset's new default type, or cursor type as it's known in ADO. In this article, we'll provide an overview of ADO's cursor types and how they compare to what you've worked with in DAO. In particular, we'll point out how to avoid problems with ADO's default cursor type.

ADO and DAO's different defaults

With DAO, if you don't specify a recordset type when opening a recordset object, DAO discerns the most appropriate type, starting with the table type. A table recordset is only valid when you're working with the current Microsoft Jet workspace. When the table type isn't appropriate, DAO defaults to a dynaset, then a snapshot, and finally a forward-only type (which is also the default type when you create a recordset in an ODBC workspace).

ADO is more restrictive; if you don't specify a cursor type, ADO returns a forward-only type. In DAO, a forward-only recordset is generally faster and often more efficient than other types for certain tasks. ADO retains the type for compatibility only, as an ADO forward-only recordset doesn't generally perform any better than other types. However, the jury's really still out on this issue--you'll find documentation to argue both sides.

Performance aside, forward-only types of recorsdsets have impaired functionality because you can't move freely through the records. As the name indicates, you can only scroll forward using the Move method--you lose the ability to move backward by specifying a negative value with the Move method. Therefore, this recordset is best used when you only need a single pass through the records.

The problem in action

Developers will often tell you not to depend on defaults--to always specify properties, even when the value you specify is the default. That way when problems arise a forgotten default option doesn't cause further problems by going undetected. There's really no right or wrong, but a specified argument leaves nothing to chance.

A simple example of this problem at work is DAO's RecordCount property. The DAO procedure shown in Listing A displays the number of records in the Northwind.mdb Customers table. As long as you're not working with an OBDC connection, this procedure should work. The MoveFirst method isn't always necessary, but we've included it just in case the default DAO recordset type doesn't define the cursor's destination.

Listing A: DAO default example

SAMPLE ONE: (below)

Our example creates a recordset from the Customers table in the Northwind database that comes with Access. Because we've failed to specify a recordset type in the OpenRecordset method, DAO defaults to the table recordset type. When applying this procedure to your own work, the recordset may default to a dynaset or a snapshot, but all three support the RecordCount property.

The ADO equivalent

When it comes time to convert the above procedure to ADO, you might try the procedure shown in Listing B. Remember, we didn't specify the recordset type in DAO, so it stands to reason that you might skip this step with the ADO cursor type. For our purpose, we'll treat the term cursor as the equivalent to the DAO recordset type. Unfortunately, this procedure will return an error because the ADO recordset defaults to a forward-only cursor. You can't move backward, so the MoveLast method returns an error (Rowset does not support fetching backward). Note that if you want to experiment with this code yourself, you must ensure that you have a reference to the

Listing B: ADORecordCount() function

SAMPLE TWO: (below)

TO SEE COMPLETE ARTICLE: http://msdn.microsoft.com/library/periodic/period01/ima0501.htm