Recordsets seem to be a cornerstone of Access VBA. The books have pages and pages of examples without ever clearly telling a novice what it is used for. Could someone please explain what they are used for, then going through the books may make more sense?
How are recordsets used?
- Thread starter PierreR
- Start date
- Local time
- Today, 14:43
- Joined
- Feb 28, 2001
- Messages
- 30,561
{put on "old perfessor" hat}
A recordset is a data stream. One old interpretation of SQL is "Sequential Query Language" - there are other interpretations of the "S" but that is the one I grew up with. The idea of SQL is that whatever the query says to do, you do it sequentially to every data record in the table, OR every record in the table that matches some selection criterion.
Using this definition, a query is an action to be performed on some records. The action for a SELECT query is to display members of the set of selected records. A recordset therefore is merely the set of all records that match your selection criteria, where you may have 0 to many criteria. Obviously, if you have zero criteria, you are getting every record from the sources in the FROM clause of the SQL equivalent. The more criteria you name, the fewer records you get. If you over-specify criteria, you can validly get no records in a recordset. As comedienne Judy Tenuta often says, "It could happen." {accordian flourish...}
A table is inherently just a source of a recordset. So is a SELECT query. When you realize that technically, Access is running a SELECT query any time you open a table, query, form, or report, you realize that the data driving your datasheet view of the table or query, or the records that appear on your form or report, all come from a recordset. Except when you see a form that is unbound. Which means the form does not have a recordset. Switchboard forms (they are in your Help files) are like this.
When a control on a form allows you to select an entry that happens to exist in a table, a recordset is feeding the control (because there is an implied SELECT query underneath that control.) When a report contains detail records, they come from the underlying recordset. (The group headers and footers are computed or extracted from the recordset, too!)
A recordset, like anything else in Access, has properties. Some examples are the fields, sort order, and record count. There is also a property that defines the SQL equivalent of how you would obtain that recordset if you had to use an external SQL interface like ORACLE's SQLplus program. Not to mention several other properties that depend on exactly what kind of recordset we are talking about.
Technically, when you view a table in datasheet view, you are actually looking at a "canned" dataview form for which the table itself is the recordset and the implied select query is merely a "SELECT * FROM table;" query - with no WHERE clause unless you turn on filtering, and with no ORDER BY clause unless you turn on sorting. You use the same exact canned dataview form when you call up a query.
Now, you might see that this means there is no practical distinction between a recordset generated directly from a table and one generated by another method. That is almost true, except that when opening a "real" query it is possible to make the recordset update-locked by making it an aggregate query of one form or another. Otherwise, opening a table is the same as opening an ordinary SELECT query. Both supply recordsets.
Now, let's go external. When you link to an external file via Access links (say to a spreadsheet or an ODBC-compatible database), all you are doing is changing the location at which Access looks for the data stream. But it is still a data stream. And opening it produces a recordset.
{removing "old perfessor" hat}
Hope that is adequate to help you understand what Access does with a recordset. Which is to say, except for a very few unbound objects, Access does nothing without a recordset to drive it.
A recordset is a data stream. One old interpretation of SQL is "Sequential Query Language" - there are other interpretations of the "S" but that is the one I grew up with. The idea of SQL is that whatever the query says to do, you do it sequentially to every data record in the table, OR every record in the table that matches some selection criterion.
Using this definition, a query is an action to be performed on some records. The action for a SELECT query is to display members of the set of selected records. A recordset therefore is merely the set of all records that match your selection criteria, where you may have 0 to many criteria. Obviously, if you have zero criteria, you are getting every record from the sources in the FROM clause of the SQL equivalent. The more criteria you name, the fewer records you get. If you over-specify criteria, you can validly get no records in a recordset. As comedienne Judy Tenuta often says, "It could happen." {accordian flourish...}
A table is inherently just a source of a recordset. So is a SELECT query. When you realize that technically, Access is running a SELECT query any time you open a table, query, form, or report, you realize that the data driving your datasheet view of the table or query, or the records that appear on your form or report, all come from a recordset. Except when you see a form that is unbound. Which means the form does not have a recordset. Switchboard forms (they are in your Help files) are like this.
When a control on a form allows you to select an entry that happens to exist in a table, a recordset is feeding the control (because there is an implied SELECT query underneath that control.) When a report contains detail records, they come from the underlying recordset. (The group headers and footers are computed or extracted from the recordset, too!)
A recordset, like anything else in Access, has properties. Some examples are the fields, sort order, and record count. There is also a property that defines the SQL equivalent of how you would obtain that recordset if you had to use an external SQL interface like ORACLE's SQLplus program. Not to mention several other properties that depend on exactly what kind of recordset we are talking about.
Technically, when you view a table in datasheet view, you are actually looking at a "canned" dataview form for which the table itself is the recordset and the implied select query is merely a "SELECT * FROM table;" query - with no WHERE clause unless you turn on filtering, and with no ORDER BY clause unless you turn on sorting. You use the same exact canned dataview form when you call up a query.
Now, you might see that this means there is no practical distinction between a recordset generated directly from a table and one generated by another method. That is almost true, except that when opening a "real" query it is possible to make the recordset update-locked by making it an aggregate query of one form or another. Otherwise, opening a table is the same as opening an ordinary SELECT query. Both supply recordsets.
Now, let's go external. When you link to an external file via Access links (say to a spreadsheet or an ODBC-compatible database), all you are doing is changing the location at which Access looks for the data stream. But it is still a data stream. And opening it produces a recordset.
{removing "old perfessor" hat}
Hope that is adequate to help you understand what Access does with a recordset. Which is to say, except for a very few unbound objects, Access does nothing without a recordset to drive it.
monkeytunes
Serf of the Jungle
- Local time
- Today, 12:43
- Joined
- Jun 8, 2004
- Messages
- 120
Doc Man, thanks for this explanation! I always had a notion of recordsets that I couldn't ever quite put into words, but this was terrific. Thanks!
Thank you very much, Docman, that is great, but my real problem is:
I have difficulty conceptualizing the uses of recordsets in VBA programming, as opposed to VBA-programming not declaring recordsets.
What is the practical value of programming on recordsets?
What do you use recordset VBA for?
When is it preferred?
How is it used?
What are the benefits?
Etc.
I think a similar perfesser essay on why and when we should use recordsets in VBA, would benefit all the new guys.
Thanks.
Pierre.
I have difficulty conceptualizing the uses of recordsets in VBA programming, as opposed to VBA-programming not declaring recordsets.
What is the practical value of programming on recordsets?
What do you use recordset VBA for?
When is it preferred?
How is it used?
What are the benefits?
Etc.
I think a similar perfesser essay on why and when we should use recordsets in VBA, would benefit all the new guys.
Thanks.
Pierre.
Similar threads
