Coding for Concurrency

Access was designed from the ground up as a multiuser database. That means that two or more users can have the same Access database open at the same time, and nothing bad will happen. This is true from the Access user interface, and it's also true from the code that you write to automate Access.

The key issue in multiuser database use is that of concurrency: what happens when two database users try to edit the same record in the database at the same time? This section briefly reviews the way that concurrency works in Access, and then shows you how to handle these issues in your ADO code.

Understanding Concurrency

There are two basic ways in which an Access application (or any other database application, for that matter) can handle concurrency issues. These are normally referred to as optimistic record locking and pessimistic record locking. Record locking itself is a process of reserving a record for the exclusive use of a single database user. A record lock is always associated with a record in a table and with a particular user. When you have a record locked, no other database user can make changes to that record.

The difference between optimistic record locking and pessimistic record locking lies in precisely when the locks are applied and released. With optimistic record locking, a record is locked only when you save changes. With pessimistic record locking, a record is locked as soon as you begin to edit the data. In either case, the lock is removed when Access finishes saving your changes.

NOTE

In earlier versions of Access, page locking was used to implement record locking. With page locking, locking one record locked the nearby records in the database as well. Starting with Access 2000, Access defaults to record locking instead of page locking. You can change this option if you like (choose Tools, Options and uncheck Open Databases Using Record-Level Locking on the Advanced tab), but it's better to leave it set to the default.

Optimistic record locking allows more than one user to be editing a record at the same time. For example, with optimistic locking, this is one possible sequence of actions:

1. Joe begins editing the customer record for Amalgamated Industries.
   
   

2. Joe changes the customer's phone number.
   
   

3. Mary begins editing the customer record for Amalgamated Industries. She can do this because the record isn't locked when Joe starts to edit it.
   
   

4. Mary changes the customer's phone number.
   
   

5. Joe saves his change. The record is locked to Joe for the time that it takes for the change to be written to the disk. At this point, the customer record has the phone number that Joe entered.
   
   

6. Mary tries to save her change. Mary gets an error message warning her that another user changed the record, so she has to choose between discarding her change or overwriting the other change.
   
   

On the other hand, if the table were using pessimistic record locking, the sequence could go like this:

1. Joe begins editing the customer record for Amalgamated Industries.
   
   

2. Joe changes the customer's phone number.
   
   

3. Mary tries to begin editing the customer record for Amalgamated Industries, but because it is locked to Joe, she can't.
   
   

4. After Joe saves his changes, Mary can edit the record to make her changes.
   
   

With pessimistic record locking, users of the database can block each other from doing their work. In the previous example, Mary can't make changes until Joe is done, even though they don't want to change the same data. Optimistic record locking has the benefit that users don't tend to get in each other's way.

But there's a drawback to optimistic locking as well: it removes the chance that one user might block another user from working with data, but it increases the chance of losing data. If two users edit the same record, the last one to decide to save changes wins.

TIP

In most cases, choose optimistic locking. That's because in any reasonably large database, users are unlikely to be trying to alter the same data at exactly the same time.

Optimistic Locking in ADO

Optimistic locking is the default locking type for ADO Recordset objects. Here's some code that you can use to test optimistic locking behavior:

 Public Sub TestOptLock()   ' Test optimistic locking   Dim rstOpt As ADODB.Recordset   Set rstOpt = New ADODB.Recordset   ' Open a recordset with optimistic locking   rstOpt.Open "Clients", CurrentProject.Connection, _    adOpenKeyset, adLockOptimistic, adCmdTable   ' Pause the code to allow experimentation   Stop   ' Make a change and save it   rstOpt.Fields("State").Value = "Nevada"   ' The lock is only for the duration of this call   rstOpt.Update   rstOpt.Close End Sub 

Notice the Stop statement in the middle of this procedure. That statement tells VBA to stop at that point, just as if you had set a breakpoint in your code. This enables you to see just how optimistic record locking works, even without firing up two copies of the application. Follow these steps to investigate:

1. Open Chapter 19's sample module in TimeTrack.mdb (or TimeTrack19.mdb) and run the TestOptLock procedure from the Immediate window. It stops with the cursor at the Stop statement.
   
   

2. Now return to the Access user interface and open the Clients table's datasheet directly from the database container.
   
   

3. Change the state of the first record in the table to any value you like for example, "Oregon," but don't save the change or leave the row.
   
   

4. Return to the module and press F5 to continue. Access will finish running the code and save the change to the record. At this point, the stored value in the State field is "Nevada." The code can obtain and release the lock it needs, because the user interface practices optimistic locking.
   
   

