Understanding ADO's Default Cursor TypeThis 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 Typeby 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 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.
a static copy of the records (you can't see additions, changes or deletions by
other users), but all types of movement are enabled.
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.
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.
records at time of creation are updateable. You can't see additions or
deletions. All types of movement are enabled.
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.