Form Record Locks

Question

Could someone explain the different types of Form record locks in plain English (the help is of no help, I don't know who writes this stuff but wow, sure doesn't clarify things for me), and perhaps when each should be used.

Particularily, I have a multiuser db on a network, what form or locking should I be implementing?

Thank you,

QuestionBoy

Answer 1

I would agree with Hans that pessimistic locking (Edited Record) will in most circumstances be the preferred option.  Be sure that the use of record level locking is selected in the Advanced page of the Access Options dialogue.  You can also set the default locking strategy in this dialogue.

Optimistic locking (No Locks) used to be the most popular despite its drawbacks as, in early versions of Access, a complete 'page' of data was locked by pessimistic locking, which could be several records, not just the current one.  The current support of record level locking has overcome this objection to pessimistic locking.  The latter does involve a greater use of resources, however, so there can occasionally be a cogent argument in favours of optimistic locking if conflicts are not anticipated, or very rarely so.

The All Records option is only useful when undertaking set updates and is consequently relatively rarely used in day to day operation of a database.  It is applied by default when executing DDL queries to modify the definition of a table etc.

When working with recordsets, the default strategy for a DAO recordset is pessimistic locking and this overrides the Access default strategy.   This can be overridden by setting the option of the OpenRecordset method, or by altering the LockEdits property.

ADO recordsets are by default read-only, but this can be overridden by changing the LockType property of the recordset object, or the LockType argument of the Recordset.Open method.  Note, however, that an ADO recordset opened against the CurrentProject.Connection does not support pessimistic locking, so a new Connection object has to be created to use this type of locking if opening an ADO recordset.

Answer 2

The strictest locking level is All Records. This means that the moment a user starts editing a record in the form, ALL records in the form will be locked for editing by other users. Other users can still view records, but they won't be able to edit any record in the form until the first user saves the edited record, either explicitly or by moving on. There is usually no reason to be this severe.

Next is Edited Record. The moment a user starts editing a record, that specific record will be locked for editing by other users until it has been saved (they can still view the record). It will be possible for other users to edit (most) other records in the form. This is usually the best level. It prevents conflicts while allowing mutiple users to edit different records at the same time.

The loosest level is No Locks. Several users can edit the same record simultaneously. If a record has been edited and saved by user A when user B tries to save it, B gets a dialog with options to overwrite A's changes, discard B's changes or copy the edited record to the clipboard for later (re)use. This option can be very confusing to users, so I'd avoid it.