Recordset

[Lou_Reed]

I know that I should have learned this long ago but it is never too late.



What is exactly a recordset and when and why is it used. I know it is set of records literally translated, but so is a table
so why not use that instead? It seems a abstract concept, but why use it and where to use it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

A recordset is just that - it is a set of records that are retrieved from "somewhere" (see below) into a recordset object, which can be manipulated as required.

The source of the records in a recordset can be a table or a query - the query can be a saved query, or it can be expressed as an SQL Select statement. When the recordset is defined (opened), the selected records can be filtered or ordered as needed. A recordset can be (and often is) empty, i.e. contains no records, but that is perfectly normal, and is easily worked with.

Where are recordsets used? Actually, you work with them all the time - you just dont realize it. When you open a form that is to display/edit data, the form creates (behind the scenes) a recordset that contains the data the form is to use. Users determine what is in the form's recordset by setting the Record Source property of the form, and by applying filters and sort orders to it.

When you open a Select query, you are seeing the contents of a recordset.

From a developer's perspective though, recordsets are used extensively in VBA programming. If you have a groups of records that you want to manipulate in some way (add, edit, delete, etc), and you are going to do that in VBA, you begin by getting retrieving all the data (records) you need into a recordset. Once you have that, you can step through the records one by one, move up or down, search, update, etc., etc.

... it is set of records literally translated, but so is a table so why not use that instead?

The easiest answer is that table records cannot be manipulated directly in VBA - they have to be put in a recordset first. The same is true for select queries. (Note: table records can be manipulated using SQL, but SQL isn't the same as VBA).

I know that is a lot to absorb, but if you are going to be using VBA frequently, you do have to know what recordsets are, and how you can use them. There are all kinds of websites and tutorials on how to create and use recordsets, so just enter "Access Recordset Tutorial" in to a web search, and you'll find a lot to get you started.

Go for it!