Understanding the Different Types of Recordsets

Modifying data at the source is quick and efficient if you don't need to continually review the data. Sometimes you need to actually retrieve the data. For instance, you might want to evaluate specific values before making a change.

Typically, you retrieve data using the Recordset object. You can think of a Recordset object as a simple data container, which you can execute via the Connection or Command object. If you could see it, a Recordset would look very much like an Access table opened in Datasheet view.

On the technical side of things, a Recordset object represents a cursor. A cursor is often described as the pointer to the current record, but it's really more. It's all the retrieved data, with a pointer to the current record. There are four types of cursors (Recordset objects):

• A dynamic cursor enables you to see changes made by other users. In addition, the cursor (or pointer) can move in all directions. Jet doesn't actually support this type of cursor and defaults to a keyset cursor if you specify a dynamic cursor.

• A keyset cursor is dynamic, but you can't see records added or deleted by other users, although you can see changes made to existing data. You can move the cursor in all directions. If only one user opens the database at a time, a keyset cursor and a dynamic cursor are equivalent.

• A static cursor creates a copy of the data. You can't change the data or see changes made by other users. You can move the cursor in all directions.

• A forward-only cursor is a static cursor that restricts movement. You can only move forward through the records. This is the default ADO cursor.

How you need to use the data determines the type of cursor you choose. The dynamic cursor is the most flexible but requires the most resources (and isn't available in Access). Dynamic cursors can be slow to perform. On the other hand, the forward-only cursor usually responds quickly and requires fewer resources, but it's the most restrictive of the four you can't change data and you can't move backward through the records.

CAUTION

Microsoft Jet doesn't support dynamic cursors, even though, through ADO, you can request one from Jet. When you do, Jet returns a keyset cursor, which does not show you records added by other users that would appear in a true dynamic Recordset. The best workaround is to request your Recordset often so you can work with the most up-to-date data.