| Microsoft Office 2000/Visual Basic Programmer's Guide | |
Although bound forms are one way to create a multiuser solution, they don't provide the flexibility that VBA procedures do. When you write your own locking procedures and run them from unbound forms, you can adapt your locking strategy to handle conflicts among users in a way that's most suitable for your situation — something that isn't possible with bound forms.
There are four different levels at which you can lock data in Recordset objects:
Note Whether records are to be locked by using page- or record-level locking is determined when the database is opened by using the Open databases using record-level locking setting on the Advanced tab of the Options dialog box (Tools menu). From ADO code, page- or record-level locking can also be controlled by setting the provider-specific Jet OLEDB:Database Locking Mode property of the Connection object, and the provider-specific Jet OLEDB:Locking Granularity property of the Recordset object. For more information, see "Page-Level Locking vs. Record-Level Locking" earlier in this chapter.
To determine at which level you want to lock objects in your solution, you must decide the level of concurrency that you need. Concurrency is the ability of more than one transaction to access the same data at the same time. For example, if you want the objects to be available as often as possible to as many users as possible, a high-concurrency strategy would dictate that you use page- or record-level locking, which is the least restrictive level. However, if your solution requires guaranteed access to most or all of the data in the database, you may opt for exclusive mode. This ensures that your solution has the database open exclusively so it can't be shared by other users.
The four levels aren't mutually exclusive. Many multiuser solutions use all four levels at different times. For example, in an order-entry system in which you want many order takers to have access to data simultaneously, you can use page- or record-level locking to lock data in the Orders table. You can use recordset locking at the end of the day to lock a summary table and update it with summary data. Finally, you can use exclusive mode each night to lock the entire database while you compact the database.
Exclusive mode is the most restrictive way you can lock data. It prevents all other users from opening the database; therefore, it denies all users access to all data in the database. This is useful for performing administrative or bulk changes to the database, such as repair or compacting operations, or for making changes to the design of the database.
When you access a database in a single-user environment, you typically open it in exclusive mode. This may provide better performance because the Jet database engine doesn't have to lock and unlock objects or refresh its cache. To allow multiple users to open the database, users must not use exclusive mode to open the database or other users will be locked out. If you want multiple users to share data with your solution, make sure none of them open your solution's database in exclusive mode. You can use security features in Access to deny most users Open Exclusive permission. The database administrator should have Open Exclusive permission to perform such tasks as compacting and repairing the database. For information about setting permissions, see Chapter 18, "Securing Access Databases."
You can use code to open a database in exclusive mode. To do so when using ADO, set the Mode property of the Connection object you are using to open the database to adModeShareExclusive. The following procedure opens the specified Access database in exclusive mode and checks for errors to determine if the operation was successful. Note that the For Each…Next loop that displays any errors uses the SQLState property of the ADO Error object to retrieve the Jet error number. When you use ADO, the Number property of the VBA Err object doesn’t return the Jet error number.
Sub OpenDBExclusive (strDBPath As String)
Dim cnnDB As ADODB.Connection
Dim errCurrent As ADODB.Error
' Initialize Connection object.
Set cnnDB = New ADODB.Connection
' Specify Microsoft Jet 4.0 provider and then try
' to open the database specified in the strDBPath
' variable in exclusive mode.
On Error Resume Next
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeShareExclusive
.Open strDBPath
End With
If Err <> 0 Then
' If errors occur, display them.
For Each errCurrent In ADODB.Errors
Debug.Print "Error " & errCurrent.SQLState _
& ": " & errCurrent.Description
Next
Else
' No errors: You have exclusive access.
Debug.Print "The database is open in exclusive mode."
End If
' Close Connection object and destroy object variable.
cnnDB.close
Set cnnDB = Nothing
End Sub
The OpenDBExclusive procedure can be found in the OpenDatabase module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.
Using ADO to open an Access database in exclusive mode has the same effect as selecting the Open Exclusive button in the Open dialog box (File menu). If one user already has the database open for write access, and another user tries to open the database in exclusive mode, a locking error occurs. The second user can’t open the database in exclusive mode until the first user closes it. For information about determining which users have a shared database open, see "Identifying Users" later in this chapter.
If you open the database for a user in shared mode with read-only access by setting the Mode property of the Connection object to adModeRead when opening the database, any other user can read the database, but the current user can't write to it. You may want to do this to prevent the current user from changing data or the design of objects in the database without establishing user-level security. Using ADO to open an Access database in read-only mode has the same effect as selecting the Open Read-Only button in the Open dialog box (File menu).
Note Opening a database in read-only mode doesn't prevent locking conflicts, because other users can still open the database in shared, read/write mode.
The Access Open dialog box (File menu) also includes an Open Exclusive Read Only button. Clicking this allows you to open the database in both read-only and exclusive modes. This prevents other users from opening the database, and also prevents you (the current user) from making changes to the database. If you want to use code to open the database in exclusive, read-only mode, set the Mode property to both the adModeShareExclusive and adModeRead constants by combining them with the plus (+) operator, as shown in the following line of code:
cnnDB.Mode = adModeShareExclusive + adModeRead
If you want to implement any other form of locking, you must open your database in shared mode. When a database is opened in shared mode, multiple users can simultaneously access the database and the Jet database engine handles conflicts between users.
You can use code to open a database in shared mode. When using ADO with the Microsoft Jet 4.0 OLE DB Provider, the Open method of the Connection object opens the database in shared mode by default. To open the database explicitly in shared mode, set the Mode property to adModeShareDenyNone. The following procedure opens the specified database in shared mode and checks for errors to determine if the operation was successful.
Sub OpenDBShared (strDBPath As String)
Dim cnnDB As ADODB.Connection
Dim errCurrent As ADODB.Error
' Initialize Connection object
Set cnnDB = New ADODB.Connection
' Specify Microsoft Jet 4.0 provider and then try to open
' the database specified in the strDBPath variable in shared
' mode.
On Error Resume Next
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Mode = adModeShareDenyNone
.Open strDBPath
End With
If Err <> 0 Then
' If errors occur, display them.
For Each errCurrent In ADODB.Errors
Debug.Print "Error " & errCurrent.SQLState _
& ": " & errCurrent.Description
Next
Else
' No errors: You have shared access.
Debug.Print "The database is open in shared mode."
End If
' Close Connection object and destroy object variable.
cnnDB.close
Set cnnDB = Nothing
End Sub
The OpenDBShared procedure can be found in the OpenDatabase module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.
Using ADO to open a database in shared mode has the same effect as selecting the Open button in the Open dialog box (File menu).
When you are setting locks in code with any level of locking, it's important that you handle any errors that occur. In Access, you don't check to see if a lock can be set before you set it. Instead, you try the operation and then check to see if it succeeded.
The typical approach to locking is a four-step process:
This approach works well because you don't have to anticipate every possible error before trying to set a lock; you handle the error only if it occurs. When writing multiuser code, you should handle the error by displaying a message and giving the user an opportunity to retry the operation.
The most common error you'll encounter when you use exclusive mode is error number 3006, "Database <name> is exclusively locked." This error occurs when you try to open a database that is currently open in exclusive mode by another user. To respond to the error, wait until the other user has finished working with the database and then try the operation again. For more information about error handling, see Chapter 8, "Error Handling and Debugging."
You use exclusive mode to lock an entire database. You use recordset locking, on the other hand, to specify locking for an entire table in a shared database. You can specify a read lock to prevent other users from reading records in the table, a write lock to prevent other users from editing records in the table, or both. ADO and OLE DB don't currently support this kind of locking, so you must use DAO code if you require recordset locking. Recordset locking applies only to DAO table- and dynaset-type Recordset objects; it can't be used with DAO snapshot-type or forward-only – type Recordset objects, because these are inherently read-only objects. To implement recordset locking, Microsoft Jet places shared table-read and shared table-write locks.
After you open the database in shared mode, you can implement recordset locking by specifying either of the dbDenyRead and dbDenyWrite constants in the Options argument of the DAO OpenRecordset method. You can also combine both constants by using the plus (+) operator if you want to apply both read and write locks to the table.
To open a DAO Recordset object with recordset locking
For example, the following code locks a table by opening it with the dbDenyWrite and dbDenyRead constants specified in the Options argument of the OpenRecordset method. For the duration of the procedure, no other users can access this table. If an error occurs when you try to open the table, the function returns Nothing. This procedure requires the DAOOpenDBShared procedure, which attempts to open a database in shared mode and handles errors if it can't. The DAOOpenDBShared and DAOOpenTableExclusive procedures can be found in the RecordsetLocking module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.
Function DAOOpenTableExclusive(strDbPath As String, _
strRstSource As String) As DAO.Recordset
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
' Open the database in shared mode by calling the
' DAOOpenDBShared function.
Set dbs = DAOOpenDBShared(strDbPath)
' Check whether database was successfully opened in shared mode.
' If it was, open specified table exclusively.
If Not dbs Is Nothing Then
Set rst = dbs.OpenRecordset(strRstSource, _
dbOpenTable, dbDenyRead + dbDenyWrite)
' Check whether recordset was opened successfully. If it was,
' return the Recordset object; otherwise return Nothing.
If Not rst Is Nothing Then
Set DAOOpenTableExclusive = rst
Else
Set DAOOpenTableExclusive = Nothing
End If
Else
Set OpenTableExclusive = Nothing
End If
End Function
Important You must also use DAO code to work with the Recordset object returned by this procedure. You can't use ADO code to work with DAO objects, and vice versa.
Note If you open a Recordset object without specifying any value for the Options argument, Microsoft Jet uses record locking by default. It opens the Recordset object in shared mode and locks only the data that's being edited in the current record.
For more information about opening a Recordset object by using DAO, see DAO 3.6 Help and the Microsoft Jet Database Engine Programmer's Guide, Second Edition (Microsoft Press, 1997).
As with opening databases in exclusive mode, setting locks on Recordset objects can cause errors if the lock fails. You should use the four-step process described in the previous section: Turn off error handling, attempt the operation, check for errors and handle any that occur, and finally, turn on error handling.
The most common error in recordset locking is error number 3262, "Couldn't lock table <name>; currently in use by user <name> on machine <name>." This error occurs when you try to use the OpenRecordset method on an object that can't be locked. There is usually another user who has the same table or tables locked in a way that prevents your action. To respond to the error, wait a short period of time and try the operation again.
Whereas opening a database in exclusive mode locks the entire database, and recordset locking locks one or more individual tables, page- or record-level locking locks only the page or record containing the record that is currently being edited. This is the least restrictive level of locking. When you use page- or record-evel locking, other users can read data from the locked page or record, but they can’t change it. Record-level locking is the default for ADO and DAO Recordset objects. Page-level locking is the default for SQL DML statements (bulk operations such as UPDATE, DELETE, and INSERT INTO statements) that use ADO Command objects or DAO QueryDef objects.
When working with Recordset objects that lock data at the page or record level, you must specify which type of locking you want to use. There are two of types of locking available when you use page- and record-level locking: pessimistic locking and optimistic locking.
You can set the type of locking for an ADO Recordset object by specifying either the adLockPessimistic or the adLockOptimistic constant in the LockType argument of the Open method of the Recordset object. You can't change the type of locking after you open an ADO Recordset object as you can with a DAO Recordset object by setting its LockEdits property.
With pessimistic locking, a record (or the page the record resides on) is locked once you begin editing the record. In ADO you don't use the Edit method to start an editing operation as you do in DAO. To edit a field's value in ADO, you simply change the Value property of a Field object. The record or page remains locked until you save your changes to the record by moving to a new record or by using the Update method. If you aren't using batch updating, you must use the CancelUpdate method to cancel the edit before moving to a new record.
The main advantage of pessimistic locking is that after you have obtained a lock, you know that you won't encounter any locking conflicts as long as the record is locked. Additionally, pessimistic locking is the only way to guarantee that your solution reads the most current data, because one user can't change a record after another user has started to edit it.
The disadvantage to using pessimistic locking when you are using page-level locking is that the entire page is locked for the duration of the procedure. Therefore, other users can't change any records on that page until the lock is released. However, by default, both ADO and DAO Recordset objects use record-level locking, so this is only an issue if you override the default setting.
To use pessimistic locking in your code
With optimistic locking, a record or page is locked only when you try to save the changes to the record by moving to a new record or by using the Update method. Because the lock is applied only when your solution tries to update the record, you minimize the time the lock is in place; this is the main advantage of optimistic locking.
The disadvantage of optimistic locking is that when you are editing a record, you can't be sure that the update will succeed. Your attempt to update the record with your edits will fail if another user updates the record first.
To use optimistic locking in your code
It's possible for the Update method to fail in optimistic locking. For example, if one user has a Recordset object open with pessimistic locking, and another user tries to update data on the same page by using optimistic locking, the second user's attempt to update will fail.
Note Optimistic locking turns into pessimistic locking when transactions are used. Because a transaction locks data so that users can't change it until the transaction is committed, pessimistic locking is used even though the LockType argument may have been set to adLockOptimistic. For more information about transactions, see "Using Transactions" later in this chapter.
When you use record- or page-level locking, before proceeding, your code must check to see if the attempted lock succeeded. As with exclusive mode and recordset locking, you should turn off error handling, attempt the operation, check for errors and handle any that occur, and finally, turn on error handling.
The following table describes the three most common errors that your solution may encounter when you use record- or page-level locking. These errors are returned by the Jet Database engine.
| Error number and text | Cause and suggested response |
| 3218 "Could not update; currently locked." | This error occurs when a user tries to save a record that is locked by another user. To handle this error, program your solution to wait for a short period of time, and then try to save the record again. Or, you can display a message that explains the problem and give users the opportunity to try the operation again. |
| 3197 "The database engine stopped the process because you and another user are attempting to change the same data at the same time." | This error occurs if another user has changed the data since the current user started trying to update the record. When this error is triggered depends on the locking mode you are using:
In either situation, to handle this error, program your solution to display a message that informs the user that someone else has changed the data. You may want to display the current data and give users the choice of whether to overwrite the other user's changes or cancel their own edits. |
| 3260 "Couldn't update; currently locked by user <name> on machine <name>." | This error occurs when a user attempts to edit a record and the current record (or if you are using page-level locking, the page it is on) is locked. If you are using page-level locking, this error also occurs when a user uses the AddNew method or the Update method to save a record on a locked page. This situation can occur when the user is trying to save a new record or when optimistic locking is in place and another user locks the page. To handle this error, program your solution to wait for a short period of time, and then try to save the record again. Or, you can inform users of the problem and allow them to indicate whether or not they want to retry the operation. |
You can write a procedure that tries to lock a record, checks to see if an error occurred, and responds to it regardless of the type of error. Alternatively, you can write a procedure that identifies the specific error that occurs and responds to it. The following procedure tries to edit a record. If a locking error occurs, the procedure tries to identify the error and responds accordingly. If an unidentified error occurs, the procedure displays a message and exits the function.
Function UpdateUnitsInStock(strProduct As String, _
intUnitsInStock As Integer,_
intMaxTries As Integer) As Boolean
Dim strConnect As String
Dim cnn As ADODB.Connection
Dim rstProducts As ADODB.Recordset
Dim blnError As Boolean
Dim intCount As Integer
Dim intLockCount As Integer
Dim intChoice As Integer
Dim intRndCount As Integer
Dim intI As Integer
Dim strMsg As String
Const MULTIUSER_EDIT As Integer = 3197
Const RECORD_LOCKED As Integer = 3218
Const NWIND_PATH As String = "C:\Program Files\Microsoft " _
"Office\Office\Samples\Northwind.mdb"
On Error GoTo UpdateUnitsInStockError
' Format connection string to open database.
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NWIND_PATH
' Open database.
Set cnn = New ADODB.Connection
cnn.Open strConnect
' Open the Products table for editing by using pessimistic locking.
Set rstProducts = New ADODB.Recordset
rstProducts.Open "Products", cnn, adOpenKeyset, _
adLockOptimistic, adCmdTableDirect
With rstProducts
.Find Criteria:="ProductName = " & "'" & strProduct & "'", _
SkipRecords:=0, _
SearchDirection:=adSearchForward
If .EOF Then
MsgBox "Record not found."
UpdateUnitsInStock = False
GoTo UpdateUnitsInStockExit
End If
' Attempt to edit the record. If a lock error occurs, the
' error handler will attempt to resolve it. Because this
' procedure uses pessimistic locking, errors are generated
' when you begin to edit a record. If you used optimistic
' locking, lock errors will occur when you update a record.
![UnitsInStock] = intUnitsInStock
.Update
UpdateUnitsInStock = True
End With
UpdateUnitsInStockExit:
On Error Resume Next
rstProducts.Close
Set rstProducts = Nothing
Exit Function
UpdateUnitsInStockError:
' Check the SQLState property of the first Error object in
' the Errors collection to retrieve the Jet error number.
Select Case cnn.Errors(0).SQLState
Case MULTIUSER_EDIT
' Data in the recordset has changed since it was opened.
' Display current value to user and provide option to overwrite.
strMsg = "The record was changed by another user since you opened it." _
& vbCr & "The value of UnitsInStock is now: " _
& rstProducts.Fields("UnitsInStock").OriginalValue & vbCr _
& " Save anyway?"
intChoice = MsgBox(strMsg, vbYesNo + vbQuestion)
If intChoice = vbYes Then
Resume
Else
UpdateUnitsInStock = False
Resume UpdateUnitsInStockExit
End If
Case RECORD_LOCKED
' The record is locked.
intLockCount = intLockCount + 1
' Tried to get the lock twice already. Let the user cancel or retry.
If intLockCount > 2 Then
intChoice = MsgBox(Err.Description & " Retry?", _
vbYesNo + vbQuestion)
If intChoice = vbYes Then
intLockCount = 1
Else
UpdateUnitsInStock = False
Resume UpdateUnitsInStockExit
End If
End If
' Yield to Windows.
DoEvents
' Delay a short random interval, making it longer each
' time the lock fails.
intRndCount = intLockCount ^ 2 * Int(Rnd * 3000 + 1000)
For intI = 1 To intRndCount
Next intI
Resume ' Try the edit again.
Case Else ' Unanticipated error.
MsgBox "Error " & cnn.Errors(0).SQLState & ": " _
& Err.Description, vbOKOnly, "ERROR"
UpdateUnitsInStock = False
Resume UpdateUnitsInStockExit
End Select
End Function
The UpdateUnitsInStock procedure can be found in the RecordLocking module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.
Note that the code specifies a random interval to retry the operation. This is an important technique for making sure that two users who are trying to update the same record don't end up in a deadlock situation where the code keeps trying to lock the record at the same time. By introducing a random element into the timing loop, you can minimize the chances of a deadlock.
You may want to check to see if a record is locked without actually locking its page or pages. The following procedure attempts to edit the current record to determine if it is locked.
Function IsRecordLocked(strProduct) As Boolean
Dim rst As ADODB.Recordset
Dim strConnect As String
Dim cnn As ADODB.Connection
Dim strSQL As String
On Error GoTo RecordLockedError
Const NWIND_PATH As String = "C:\Program Files\Microsoft " _
& "Office\Office\Samples\Northwind.mdb"
' Format connection string to open database.
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NWIND_PATH
' Open database.
Set cnn = New ADODB.Connection
cnn.Open strConnect
' Format SQL statment to open Recordset object on the specified record.
strSQL = "SELECT Products.ProductName,Products.UnitsInStock FROM " _
& "Products WHERE Products.ProductName ='" & strProduct & "';"
' Open the Products table for editing by using pessimistic locking.
Set rst = New ADODB.Recordset
With rst
.Open strSQL, cnn, adOpenKeyset, _
adLockPessimistic, adCmdText
' If no record is found, display message, return False, and exit.
If .EOF Then
MsgBox "Record not found."
IsRecordLocked = False
GoTo IsRecordLockedExit
End If
' Attempt to edit a value in the record. If this succeeds,
' return False; otherwise an error will be triggered.
![UnitsInStock] = 999
IsRecordLocked = False
End With
IsRecordLockedExit:
rst.CancelUpdate
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing
Exit Function
RecordLockedError:
Select Case cnn.Errors(0).SQLState
' Record is locked.
Case 3260
IsRecordLocked = True
GoTo IsRecordLockedExit
Case Else
Resume Next
End Select
End Function
The IsRecordLocked procedure can be found in the RecordLocking module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.