Understanding ADO's Default Cursor Type

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Understanding ADO's Default Cursor Type

by Susan Sales Harkins

Application: Access 2000

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

Function RecordCount()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(Name:="Customers")
rst.MoveFirst
rst.MoveLast
MsgBox rst.RecordCount
Set rst = Nothing
Set db = Nothing
End Function

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 Microsoft ActiveX Data Objects library.

Listing B: ADORecordCount() function

  Function ADORecordCount()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Set cnn = CurrentProject.Connection
rst.ActiveConnection = cnn
rst.Open Source:="Customers", ActiveConnection:=cnn
rst.MoveFirst
rst.MoveLast
MsgBox rst.RecordCount
End Function

Recordset types vs. cursor types

At this point, we've only touched on the different recordset types and cursor types. The desired cursor type can be specified as an integer value or one of the intrinsic constants listed in Table A. For a comparison reference, we've included the corresponding DAO recordset constants and integers in the table. Although we won't work with all the cursor types in this article, you should be familiar with them in order to work successfully with ADO recordsets.

Table A: Cursor type constants and descriptions

A solution

The solution for our ADO procedure is simple--specify a cursor type in the recordset's Open method. The Microsoft Jet OLE DB Provider doesn't support the adOpenDynamic and adCmdTableDirect cursors, so they won't work in our example. In addition, we need to avoid the forward-only cursor, which is what our current procedure produces. That leaves the static and keyset cursors and either will work in our example. To fix our function, replace the statement

  rst.Open Source:="Customers", _ActiveConnection:=cnn

with

  rst.Open Source:="Customers", _ActiveConnection:=cnn, _CursorType:=adOpenKeyset

or

rst.Open Source:="Customers", _ActiveConnection:=cnn, _CursorType:=adOpenStatic

Since we're just returning the record count of the recordset, the differences between the two cursor types are not important.

And that's not all

ADO's cursor type isn't the only argument that affects the resulting recordset. We've just reviewed a simple example of how ADO's default cursor type can be a problem. Depending on the complexity of your task, you should also consider the Open method's LockType and Options parameters. In addition, the OLE DB provider is an extremely important component of the ADO recordset--not all options are supported by all providers. In fact, if you're using the Microsoft Jet OLE DB Provider, there's no compelling reason to convert your existing DAO code to ADO--just keep using DAO.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.