Access 2003 VBA Programmer's Reference

8.5. Using Recordsets

Recordset operations are one of the cornerstones of Access VBA. They are a direct way to read, update, add, and delete records in Access tables and queries. We cover them in the following sections.

8.5.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. This is accomplished this way

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 orders, 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 will evaluate and run 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 a 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 modify the SQL statement ...