Cursors can be either updateable or non-updateable. If you only need to display information and not diddle with it, the non-updateable is the fastest. The provider simply passes you the data and forgets about it! There is no need to keep tabs on the data to see if it has been changed. Therefore, this is the fastest cursor to use.
Scrollable cursors, which can be updateable or non-updateable, permit you to move (scroll) back and forth in the recordset. If you only need to blast out some data to a grid or HTML page, a non-scrollable cursor will give a performance boost. This is because there is no need to track where you are in the recordset.
Keyset-Driven cursors take note of a key you may have in your database. Under normal circumstances, when you request records from a data source, you get the entire package. However, when a Keyset cursor is selected, only the keys are returned, giving a performance boost. Then, when you scroll through the recordset, the data for the keys is retrieved. However, when you first invoke the cursor, both keys and data are returned so you can see something in your bound control. When you scroll, and the data is not in the current batch of data, the Keyset fetches the next block. This way it only has to manage small keys rather than large chunks of data.
Dynamic and Static cursors determine what data is available in the cursor at any point in time. As the name implies, Static cursors only contain data that was placed in the cursor when it was created. However, with a Dynamic cursor, any new records that are added to the recordset are available. It's like a living cursor.
We can use our old friend the Object Browser to see the cursor types available to us:
With ADO, we have the choice of four types of cursors. We can simply use constants to tell the recordset which to use:
| Cursor type | Constant |
| Static cursor. This is the one to use for generating reports or finding data. Additions, changes, or deletions by other users are not visible. | adOpenStatic |
| Forward-only cursor. This is the default. It is identical to the Static except that you can only scroll forward. The fastest cursor this side of the Pecos Mountains. | adOpenForwardOnly |
| Dynamic cursor. Additions and deletions by others are visible. All movement is supported. But some providers don't support this cursor type. | adOpenDynamic |
| Keyset-driven cursor. This is similar to a Dynamic cursor except you can't see records others add. If another user deletes a record, it is inaccessible from your recordset. | adOpenKeyset |
We can also tell the recordset how to lock our data while it's being manipulated via the cursor:
| Lock Type | Description |
| AdLockReadOnly | Default. Read-only: you cannot alter the data. |
| AdLockPessimistic | Pessimistic locking, record by record—the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately upon editing. |
| AdLockOptimistic | Optimistic locking, record by record—the provider uses optimistic locking, locking records only when you call the Update method. |
| AdLockBatchOptimistic | Optimistic batch updates—required for batch update mode as opposed to immediate update mode. |
The syntax for opening an ADO recordset is like this:
adoRecordset.Open Source, ActiveConnection, CursorType, LockType, OptionsThe Source argument is Optional. This is a valid command object variable name, or it might be an SQL statement, a table name, or a stored procedure call.
The ActiveConnection is also optional. This is either a valid Connection object variable name, or a String that contains our ConnectionString.
The CursorType is also Optional. This is simply one of the cursor constants that tells the provider the type of cursor to use when opening the Recordset.
There are several other options that we can provide the recordset as well. You may recall that when we build the connect string in our ADO data control, these options were provided in a drop down box. They may have looked a bit cryptic back then, but now we can see what they mean:
| Description | Constant |
| The provider should take the source as a text description of a command, such as a SQL statement | adCmdText |
| ADO should generate an SQL statement to fetch all rows from the table in Source | adCmdTable |
| The provider should return all of the rows from the table named in Source. | adCmdTableDirect |
| The provider should treat the Source as a stored procedure | adCmdStoredProc |
| The type of command in Source is unknown. You should not use this! It is the slowest of all cursors. | AdCmdUnknown |
| A saved recordset should be restored from the file names in Source | adCommandFile |
| Source should be executed asynchronously | adFetchAsync |
| After the initial quantity of records specified in CacheSize is fetched, any remaining rows should be fetched asynchronously | adFetchAsync |
When might you use some of these options? Well, a good illustration would be when using a transaction.