A Recordset object can act as temporary table that stores the(temporary)data in the memory of the computer after you retrieved the data from the table or query. To open Recordset object, you need to use the following syntax:
Set Rs= Db.OpenRecordset(Name As string,[Type],[Option],[LockEdits] As Recordset)
The explanation of syntax:
Set: The keyword is used to set the variable to represent an open Recordset object.
Rs: A variable represents the Recordset object that you want to open.
Db: The variable represents the Database object that you opened.
Name: The name of the table or query( data type as string).
Type: (an optional argument)The types of Recordset object. There are five types of Recordset object:
- dbOpenTable: This Recordset object type is for the table only. If you use this type for the Name argument is an SQL statement or query's name, an error will occur.
- dbDynaset: This type of the Recordset object allows the Record to be moved forward or backward or updated. And for updating the record from other users, the changes can be seen by other users. But appending and deleting the record by a user are kept secret from other users.
- dbSnapShot: In this type, you cannot see the changing of record when other users update the record.
- dbForward-Only: Open Recordset object in a form that you cannot modify the record(read only).
- dbDynamic: You will see the data changed when other user append, update, and delete the record.
Options: It is an optional argument of the open Recorset object syntax. Its values can dbConsistent, dbInconsistent, and dbReadOnly.
- dbConsistent: This value indicates consistent updates.
- dbInconsistent: This value indicates inconsistent updates.
- dbReadOnly: If you supply dbReadOnly value, the recordset can be read only and this value can't be used at the same time with dbReadOnly of LockEdits.
LockEdits: (an optional argument)It is used to define the permission of modifying the record when there are more than one user using a single table. LockEdits consists of three values:
- dbReadOnly: Open Recordset in a form of read only.
- dbPessimistic: In this case, if there are two users(user A and user B) want to update a single record at the same time, the updating record cannot occur. User A can update that record till user B finishes the updating and vice versa.
- dbOptimistic: If there are two user(user A and user B) intend to update a single record in a single time, the two users can perform this task. User A can update the record first if user A use the update method first.
For example, you want to open a recordset object in a type of dbOpenDynaset of the current database. To do so, use the following code:
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Private Sub Form_Load()
Set Db = CurrentDb ' Open current database Object
' Open Recordset Object
Set Rs = Db.OpenRecordset("TblCustomer", dbOpenDynaset)
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: