Sharing Forms

Even without any code, Access forms allow multiple users to edit, add, and delete records in a database. However, you can enhance this capability in a multi-user environment using modules behind forms.

A simple AutoForm based on a table or other recordset source can work in a multi-user environment. Two or more users can open such a form and then page through records. They can also modify the form's bound recordset in all the ways that Access permits. You can program Access to change the defaults for how and when one user can view the changes made by another. The database file for this chapter includes five forms (WebBasedList, WebBasedList1, WebBasedList2, WebBasedList3, and WebBasedList4) that reflect varying types of programmatic control.

Locking Records Manually

You'll find form-sharing controls on the Advanced tab of the Tools-Options dialog box. The Edited Record option button imposes pessimistic page-level locking. With pessimistic page-level locking, Access locks the page (or pages) containing a record as soon as a form opens the record for editing. Any other records on the same page(s) also lock. The Open Databases With Record-Level Locking check box applies optimistic locking to just the current record. Other records on the same page remain unlocked. This new record-level locking feature was requested by many developers to reduce the chance of concurrency conflicts in a multi-user environment. Microsoft added this feature to counteract the impact of expanding its page size from 2 KB to 4 KB (which was necessary to accommodate Unicode format for character-based, as opposed to numeric, fields).

Refreshing Field Values

The default refresh interval on the Advanced tab specifies the maximum number of seconds before an updated field value on one form appears to other users. Users can manually refresh a field by choosing the Refresh command from the Records menu or by simply moving the cursor over the field. If the cursor is inactive on a form, a relatively long time might elapse before a change appears to another user who is looking at the same record.

The WebBasedList form shows the behavior of the current lock and refresh settings for an Access form in a multi-user environment. If you open two different copies of the form and change the same record from two different computers (or two different Access sessions on the same computer), you can see the locking behavior and update refresh intervals.

The WebBasedList1 form improves on the no-module WebBasedList form using two short event procedures, shown below. The Form_Open event procedure sets the refresh interval option setting to 2 seconds. The Form_Close event procedure restores the default interval to 60 seconds. Note that the procedures use the SetOption method for the Access Application object.

Private Sub Form_Open(Cancel As Integer)     Application.SetOption "Refresh Interval (Sec)", 2 End Sub Private Sub Form_Close()    Application.SetOption "Refresh Interval (Sec)", 60 End Sub 

When two sessions open the WebBasedList1 form, simple data updates appear to propagate from one session to the next much more quickly than with the WebBasedList form. The shorter refresh interval does not reflect any reordering, adding, or deleting of records between sessions, but it reflects updates very quickly with a light resource consumption requirement.

The simple update that the Form_Open procedure achieves can dramatically affect the apparent responsiveness of a multi-user Access application. In fact, this setting has no impact in single-user operation. Its sole purpose is to influence when changes by other users are reflected on forms (and on datasheets).

Custom refresh and requery controls

The WebBasedList2 form includes a Refresh button that invokes the Refresh method for the form. The code behind the button's click event is simply Me.Refresh. The Refresh method functions just like the timeout for a refresh interval. Again, users do not see revised sorts, added records, or deleted records, but they instantly see changes to records made by other users. While users can achieve similar results by moving the cursor over a field, the button is named Refresh so users immediately know what it does.

The Refresh method is fast, but it does not update a form to reflect a new record, and it shows a deleted record with a deleted marker. You invoke a form's Requery method to reflect the look of a recordset after one or more other users adds and deletes records. In the default mode, the Refresh method leaves the current record active, but the Requery method selects the first record after its operation. If you want to return to your previous position (which will likely be different from the first record), you must add logic to navigate back to it. If any users alter sort criteria, such as primary key or sort key values, the Requery method reflects these changes but the Refresh method does not update the record order. The Requery method works in both single-user and multi-user environments. (The Refresh method has no effect in single-user applications.)

The following two procedures add a Requery button to the Refresh button in the WebBasedList2 form. The cmdRequery_Click procedure has three parts. First, it saves the current primary key value to help relocate the record after the Requery method repositions the recordset point to the form's first record. Second, it applies the Requery method. Third, it sets the focus to the primary key field (ContactID) and invokes the DoCmd FindRecord command. This repositions the form from the first record to the record that was current before the application of the Requery method. If another user deleted that record, the form repositions the display to the first record. You can use more elaborate logic to position the current record elsewhere.

