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, 07:53
- Joined
- Feb 28, 2001
- Messages
- 30,034
{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, 05:53
- 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.
- Local time
- Today, 08:53
- Joined
- Feb 19, 2002
- Messages
- 46,908
You use DAO or ADO to process a recordset in VBA when you need to do something that you cannot do (or cannot do efficiently) with a query.
An example might be to normalize a table with a large repeating group. Someone (not you of course) created a table that has 120 yes/no fields, most of which are not normally populated. To normalize it, you would need to create/run 120 append queries to create the many-side table. If the thought of that gives you a headache, you might decide to write a VBA procedure to loop through the fields collection of the spreadsheet like table and create tha appropriate rows in the new many-side table.
Someone sent you a flat file that you need to load into 2 tables. The file has record types and therefore all records in the recordset are not identical in format. Therefore, you can't use transferText or any other standard method to import the table. You can write DAO or ADO to read the input file, decide which type each record is and add rows to the parent and child tables as appropriate.
Try very hard to do what you need to do with a query. Queries are far more efficient than VBA loops that process recordsets. If you can't solve your problem with a query, then process a recordset in VBA. If you process a recordset with VBA, base the recordset on a query that selects the minimum number of rows and columns so the loop processes the minimum number of records. You should not need to use find or seek to position yourself except in extremely rare cases where you need to actually re-process parts of a recordset.
An example might be to normalize a table with a large repeating group. Someone (not you of course) created a table that has 120 yes/no fields, most of which are not normally populated. To normalize it, you would need to create/run 120 append queries to create the many-side table. If the thought of that gives you a headache, you might decide to write a VBA procedure to loop through the fields collection of the spreadsheet like table and create tha appropriate rows in the new many-side table.
Someone sent you a flat file that you need to load into 2 tables. The file has record types and therefore all records in the recordset are not identical in format. Therefore, you can't use transferText or any other standard method to import the table. You can write DAO or ADO to read the input file, decide which type each record is and add rows to the parent and child tables as appropriate.
Try very hard to do what you need to do with a query. Queries are far more efficient than VBA loops that process recordsets. If you can't solve your problem with a query, then process a recordset in VBA. If you process a recordset with VBA, base the recordset on a query that selects the minimum number of rows and columns so the loop processes the minimum number of records. You should not need to use find or seek to position yourself except in extremely rare cases where you need to actually re-process parts of a recordset.
Last edited:
Similar threads
