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:
|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.
|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.
|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.
We create a button where upon clicked, it will add the new record with value above:
Originally posted 2017-06-08 06:58:14.
Source Tags: access vba openrecordset query, access vba openrecordset query with parameters, openrecordset dbseechanges, openrecordset too few parameters expected 1, recordset vba access, what is a recordset in access.