O'Reilly logo

Access 2003 VBA Programmer's Reference by Armen Stein, Graham Seach, Teresa Hennig, Patricia Cardoza

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required