A cursor's type, in ADO terminology, indicates some facts about how it behaves, what
you can and can't do with it, and how thrifty or wasteful it is with system resources.
You can set a Recordset's cursor type by setting its CursorType just before
you open it, as shown in Listing 9.19
LISTING 9.19
SETTING A Recordset'S CURSORTYPE PROPERTY
rsEmployees.CursorType = adOpenStatic
rsEmployees.Open
NOTE - The Most Economical Cursor: The most efficient
cursor in terms of resource usage is a Forward-Only cursor with its lock type
set to Read- Only. This type of cursor is also known as a "firehose cursor." See
the section titled "Using Locking Strategies to Ensure Data
Integrity" for more information on the Read-Only lock type.
NOTE - Static Cursors and Client-Side Cursors: When
you set the CursorLocation Property to Client-Side, the only available CursorType is Static
The following sections discuss the four cursor types available from the ADO Cursor library.
-
Forward-Only Cursors
-
Static Cursors
-
Keyset Cursors
-
Dynamic Cursors
Forward-Only Cursors
A Forward-Only cursor behaves a lot like sequential file access: It only furnishes one record at a
time, and then only in strict order from the beginning to the end of the rowset.
In other words, you can't use a Forward-Only cursor to skip around in a Recordset's rows.
You can only move forward one record at a time until you reach the EOF condition at the end of
the Recordset.
If you attempt to use any other Recordset navigation method besides MoveNext, you will generate a runtime
error.
A Forward-Only cursor is the default ADO cursor, because it consumes the least resources of all
cursor types.
Static Cursors
Static cursors are less economical than Forward-Only cursors, but they allow greater flexibility of
movement through the rowset. A Static cursor supports navigation in all directions, and it enables you
to make repeat visits to the same record during the same session.
The biggest drawback to a Static cursor is the fact that its rowset doesn't get updated
with concurrent changes made by other users.
If User A opens a Static cursor on a set of records and User B makes changes to the records
during User A's session, for example, User A will not see the changes made by User B. To
see User B's changes, User A's Static cursor would have to close and then be reopened.
The user can make changes to the Static cursor's Recordset, but (once again) the user cannot see
changes made by others during the time that the cursor is open. This includes additions and deletions
to the records as well as editing changes to individual records.
Keyset Cursors
Keyset-type cursors have the same freedom of movement in any direction as Static cursors. In
addition, Keyset cursors can immediately see changes to existing records made by other users. However,
additions and deletions made by other users are not visible to a Keyset-type cursor.
Dynamic Cursors
Dynamic cursors have all the flexibility and visibility of Keyset-type cursors
with an extra enhancement: Additions and deletions made by other users are visible
to a Dynamic cursor. Dynamic cursors are, however, the biggest resource hogs,
so you should be very sure that you absolutely need a Dynamic cursor before deciding
to use one.