You can use the RecordLocks property to determine how records are locked and what happens when two users try to edit the same record at the same time. Read/write.
expression Required. An expression that returns one of the objects in the Applies To list.
When you edit a record, Microsoft Office Access 2007 can automatically lock that record to prevent other users from changing it before you are finished.
Note The RecordLocks property only applies to forms, reports, or queries in a Microsoft Access database (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) (.mdb or .accdb).
The RecordLocks property uses the following settings.
(Default) In forms, two or more users can edit the same record simultaneously. This is also called "optimistic" locking. If two users attempt to save changes to the same record, Access displays a message to the user who tries to save the record second. This user can then discard the record, copy the record to the Clipboard, or replace the changes made by the other user. This setting is typically used on read-only forms or in single-user databases. It is also used in multiuser databases to permit more than one user to be able to make changes to the same record at the same time.
In reports, records aren't locked while the report is previewed or printed.
In queries, records aren't locked while the query is run.
||All records in the underlying table or query are locked while the form is open in Form view (Form view: A view that displays a form to show or accept data. Form view is the primary means of adding and modifying data in tables. You can also change the design of a form in this view.) or Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.), while the report is previewed or printed, or while the query is run. Although users can read the records, no one can edit, add, or delete any records until the form is closed, the report has finished printing, or the query has finished running.
||(Forms and queries only) A page (page (data storage): A portion of the database (.mdb) file in which record data is stored. Depending on the size of the records, a page (4 KB in size) may contain more than one record.) of records is locked as soon as any user starts editing any field in the record and stays locked until the user moves to another record. Consequently, a record can be edited by only one user at a time. This is also called "pessimistic" locking.
Note You can set this property by using a form's property sheet (property sheet: A pane that is used to view or modify the properties of various objects such as tables, queries, fields, forms, reports, data access pages, and controls.), a macro (macro: An action or set of actions that you can use to automate tasks.), or Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) code.
Note Changing the RecordLocks property of an open form or report causes an automatic recreation of the recordset.
You can use the No Locks setting for forms if only one person uses the underlying tables or queries or makes all the changes to the data.
In a multiuser database, you can use the No Locks setting if you want to use optimistic locking and warn users attempting to edit the same record on a form. You can use the Edited Record setting if you want to prevent two or more users editing data at the same time.
You can use the All Records setting when you need to ensure that no changes are made to data after you start to preview or print a report or run an append (append query: An action query that adds the records in a query's result set to the end of an existing table.), delete (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), make-table (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in that table by copying records from an existing table or query result.), or update (update query: An action query (SQL statement) that changes a set of records according to criteria (search conditions) that you specify.) query.
In Form view or Datasheet view, each locked record has a locked indicator in its record selector.
To change the default RecordLocks
property setting for forms, click the Microsoft Office Button
, and then click Access Options
. In the Access Options
dialog box, click Advanced
, and then select the option you want under Default record locking
Data in a form, report, or query from an Open Database Connectivity (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.
The following example sets the RecordLocks property of the "Employees" form to Edited Record (a page of records is locked as soon as any user starts editing any field in the record and stays locked until the user moves to another record).
Forms("Employees"). = 2