LOOKING AT THE BUILT-IN LOCKING MODES

Access provides three alternative locking modes that you can use on forms, reports, and queries: No Locks, All Records, and Edited Record. Table 22.2 shows the objects and which modes work with them. You can set these choices globally by accessing the Options dialog (from the Tools menu, choose Options) and clicking the Advanced tab. For more information about this option, see the earlier section “Default Record Locking.”

Using Record Versus Page-Level Locking

Before Access 2000 (Jet 4.0), Jet didn't use row-level locking. Instead, it used what's referred to as page-level locking. This means that depending on the locking scheme used, when a record is edited, a 4KB page surrounding the record is locked as well. Figure 22.10 shows what a 4KB page would look like.

As you can see in Figure 22.10, some issues come along with page locking. For example, when a record falls on more than one page, both pages are locked. Issues are more pronounced when using some locking schemes rather than others.

All this discussion of page locking deals with Access native tables only. Dealing with other products for the back end depends on the product. For other ISAM products, such as Microsoft FoxPro and Paradox, their locking methods will be used. SQL Server Access lets Access perform all the locking necessary.

In using row-level locking, not only is it more convenient, especially using Edited Record, but you get an increase in performance as well.

Locking All Records

When using the All Records locking mode, you are in essence locking all the records in a recordset. This means that other users who try to access the same records can only read those records, but not update them. Using All Records means that other users are also excluded from adding or deleting records.

In some cases, you want to use this mode. For example, if you're creating a long report, you want to make sure that the data doesn't change while you're working on it, so you set the report's Record Locks property to All Records.

Locking Edited Records

Edited Record mode uses pessimistic locking, which locks the row or page as soon as you start editing a record. There are good points and bad points to this mode.

One good point is that you can be sure that only one user at a time is editing a record. As soon as a user presses a key, that page is locked, and any other users who happen to be on that page receive an international “not” symbol on their record selector. Figure 22.11 shows a locked page.

You can tell in Figure 22.11 that the Employees form (on the right) is being edited because of the pencil symbol on the record selector along the left side of the form. You know the table can't be edited because of the “not” symbol on the left side of the Employees table (on the left).

Now, the bad news for page-level locking: Notice that even though the employee named Steve Krash is the record next to the one that's being edited in the Employees form, it has the “not” symbol in it. This is because Access uses page locking rather than record locking. This can confuse users, and more than once you'll get a call saying that Access is locking records other than the one being edited.

Using the No Locks Locking Mode

The third locking mode used by Access is No Locks. This mode is referred to as optimistic locking because the lock is in place only at the time the update is performed, and it causes an error only if the same record is edited by two different people at the same time. This kind of error—a write conflict—is one of the biggest disadvantages to using optimistic locking.

A write conflict can occur as follows:

The choices on the Write Conflict dialog are as follows:

• Save Record saves the record over whatever changes were made by the other user.

• Copy To Clipboard allows the user to copy the information for the record to the Clipboard and then to append it to the end of the recordset. This choice is highly confusing and not one that users should be encouraged to use.

• Drop Changes allows users to simply forgo the changes altogether.

Although the write-conflict error is confusing, the No Locks mode results in the best performance because a write conflict occurs only when two users are working on the same record.

Most developers work with the three built-in Access locking modes when dealing with multiuser programming. That Access no longer has a problem adding records and locking people out helps quite a bit as well. But for some, it's just not quite good enough. Some developers choose to use their own ways to handle record locking, discussed shortly in the section “Using Alternative Locking Schemes.”

Using the Locking Modes in VBA

In VBA, the default mode of record locking is pessimistic. To set it to optimistic by using DAO, you must set the LockEdits property of the recordset to false. This sample code does just this:

Set dynSample = dbLocal.OpenRecordSet("Customers", dbOpenDynaset)
dynSample.LockEdits = False

For ADO, use the recordset's LockType property, which has four settings: adLockReadOnly, adLockPessimistic, adLockOptimistic, and acLockBatchOptimistic. The default is adLockReadOnly. Here's some code to show using this property:

rstCust.Open "Customers", cnnCurr
rstCust.LockType = adLockOptimistic

Using Alternative Locking Schemes

A couple of different schemes for working with Access locking mechanisms are rolling your own record locking and using unbound forms to control locking through code.

Using the “Roll-Your-Own” Scheme

The idea behind rolling your own record locking is to create a record-level locking scheme for your application. This can be done with tables to store the record that's being edited at any given time. Then some code has to be created to maintain the locks and keep everything updated.

This system isn't recommended and is unnecessary with Access 2000. One reason is because it can take quite a bit of code to maintain all the necessary pieces.

This system is also not recommended because other applications might get into the system through VBA and Automation or ODBC. These other systems won't necessarily be up to the task of keeping the record locks in place. For example, if a program is using Visual Basic to look at the data, it has to know exactly what you did—and have the routines—to perform the same type of locking scheme.

Using the Unbound Forms Scheme

The unbound forms scheme is popular. You have to do some work up front for one form, and then—if done properly—the other forms you create can use the same functions created for the first one. This scheme is good because you can have very fast forms that maintain their speed, even when used on a wide area network with systems all over the country.

The major benefits of using this scheme are that the locks are all performed within code at the time the updates are performed, and can be controlled with error handling. The tough part of using this scheme is that a bit of code is involved in getting the routines set up to handle all the work that's performed automatically when you use bound forms.

The following steps overview what needs to take place when you're creating unbound forms (the actual details are presented in the next section):

This last step is by far the most complicated. It also turns most developers off before even starting. The cool part is that the last step—the writing of the VBA code—has been done for you generically, and can then be used on other forms with very little coding of your own. All you need to do is remember which pieces go where and what's already created. The following sections discuss this as well as other coding issues.