Locking Shared Data by Using Recordset Objects in VBA

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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.

Levels of Locking

There are four different levels at which you can lock data in Recordset objects:

• ****Exclusive mode  **** To prevent all other users from using the entire database. This is the most restrictive level.

• ****Recordset locking  **** To lock a Recordset object's underlying table.

• ****Page-level locking  **** To lock the entire 4096-byte (4K) page where the data that is being edited resides.

• ****Record-level locking  **** To lock only the current record that is being edited.

  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.

Using Exclusive Mode

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.

Using Read-Only Mode

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

Opening the Database in Shared Mode

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).

Checking for Errors

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:

1. Turn off error handling.

2. Attempt the operation.

3. Check to see if an error occurred. If so, handle the error based on the error number.

4. Turn on error handling.

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."

Using Recordset Locking

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

1. Open the Recordset object's database in shared mode.

2. Determine the type of recordset locking you want to use.

3. Open the Recordset object by using the OpenRecordset method and set the Options argument to specify the type of locking that you want.

4. Close the Recordset object when you're finished performing operations on the data to release any locks on the Recordset object.

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).

Checking for Errors with Recordset Locking

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.

Using Page-Level and Record-Level Locking

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: pessimisticlocking and optimisticlocking.

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.

Pessimistic Locking

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

1. Implement pessimistic locking by setting the LockType argument of the Open method of the Recordset object to adLockPessimistic when you open the Recordset object.

2. Move to the record that you're interested in.

3. Edit the record by specifying changes to Field objects. When you use pessimistic locking, ADO attempts to lock the record as soon as you start editing the first Field object. If the lock fails, try again.

4. When the record is locked, make your changes to the record.

5. Save your changes to the record by moving to a new record or by using the Update method. After your changes are saved, the lock is released.

Optimistic Locking

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

1. Implement optimistic locking by setting the LockType argument of the Open method of the Recordset object to adLockOptimistic when you open the Recordset object.

2. Move to the record that you're interested in.

3. Edit the record by specifying changes to Field objects. When you use optimistic locking, this doesn't lock the record.

4. Save your changes to the record by moving to a new record or by using the Update method. This attempts to lock the record.

5. Check to see if the Update method succeeded. If it didn't, try again.

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.

Checking for Errors with Record-Level and Page-Level Locking

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.

Record-Level or Page-Level Locking Code Example

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.

Testing a Record for Locking Status

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.