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")
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
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

Constant Integer Description
Provides a static copy of the records (you can't see additions, changes or deletions by other users). You can only move forward through the recordset. Forward-only is the ADO default cursor type.
Provides a static copy of the records (you can't see additions, changes or deletions by other users), but all types of movement are enabled.
Dynamic requires more overhead, because updates are immediate and all types of movement are enabled. The dynamic cursor isn't currently supported by the Microsoft Jet OLE DB Provider, and therefore defaults to a keyset cursor when adOpenDynamic is applied to a Jet database.

Similar to the dynaset recordset type and dynamic cursor, but the resulting recordset is based on a single table. Since the dynamic cursor type isn't currently supported by the Microsoft Jet OLE DB Provider, Jet defaults to a static cursor when you apply the adCmdTableDirect option.
(no equivalent)
1 Existing records at time of creation are updateable. You can't see additions or deletions. All types of movement are enabled.

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


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


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.