What is a recordset?
In Access, you don't work with tables or queries. Never mind what the titles of each sub-pane on the Access window tells you. You ALWAYS work with recordsets. This is because queries and tables imply the existence of a recordset. It can be an EMPTY recordset. But it still exists.
Internally, Access stores records via a pointer system that is rather amorphous. We don't need to know its exact form, but it exists. A table is described as a collection of records. It is ACTUALLY a collection of pointers to its records. The records can go anywhere that Access wants to put them within the .MDB file. Access DOES NOT SHUFFLE RECORDS when you add or remove records to a table. It just mucks the collection of pointers. The only time Access shuffles records is after a Repair or Compress.
A SELECT query scans the collection, applying your selection criteria to find conformant records and builds a NEW list of pointers to the data involved in the records. This includes JOIN cases that involve more than one set of records because the components come from more than one table.
OK, so what happens next? When you activate something that involves a table or SELECT query, Access just traverses the list, returning the records implied by the pointers in the same order that the pointers appeared - unless you included a SORT specification. Do any records get moved? No. The POINTER get re-ordered.
Here is where set theory becomes the issue. When you build a SELECT query, you are telling Access to find the set of all records that match some criteria you have specified. Set of records.... recordset.... see the similarity? So what we have with queries is a set of records that match some specification. Stringent specifications make the set smaller. Loose specifications make it larger. Omitting specifications make the set match the size of its table of origin.
A table is a set of records, too ... the set of the whole table. There is a bizarre theorem in set theory that shows that the entire set is ALSO a formal subset of the entire set. (I.e. A contains A.) This is the basis of subsequent recursion theorems and such. So there is nothing wrong with saying that a table-type recordset and a query-type recordset are similar.
OK, so what do you do with recordsets and what properties do they have?
Well, recordsets are used to drive things. For instance, forms are used to visit records in the order that the underlying recordset visits them. Reports visit the records in a recordset and format the detail or summary lines as required. Queries visit recordsets in some particular order and produce other recordsets. But why?
The reason is SQL. In older texts, SQL is expanded to Sequential Query Language. SQL does ONE THING AT A TIME to a record, but it does it for every record in the set - sequentially. For an Access SELECT query, information is displayed in a datasheet format. Each row is a record. Each column is a field. For action queries, SQL does what it does (again, one thing at a time) and the nature of the action query governs what is done to fields referenced by the query.
From this we can see that recordsets have an implied order, an implied number of records, and an implied list of fields. They also have an implied SOURCE. I.e. they can be single-table selections or multi-table JOIN selections.
Recordsets are also the subject of an obscure branch of formal logic called "interrogative logic" - in which you ask questions and see if the answer you got was condoned by the question. It is possible to ask a question, get an answer, and assign TRUE or FALSE to that combination of question and answer. So it is up to the maker of the SQL program to assure that the relationship between a RECORDSET and the originating QUERY is ALWAYS TRUE. I.e. the answer is appropriate for the question that was asked. From the standpoint of "what is a recordset" - this "interrogative logic" gives another possible definition: A recordset is the TRUE answer to a QUERY that asks a question (commands an answer) from a database table or group of tables.
Now, a side-effect of JOIN queries: Normally we would say that the whole cannot exceed the sum of its parts. When you build queries correctly based on proper JOIN clauses, this is true. If you ever tried to write a JOIN query without a proper relationship and didn't specify the JOIN clause correctly, you get something called a 'permutation JOIN' for which the resultant recordset IS bigger than the sum of the tables. This is also called a 'Cartesian JOIN' and is often, but not always, a bad thing.
JOIN queries can legitimately produce more records than exist in the tables being joined because a JOIN query can produce all permutations of the records that potentially match the criteria in question. In this case, the recordset size cannot be predicted from the size of the tables contributing to the recordset. BUT in a properly constrained JOIN, the number of records will not exceed the number of records in the larger of the component tables.
Working with recordsets in Access at the VBA level is merely a way for you to write the explicit loop that is IMPLIED in some SQL statements. Now, as to recordset clones... Access just makes a copy of the pointers in the original recordset. You can do what you want to the original recordest once the clone has been generated. Because the pointers in the recordset don't go away until the recordset is closed. BUT you have to remember THIS about recordset clones: There is still only one true set of records - but you can have MORE than one set of pointers to those records.
Hope this helps.
