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