If you develop an application that allows two or more users (or applications) to refer to the same data set, you want to ensure that your application shares data effectively. This section explores strategies for managing multiuser access to data with different locking techniques, and it then discusses how to resolve locking conflicts in a multiuser environment.
When one user is adding or changing data in a multiuser database, you need a way to prevent other users from changing the data until the first user is finished with it. To accomplish this, Jet provides three locking mechanisms. When a data set is locked, other users can read the records inside the data set, but only the user who initiated the lock can edit them.
By default, Jet locks an entire page of data when a record within the page is edited. A page is a block of record data that takes up approximately 2048 bytes (2 kilobytes) of memory. Depending on the size of each record, a page can contain one record or many records.
Note
When Jet locks a record from an external database in Microsoft FoxPro, dBASE, Paradox®, or Btrieve® format, it locks the smallest unit of memory (that is, record, page, or file) that's lockable in the external database.
Pessimistic locking is the default locking scheme and is in effect when the LockEdits property is True. Jet locks the entire page containing the record you're editing as soon as you use the Edit method, and it releases the locked page when you either terminate a transaction or explicitly release the lock by using the CancelEdit method. The locked page isn't static — it moves up when you move up in the set of records, and it moves down when you move down, so it's possible to unintentionally lock a set of records.
Optimistic locking is a locking scheme that allows you to edit a record in memory and to lock the record only when you're ready to write the changes to the database. This method attempts to lock the record when you call the Update method. The advantage of optimistic locking is that pages are locked only briefly. To use optimistic locking, set the LockEdits property to False.
A common practice in most applications are Recordset object operations: adding and editing records, or moving within a Recordset object. This section describes these operations.
One way to navigate inside a Recordset object is to move up or down one record at a time using the Move method. You can also use one of the Find methods (which one you use depends on your position in the Recordset object) and a specified criterion to locate a record in a dynaset or a snapshot. If your Recordset object is opened as a table, use the Seek method for optimal performance. For information about the syntax of the four Find methods and the Seek method, see the appropriate topic in Help.
To add a new record, use the AddNew method to prepare the record for editing, and use the Update method to save the record. The AddNew method prepares a new, blank record and makes it the current record. The Update method saves the record and repositions the record pointer back to the record that was active before you used the AddNew method. The following code segment adds a new record to the Shippers table in Nwind.mdb.
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("Nwind.mdb")
Set rs = db.OpenRecordset("Shippers", dbOpenTable)
rs.MoveFirst
rs.AddNew
rs.Fields(0).value = "4"
rs.Fields(2).value = "Global Parcel Service"
rs.Fields(3).value = "(503)545-3190"
rs.Update
End If
When you edit records, you use the Edit method to mark the start of an editing session, and you use the Update method to signal the completion of the session. The Edit method tells Jet to buffer the changes to the specified record and write the changes to the data set when the editing tasks are completed. The Update method saves the record. The following code changes the job title for each sales representative in the Employees table.
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("Nwind.mdb")
Set rs = db.OpenRecordset("Employees", dbOpenTable)
rs.MoveFirst
Do Until rs.EOF
If rs.Fields(3).value = "Sales Representative" Then
rs.Edit
rs.Fields(3).value = "Account Executive"
rs.Update
End If
rs.MoveNext
Loop
Note
You must use the Edit method before you use the Update method to write changes back to a record; otherwise, an error will occur.
Advanced Note
If you're changing large groups of records regularly, it's faster and easier to create a SQL statement than it is to write code. For examples that could replace the code preceding these notes, see "Update method" in Help.
If your application depends on successfully adding or deleting records in one data set before proceeding to another one, you'll probably want some assurance that the first set of tasks has been completed successfully before you begin the second set. For example, if you update accounting records, you'll want records to be posted to Receivables before you delete records from Payables — otherwise, the books won't balance. When you wrap these actions into transactions, you ensure that each transaction is performed successfully before your application continues.
The BeginTrans method marks the beginning of a transaction. When you've completed your tasks, use the CommitTrans method to make the changes permanent, or use the RollBack method to delete the changes from memory. The most common way to implement transaction processing is within an error routine that traps errors during your procedure. If an error occurs, the routine flags the error and ignores any changes to the file; otherwise, the routine makes these changes permanent.
The following example demonstrates how transaction processing works.
Sub ChoiceToCommit()
On Error GoTo MyErrorHandler
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("d:\access\sampapps\nwind.mdb")
Set rs = db.OpenRecordset("Customers")
rs.MoveFirst
BeginTrans
MsgBox "The original record value is " & rs.Fields(0).Value
rs.Edit
rs.Fields(0).Value = 5000
rs.Update
action = MsgBox("We are changing the value to " & rs.Fields(0).Value
& " ." & Chr(13) & " Do you want to continue? ", vbYesNo)
If action = vbYes Then
CommitTrans
Else
Rollback
End If
rs.Close
db.Close
Exit Sub
MyErrorHandler:
MsgBox DBEngine.Errors(0)
End Sub
In the BeginTrans segment of code, rs.Edit marks the beginning of an editing session within the transaction; rs.Update marks the end of the editing session and updates the record set in memory only. The first message box displays the original value of the first field in the first record of the Customers table in Nwind.mdb. The second message box tells you that the original value is about to be changed to 5000 and asks you whether you want to continue. If you click Yes, the CommitTrans method is triggered, and the change becomes permanent. If you click No, the RollBack method is triggered, and the changes are deleted from memory, leaving the record unchanged.