Determining the proper cursor type to use with a Recordset in ADO is often a trial-and-error effort. The text “ADO: ActiveX Data Objects” by Jason T. Roff summarizes the four types of cursors supported by ActiveX Data Objects:
- A dynamic cursor (
adOpenDynamic) allows you to move freely throughout your recordset, without restrictions, except when your provider does not support bookmarks. The dynamic cursor is by far the most versatile cursor available to the ADO developer. The major drawback of the dynamic cursor is that it is extremely resource- and time-intensive because of its ability to show, in real time, changes made by other users.
- The keyset cursor (
adOpenKeyset) is very similar to the dynamic cursor in functionality, with the exception of the real-time viewing of new records created, deleted, and modified by other users. Unlike with the dynamic cursor, the ability of the data provider to offer the use of bookmarks is mandatory. This is so that the keyset cursor can offer something called batch updates.
- The static cursor (
adOpenStatic) is vastly different than the previous two. It still allows your application to move freely through the recordset. The difference is that you are unable to dynamically view changes made to the records by other users within your static recordset. The static cursor is like a snapshot of the data within your data source at a specific point in time.
- The forward-only cursor (
adOpenForwardOnly) is the fastest available. It is similar to the static cursor in that it presents a snapshot of the data at a particular point in time, but it lacks the ability to move the record pointer backwards.