Chapter 13: Accessing Data with VBA Code
505
Debug.Print “Number of records: “ & rs.RecordCount
Set GetRecordset = rs
rs.Close
End SubNotice that the locally declared Recordset object (rs) is assigned to the function just before the
function ends. This is one way for a procedure to build recordsets without having to duplicate the
code setting up the recordset and running the QueryDef every place a recordset is needed by an
application.
The DAO Recordset object
Recordset objects are declared and set to a particular table, query, or ODBC data source within
your application. Using a Recordset object’s methods you can update, edit, and delete records,
move forward and backward within the recordset, or locate specific records using the Find and
Seek methods.
A Recordset object can be a Table, a Dynaset, or a Snapshot type; the type you specify
depends on your needs. For example, suppose you only wanted to scan through a table to search
for a particular value of a field. A Snapshot, which is a read-only view of your data, would prob-
ably be a good choice. Or maybe you’d like to query a table on the fly, but the query depends on
user input. In this case, you might build a SQL statement based on an input value, and use the
SQL statement to build a Dynaset-type recordset.
You specify the type of recordset using the dbOpenTable, dbOpenDynaset, and dbOpen-
Snapshot constants as arguments of the OpenRecordset method of a Database object. The
following example shows how to open a Snapshot-type recordset based on a SQL string.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = “SELECT * FROM tblCustomers”
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)If you don’t explicitly choose a type of Recordset, Access uses what it believes to be the most
efficient method. You can’t open an ODBC data source using the dbOpenTable option. Instead,
you must use the dbOpenDynaset and dbOpenSnapshot constants.
As you’ll see in many different places in this book, there are a number of different ways to open
DAO recordsets. The following procedure illustrates just one of these techniques. In this particular
example, the recordset is created directly against tblCustomers, and each field in every row in
the table is displayed in the debug window (the Field object and Fields collection are dis-
cussed in the next section):