Locking Shared Data with Bound Forms

The easiest way to create a multiuser application is to create forms that are bound to tables or queries. You control how Microsoft Access locks data by setting multiuser options and form properties.

Selecting a Locking Strategy

When you share data with bound forms, you can specify how Microsoft Access locks records in a form. There are three locking strategies to choose from:

• No Locks Microsoft Access doesn’t lock the record you’re editing. This strategy ensures that records can always be edited, but it can create editing conflicts between users.
• All Records Microsoft Access locks all records in the underlying table or query of the form that contains the record you’re editing for the entire time you have the form open. This strategy is very restrictive, so choose it only when you know you’re the only person who needs to edit records at any one time.
• Edited Record Microsoft Access locks the page that contains the record you’re editing. This strategy ensures that you can always finish making changes that you start.

To set a locking strategy for all forms in the database, select one of these options on the Advanced tab of the Options dialog box (Tools menu). If you want to set the locking strategy for an individual form, set the form’s RecordLocks property to one of these settings. You can specify a locking strategy for one, many, or all forms in a database by setting the RecordLocks property in code.

See Also   For more information on the RecordLocks property, search the Help index for “RecordLocks property.”

Setting Locking Options

In addition to specifying a locking strategy, you can set other options that control how Microsoft Access handles locking in your multiuser application. The following options help you avoid locking conflicts with other users:

• Number Of Update Retries Sets the number of times Microsoft Access tries to save a changed record that is locked before displaying an error message.
• Update Retry Interval Sets the number of milliseconds after which Microsoft Access automatically tries to save a changed record that is locked by another user.
• Refresh Interval Sets the number of seconds after which Microsoft Access automatically updates records.
• ODBC Refresh Interval Sets the number of seconds after which Microsoft Access automatically refreshes records that you’re accessing by using Open Database Connectivity (ODBC).

You set these options on the Advanced tab of the Options dialog box (Tools menu). Alternatively, you can set these options in code by using the SetOption method of the Application object. For example, the following lines of code set the Number Of Update Retries and the Update Retry Interval options:

Application.SetOption "Number of Update Retries", 5
Application.SetOption "Update Retry Interval (msec)", 500

When you share data with bound forms, Microsoft Access displays standard messages when locking conflicts occur.