|< Day Day Up >|
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.
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.
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:
On the other hand, if the table were using pessimistic record locking, the sequence could go like this:
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.
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:
When you see this error message, you have three choices:
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:
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
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:
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.
|< Day Day Up >|