Access™ 2007 VBA Programmer's Reference

8.4. Using Recordsets

Recordset operations are one of the cornerstones of Access VBA, enabling you to directly read, update, add, and delete records in Access tables and queries. You'll explore all of this in the following sections.

8.4.1. Opening Recordsets

Opening a recordset is easy, using either DAO or ADO (for more details about DAO and ADO, refer to Chapters 6 and 7). To open a recordset, you first need a reference to the current database, usually named db, and a recordset object. Here's how to accomplish that using DAO:

Dim db as Database
Set db = CurrentDB
Dim rec as DAO.Recordset

Now, you need to actually open the recordset. There are three basic ways to open a recordset: by table, by query, and by SQL statement. Here's the way to use a table directly:

Set rec = db.OpenRecordset("tblMyTableName")

If you have a query that already has some joined tables, selection criteria, or sort order, you can use it to open the recordset instead of using a table.

Set rec = db.OpenRecordset("qryMyQueryName")

Finally, you can open a recordset using your own SQL statement instead of using a preexisting query. Access evaluates and runs the query string on the fly.

Set rec = db.OpenRecordset("Select * From tblMyTableName")

Now, you're probably thinking, "why is that last way any better than opening the table directly?" Your question is justified in this simple example. But using a recordset based on a SQL statement is much more flexible than using a table or query directly because you can ...