Private Sub cmdRefresh_Click()     Me.Refresh End Sub Private Sub cmdRequery_Click() Dim pkBefore As Long 'Save primary key value before requery.     pkBefore = Me.ContactID.Value      'Apply requery method.     Me.Requery 'Attempt to find primary key value before requery.     Me.ContactID.SetFocus     DoCmd.FindRecord pkBefore End Sub 

Locking Records Programmatically

Record locking means locking record values so other users cannot edit them. Access 2000 offers record locking for the first time, instead of only page locking. You control when to apply locks using the locking options on the Advanced tab of the Options dialog box. You can also programmatically assign values to the RecordLocks property for a form to determine whether it locks records optimistically or pessimistically at the page level or table level. The RecordLocks property can assume one of three values. The default value of 0 specifies optimistic page locking. A value of 2 specifies pessimistic page locking. A value of 1 exclusively locks the whole table.

The Advanced tab also offers a new check box for record-level locking instead of page-level locking. It is selected by default. This setting lets two users simultaneously update two different records on the same page, using forms. Row-level locking is not available for memo data types or on index pages. Access does not generally support naming users with conflict locks. You can programmatically set row-level locking with the SetOption method for the Access Application object. The GetOption method offers a mechanism for returning row-vs.-page lock settings.

The Form_Open and Form_Close procedures below manipulate different styles of page locking and record-vs.-page locking. The Form_Open event offers form users the option of locking pages optimistically or pessimistically. If a user chooses either option, the procedure assigns a value of False to the row-level locking setting. If a user does not choose either page-locking option, the procedure assigns a value of True to the row-level locking setting. These True/False values correspond to a selected or deselected check box on the Advanced tab for record-level locking.

Private Sub Form_Open(Cancel As Integer)      'Present a series of message boxes to let a user set the 'page-locking style and record-level locking for a form.         If MsgBox("Optimistic page locking?", vbYesNo, _         "Programming MS Access") = vbYes Then         Me.RecordLocks = 0         Application.SetOption "Use row-level locking", False     ElseIf MsgBox("Pessimistic Page locking?", vbYesNo, _         "Programming MS Access") = vbYes Then         Me.RecordLocks = 2         Application.SetOption "Use Row Level Locking", False     Else         MsgBox "Setting new row-level locking.", _             vbInformation, "Programming MS Access"         Application.SetOption "Use row-level locking", True     End If 'Handy for confirming selected states     Debug.Print Me.RecordLocks     Debug.Print Application.GetOption("Use row-level locking") End Sub Private Sub Form_Close() 'Restore default locking.     Me.RecordLocks = 2     Application.SetOption "Use row-level locking", True      End Sub 

NOTE

---

The first user to set locking options in a multi-user session controls locking for all other users in that session. When testing procedures assign values to different locking options, you should exit and restart Access between tests to initialize new locking options.

Not all of the RecordLocks settings have corresponding options on the Advanced tab. The two print statements at the bottom of the Form_Open event procedure reflect the current settings for the style and type of page locking as well whether your system is using row-level instead of page-level locking.

Access 2000 has an optional feature for changing multiple page-level locks to a single lock for the whole table. As the number of locks per table increases, Access can automatically replace the individual record locks with a single lock per table. This automatic lock promotion can dramatically lower the cost of lock management while offering the flexibility of record locks when locks are sparse. You can select this feature by turning off default row-level locking and using the SetOption method for the Application object. You must also assign a value to the new PagesLockedToTableLock registry entry. A default value of 0 disables automatic lock promotion. Values greater than 0 indicate the minimum number of locks per table before Jet attempts to replace individual record locks with a single table lock. A value of 100 means that Jet will attempt to replace page locks with a single table lock on the application of the 101st lock per table, 201st lock per table, and so on. The attempts succeed only if Jet can get exclusive access to the table (because placing a lock on a table locks out all users except the one with the lock). Jet performs data manipulation much more rapidly when it has exclusive access to the table. This feature also removes the need for the developer to determine in advance when to apply exclusive table locks.