Chapter 13: Accessing Data with VBA Code
495
Many developers prefer the approach in OpenRecordset2 because it’s easier to see exactly what’s
happening to the Recordset object and where its properties are being set. Although these very
small procedures are easily understood, in larger code segments finding all the references to an object
like rst can be challenging, especially when the VBA statements become long and complex.
As with the other ADO objects, a Recordset object must be declared and instantiated. Like the
Command object, if the Open method is used to populate a Recordset object, an open connec-
tion must be provided as an argument to the Open method.
Recordset objects are used in many different places in this book. Depending on context, the
most commonly used Recordset methods include Open, Close, MoveFirst, MoveNext,
MovePrevious, and MoveLast.
Navigating Recordsets
Recordsets wouldn’t be much use if all you could do is open and close them, or if the GetString
method was the only way to use the data in a recordset. Depending on context, the word recordset
means several different things:
l The rows of data returned by a queryl (^) The data bound to an Access form
l The object filled with data as the result of an ADO operation
In all cases, however, a recordset is a data structure containing rows and columns of data. The
rows, of course, are records, while the columns are fields.
It makes sense that Access provides ways to navigate through a recordset. When viewing a table or
query results as a datasheet, you can use the vertical and horizontal scroll bars or arrow keys to
move up and down, left and right, through the datasheet view of the recordset. It’s not surprising,
then, that ADO Recordset objects support methods for moving through the records contained
in a recordset.
The following procedure demonstrates the fundamental ADO recordset navigation methods. (As
you’ll see in the “Understanding DAO Objects” section, later in this chapter, DAO recordsets sup-
port identically named methods.)
Public Sub RecordsetNavigation()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.Open “Select * from tblCustomers”
Debug.Print rs!CustomerID, rs!Company
rs.MoveNext
Debug.Print rs!CustomerID, rs!Company
rs.MoveLast
Debug.Print rs!CustomerID, rs!Company
rs.MovePrevious