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 ...

Get Access 2003 VBA Programmer's Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.