Since Recordset objects represent a single row of data, VBA programmers need a way to iterate through a list of rows. The ability to maneuver through a result set is implemented through database cursors.
In database terms, a cursor is a structure that names and manages a storage area in memory. Programmers use cursors to point to a row of data in a result set one row at a time. The concepts of a cursor and a result set are depicted in Figure 9.1.
A cursor points to one row in a recordset.
Using a combination of other structures such as loops and objects, programmers navigate through a recordset with the cursor pointing to the current row. You can think of programming with cursors as similar to file processing (discussed in Chapter 7, "Debugging, Input Validation, File Processing, and Error Handling") where you open a data file and read one record at a time. When programming with cursors, you establish a cursor and move the cursor's pointer to one row in a recordset at a time.
When working with ADO's Recordset object, you can specify one of four cursor types in the CursorType property. Table 9.3 outlines this.
You should use forward-only (adOpenForwardOnly) cursors when updating rows is not required and reading rows in a result set from start to finish is acceptable. If you require dynamic updates (adOpenDynamic) in your result set, a dynamic cursor type is recommended.
In addition to cursor types, ADO allows programmers to specify a cursor location for the Recordset and Connection objects via the CursorLocation property. Depending on the location of your database and the size of your result set, cursor locations can have a considerable effect on your application's performance.
As outlined in Table 9.4, cursor locations can be either server side or client side.
Was this article helpful?