5. Return to the user interface and attempt to save the record. You'll see the error message shown in Figure 19.1.
   
   

   Figure 19.1. Optimistic locking write conflict message.

   

   
   

When you see this error message, you have three choices:

• Save your changes, thus overwriting the changes made by the other user.

• Copy your changes to the Clipboard. Doing so automatically refreshes your view of the data to show the changes made by the other user. If you don't like the user's changes, you can paste yours back in.

• Discard your changes.

Now that you know what a write conflict looks like from the user interface, you can use the TestOptLock2 procedure to investigate it from the code side:

 Public Sub TestOptLock2()   ' Test optimistic locking   Dim rstOpt As ADODB.Recordset   Set rstOpt = New ADODB.Recordset   ' Open a recordset with optimistic locking   rstOpt.Open "Clients", CurrentProject.Connection, _    adOpenKeyset, adLockOptimistic, adCmdTable   ' Make a change and save it   rstOpt.Fields("State").Value = "Nevada"   ' Pause the code to allow experimentation   Stop   ' The lock is only for the duration of this call   rstOpt.Update   rstOpt.Close End Sub 

The difference between TestOptLock2 and the previous procedure is that now the Stop statement has been moved after the statement that does the edit. You can test the effect of this change by following this procedure:

1. Restore the first record of the Clients table to its original state.
   
   

2. Run the TestOptLock2 procedure from the Immediate window. It stops with the cursor at the Stop statement.
   
   

3. Now return to the Access user interface and open the Clients table's datasheet directly from the database container.
   
   

4. Change the state of the first record in the table to any value you like for example, "Oregon." Leave the row to save the change. The user interface locks the record just long enough to save the change.
   
   

5. Return to the module and press F5 to continue. Access will attempt to finish running the code. But instead of the procedure completing, you'll receive the error message shown in Figure 19.2.
   
   

   Figure 19.2. Optimistic locking write conflict error in code.

   

   
   

Of course, if you add error-handling code to your application, you can trap the error caused by the write conflict. In that case, you can decide what to do about the error in your code, without displaying any errors to the users.

If you inspect the two procedures you've seen so far, you can see how Access detects write conflicts. When you first edit a record, Access saves a copy of the data as it is at that moment. When you try to save the record, Access looks at the data on the hard drive before performing the save. If the data on the hard drive doesn't match the saved copy, someone else must have changed the record, so Access raises a write conflict error.

Pessimistic Locking in ADO

The code to test pessimistic record locking is slightly more complex:

 Public Sub TestPessLock()   ' Test pessimistic locking   Dim cnn As ADODB.Connection   Dim rstOpt As ADODB.Recordset   ' Get a new connection to the current project   Set cnn = New ADODB.Connection   cnn.Open CurrentProject.BaseConnectionString   Set rstOpt = New ADODB.Recordset   ' Open a recordset with pessimistic locking   rstOpt.Open "Clients", cnn, _    adOpenKeyset, adLockPessimistic, adCmdTable   ' Make a change and save it   ' The lock is taken when the next statement is executed   rstOpt.Fields("State").Value = "Nevada"   ' Pause the code to allow experimentation   Stop   rstOpt.Update   rstOpt.Close End Sub 

CAUTION

You'll see that this code opens a new connection to the current project, rather than piggybacking on CurrentProject.Connection. That's because you can't open a Recordset with pessimistic locking on the current connection. If you try, you don't get an error, but instead Access silently changes the locking type to optimistic.

To see how this code interacts with another attempt to edit data, try this exercise:

1. Restore the first record of the Clients table to its original state.
   
   

2. Run the TestPessLock procedure from the Immediate window. It stops with the cursor at the Stop statement. At this point, the procedure already has the first record locked. With pessimistic locking, the first edit locks the record.
   
   

3. Now return to the Access user interface and open the Clients table's datasheet directly from the database container.
   
   

4. Change the state of the first record in the table to any value you like for example, "Oregon." Attempt to leave the row to save the change. You'll receive the error message shown in Figure 19.3. Click OK to dismiss the message. The cursor will still be in the edited record.
   
   

   Figure 19.3. Pessimistic locking message.

   

   
   

5. Return to the module and press F5 to continue. Access will save the change from the code.
   
   

6. Return to the user interface and attempt to save the record again. You'll get a write conflict dialog box, because the change from the code was already saved.
   
   

Access apparently enabled you to edit the locked record. But as you saw in step 4, it doesn't enable you to save the record. When you're editing in Access, you're really editing a local copy of the data until you try to save it. That's the point at which Access checks for write conflicts.

There are two other locking constants you can use besides the ones for optimistic and pessimistic locking. You can find a brief discussion of these constants in "Creating and Opening a Recordset," p. 245.