Ms Access OpenRecordSet In VBA Syntax

This tutorial will explain about Ms Access OpenRecordSet. Recordset is the object represents the records in a base table or the records that result from running a query. The OpenRecordSet is the syntax for creating a new Recordset object and appends it to the Recordsets collection. The OpenRecordSet function is used in VBA Code.

The syntax for OpenRecordSet function
Database.OpenRecordset (Name, Type, Options, LockEdit)

Name: Table name, Query name, or SQL statement that is the source of the records for the new Recordset. This is the only required parameter, the others are optional.
Type: The constant that indicates the type of Recordset to open. You can see the type list in the table:

Name Description
dbOpenDynamic Opens a dynaset-type Recordset
dbOpenDynaset Opens a dynaset-type Recordset
dbOpenForwardOnly Opens a forward-only type Recordset
dbOpenSnapshot Opens a snapshot-type Recordset
dbOpenTable Opens a table-type Recordset


If it omitted, the default value will be dbOpenTable (for single table) or dbOpenDynaset (for query or linked tables).
Options: the constants that specify characteristics of the new Recordset.

Name Description
dbAppendOnly Allows user to add new records to the dynaset, but prevents user from reading existing records.
dbConsistent Applies updates only to those fields that will not affect other records in the dynaset (dynaset- and snapshot-type only).
dbDenyRead Prevents other users from reading Recordset records (table-type only).
dbDenyWrite Prevents other users from changing Recordset records.
dbExecDirect Executes the query without first calling the SQLPrepare ODBC function.
dbFailOnError Rolls back updates if an error occurs.
dbForwardOnly Creates a forward-only scrolling snapshot-type Recordset (snapshot-type only).
dbInconsistent Applies updates to all dynaset fields, even if other records are affected (dynaset- and snapshot-type only).
dbReadOnly Opens the Recordset as read-only.
dbRunAsync Executes the query asynchronously.
dbSeeChanges Generates a run-time error if another user is changing data you are editing (dynaset-type only).
dbSQLPassThrough Sends an SQL statement to an ODBC database (snapshot-type only).

LockEdit: the constant that determines the locking for the Recordset.


Name Description
dbOptimistic Compares record ID in old and new records to determine the (occurred) changes since the record was last accessed.
dbOptimisticBatch Enables batch optimistic updates.
dbOptimisticValue Compares data values in old and new records to determine the (occurred) changes since the record was last accessed.
dbPessimistic Uses the lowest level of locking sufficient to ensure that the record can be updated.

NOTE: the syntax is for DAO object, not to be confused with ADO.


Ms Access Openrecordset-1

We create a button where upon clicked, it will add the new record with value above:

Ms Access Openrecordset-2

Source Tags: , , , , , .

Ms Access OpenRecordSet In VBA Syntax | access2016 | 4.5