Record Locking

When you open a Recordset object, you can also specify the type of record locking you want to use by setting the lockedits argument of the OpenRecordset method to the appropriate constant. The following table lists the five constants you can use for the lockedits argument of the OpenRecordset method, and describes the ODBC cursor lock type to which they correspond.

Constant ODBC cursor lock type
dbOptimistic Uses optimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked only while the record is being updated by the Update method.
dbPessimistic Uses pessimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked as soon as you use the Edit method.
dbOptimisticValue Uses optimistic concurrency based on record values. The cursor compares data values in old and new records to determine if changes have been made since the record was last accessed.
dbOptimisticBatch Uses batch optimistic updating. For more information, see “Using Batch Optimistic Updating” earlier in this section.
dbReadOnly Default for ODBCDirect workspaces. Prevents users from making changes to the data in the Recordset object.

When values or record versions are compared to test concurrency, the original record as it was prior to using the Edit or AddNew methods is compared with the record as it exists in the temporary buffer prior to being committed by the Update method. If the record data changed in this time frame, a trappable error results. At this point, your temporary record buffer is lost. To carry out the changes, you must refresh the current record by using Move 0, then use the Edit or AddNew method, fill in the new values, and try the Update method again.

Some combinations of cursors and lock types will not work together. For example, with Microsoft SQL Server version 6.0 cursors, if you specify the dbOpenSnapshot constant for the type argument of the OpenRecordset method, you must specify the dbReadOnly constant for the lockedits argument. Static cursors do not support the other types of record locking. Which combinations work together depends on the cursor driver. For specific information about compatible lock types, refer to your cursor driver documentation.

Your cursor driver can handle different combinations of cursor types and lock types in different ways. In some cases, it may return an error if it does not handle a specific combination. In other cases, it may switch to the nearest possible combination that it supports. If an error occurs, DAO places the error information in the Errors collection.