What is a record set in VBA? ... What purpose does this serve?

Question

What is a Recordset in VBA?

What purpose does he fulfill?

How do you use them?

Answer 1

This is a pretty big question. In short, a recordset is a selection of records from a table or query. Depending on the query used, it can be used to add, edit, delete and manage records. A set of records can be obtained using ADO or DAO and can have various methods and properties, respectively. Bonding to a DAO that is native to Access:

 Dim rs As DAO.Recordset Set rs=CurrentDB.OpenRecordset("Select ID, Company From Companies") rs.Edit rs!Company="ABC" rs.Update rs.AddNew rs!Company="ABC" rs.Update Do While Not rs.EOF If rs!Company="ABC" Then ''Do something End If rs.MoveNext Loop Set rs=Forms!SomeForm.RecordsetClone rs.FindFirst "Company='ABC'" If Not rs.NoMatch Then Forms!SomeForm.Bookmark=rs.Bookmark End If 

Answer 2

A record set is basically a container for storing data. You use it to manage data or to transfer data.

When you read data from a database in VBA, the result will be in the recordset (excluding scalar data).

Answer 3

Gabriel gave an excellent description of the record set.

Databases are designed to use set theory to interact with data, but a record set is a way to also work with data procedurally. This code is more like programming. The MoveNext method is an example where you can go through data and work with one record at a time.

See Remou code for implementing DAO sets.