Locking Shared Data in Recordsets

Although bound forms are one way to create a multiuser application, they don’t provide the flexibility that Visual Basic procedures do. When you write your own locking procedures and run them from unbound forms, you can adapt your locking strategy and handle conflicts among users in a way more suitable for your situation—something that isn’t possible with bound forms.

Levels of Locking

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

• Exclusive mode prevents all other users from using the entire database. This is the most restrictive level.
• Recordset locking locks a Recordset object’s underlying tables.
• Page locking locks the 2,048 byte (2K) page where the data that is being edited resides. This is the least restrictive level.

To determine at which level you want to lock objects in your application, 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 locking, which is the least restrictive level. However, if your application requires guaranteed access to most or all of the data in the database, you may opt for exclusive mode. This ensures that your application has the database open exclusively so it can’t be shared by other users.

The three levels aren’t mutually exclusive. Many multiuser applications use all three 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 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 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 compact operations, or when making changes to the database’s underlying structure, or schema.

When you access a database in a single-user environment, you typically open it in exclusive mode. This may provide better performance because Microsoft Jet doesn’t have to lock and unlock objects or refresh its cache. When you access a database in a multiuser environment, exclusive mode is no longer an option; you must open the database in shared mode.

If you want multiple users to share data with your application, make sure none of them open your application’s database in exclusive mode. You can use security features in Microsoft 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.

See Also   For information on setting permissions, search the Help index for “setting permissions.”

You can use code to open a database in exclusive mode. To do so, use the OpenDatabase method on the current Workspace object, and specify True for the options argument. The following procedure opens the Northwind sample database in exclusive mode and checks for errors to determine if the operation was successful:

Sub OpenDatabaseExclusive()

	Dim dbs As Database
	Dim errCurrent As Error

	' Try to open the Northwind database exclusively.
	On Error Resume Next
	Set dbs = OpenDatabase("Northwind.mdb", True)

	If Err <> 0 Then
		' If errors occur, display them.
		For Each errCurrent In DBEngine.Errors
			Debug.Print errCurrent.Description
		Next
	Else
		' No errors: You have exclusive access.
		Debug.Print "The database is open in exclusive mode."
	End If

	dbs.Close
End Sub

Using Visual Basic to open the Northwind sample database in exclusive mode has the same effect as selecting the Exclusive check box 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.

Using Read-Only Mode

If the database is opened in exclusive mode with read-only access (both the options and read-only arguments of the OpenDatabase method are specified as True), any user can read the database, but no user—including the user who opened it—can write to it. You may want to do this to force other users to open the database in read-only mode without establishing user-level security.

When you open a database in read-only mode without also specifying exclusive mode, you cannot change objects in the database; this includes data and structural information. However, this mode doesn’t prevent other users from changing data.

Note   Opening a database in read-only mode doesn’t prevent locking conflicts.

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 Microsoft 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 OpenDatabaseExclusive procedure in the previous section uses this technique.

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.

See Also   For more information on error handling, see Chapter 8, “Handling Run-Time Errors.”

Using Recordset Locking

You use exclusive mode to lock an entire database. You use recordset locking, on the other hand, to control how one or more individual tables in a database are locked. Recordset locking applies only to table- and dynaset-type Recordset objects; it doesn’t apply to snapshot- and forward-only-type Recordset objects because these are inherently read-only objects.

Note   The default recordset lock is a write lock, which means that other users can’t edit data in the locked records. You can also prevent users from reading the data by setting a read lock. You may want to do this if you are making bulk changes to a Recordset object and you want to prevent users from reading the data until you have completed your update. To set a read lock, specify the dbDenyRead constant in the options argument of the OpenRecordset method. For more information, search the Help index for “OpenRecordset method.”

Opening the Database in Shared Mode

If you want to implement recordset 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 Microsoft Jet handles conflicts between users.

You can use code to open a database in shared mode. To do so, use the OpenDatabase method on the current Workspace object, and specify False for the options argument. The following procedure opens the Northwind sample database in shared mode and checks for errors to determine if the operation was successful:

Sub OpenDatabaseShared()

	Dim dbs As Database
	Dim errCurrent As Error

	' Try to open the Northwind database in shared mode.
	On Error Resume Next
	Set dbs = OpenDatabase("Northwind.mdb", False)

	If Err <> 0 Then
		' Errors occurred: Display them.
		For Each errCurrent In DBEngine.Errors
			Debug.Print errCurrent.Description
		Next
	Else
		' No errors: Database is in shared mode.
		Debug.Print "The database is open in shared mode."
	End If

	dbs.Close
End Sub

Using Visual Basic to open the Northwind sample database in shared mode has the same effect as clearing the Exclusive check box in the Open dialog box (File menu).

Opening the Recordset Object

After you open the database in shared mode, you can implement recordset locking by specifying various constants in the options argument of the OpenRecordset method. You can use a combination of the available constants to more precisely specify the type of locking you want to implement.

See Also   For a complete list of the constants you can specify for the options argument of the OpenRecordset method, see “OpenRecordset Options” in Chapter 9, “Working with Records and Fields.”

Û To open a Recordset object with locking enabled

1. Open the Recordset object’s database in shared mode.
2. Determine the type of recordset locking you want to implement.
3. Open the Recordset object by using the OpenRecordset method and set the options argument to the constant or combination of constants that 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 trying to open the table, the function returns the public constant conFailed.

Function OpenTableEx(dbs As Database, strTable As String) As Integer

	Dim rst As Recordset
	On Error Resume Next

	Set rst = dbs.OpenRecordset(strTable, dbOpenTable, dbDenyRead + dbDenyWrite)
	Select Case Err
		' conSuccess and conFailed are defined at the module level as public
		' constants with values of 0 and -32,761 respectively.
		Case 0: OpenTableEx = conSuccess
		Case Else: OpenTableEx = conFailed
	End Select

	Err = 0

End Function

Note   If you open a Recordset object without specifying any value for the options argument, Microsoft Jet uses page locking by default. It opens the Recordset object in shared mode and locks only the data that’s being edited in the current page.

See Also   For more information on opening a Recordset object, see “Creating a Recordset Object Variable” in Chapter 9, “Working with Records and Fields.”

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 Locking

While exclusive mode locks the entire database, and recordset locking locks one or more individual tables, page locking locks only the page containing the record that is currently being edited. This is the least restrictive level of locking. When you use page locking, other users can read data from the locked page, but they cannot change it. Page locking is the default locking level for Recordset objects.

In the parts of your application that lock data at the page level, you must specify which type of page locking you want to use. There are two types of page locking available: pessimistic locking and optimistic locking.

You can set the type of page locking for a Recordset object by specifying either the dbPessimistic or the dbOptimistic constant in the lockedits argument of the OpenRecordset method. You can also set the type of page locking after you open a Recordset object by using its LockEdits property. For pessimistic locking, set the LockEdits property to True; for optimistic locking, set the LockEdits propertyto False.

Pessimistic Locking

With pessimistic locking, a page is locked once you begin editing a record on that page with the Edit method. The page remains locked until you save your changes to the record with the Update method or cancel the edit, either with the CancelUpdate method or by moving off the current record. Pessimistic locking is the default type of page locking for Recordset objects.

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 application reads the most current data, because one user can’t change a record after another user has started to edit it.

The disadvantage of pessimistic locking is that it locks the entire page for the duration of the procedure. Therefore, other users can’t change any records on that page until the lock is released.

Û To use pessimistic locking in your code

1. Open a table- or dynaset-type Recordset object on the data that you want to edit.
2. Implement pessimistic locking by setting the LockEdits property of the Recordset object to True.
3. Move to the record that you’re interested in.
4. Use the Edit method to allow edits to the record. This locks the record. If the lock fails, try again.
5. When the record is locked, make your changes to the record.
6. Save your changes to the record by using the Update method. After your changes are saved, the lock is released.

Optimistic Locking

With optimistic locking, a page is locked only when you try to save the changes to the record with the Update method. Because the lock is applied only when your application 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 fails if another user updates the record first.

Û To use optimistic locking in your code

1. Open a table- or dynaset-type Recordset object on the data that you want to edit.
2. Move to the record that you’re interested in.
3. Implement optimistic locking by setting the LockEdits property of the Recordset object to False.
4. Use the Edit method to allow edits to the record. This does not lock the record.
5. Make changes to the record.
6. Save your changes to the record by using the Update method. This attempts to lock the record.
7. 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 LockEdits property may have been set to False. For more information on transactions, see “Microsoft Jet Transactions” in Chapter 9, “Working with Records and Fields.”

Checking for Errors with Page Locking

When using page 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 application may encounter when you use page locking.

Function UpdateUnitsInStock(strProduct As String, intUnitsInStock _
	As Integer, intMaxTries As Integer) 

	Dim dbs As Database, rstProducts As Recordset
	Dim blnError As Boolean, intCount As Integer
	Dim intLockCount, intChoice As Integer, intRndCount As Integer, intI As Integer

	Const conFilePath As String = "C:\Program Files\Microsoft Office\Office\Samples\"

	On Error GoTo ErrorHandler

	' Open the database in shared mode. 
	Set dbs = OpenDatabase(conFilePath & "Northwind.mdb")

	' Open the table for editing. 
	Set rstProducts = dbs.OpenRecordset("Products", dbOpenDynaset)

	With rstProducts
		' Set the locking type to pessimistic. Setting LockEdits to
		' False would use optimistic locking.
		.LockEdits = True

		.FindFirst "ProductName = " & Chr(34) & strProduct & Chr(34)

		If .NoMatch Then
			' conErrNoMatch is defined at the module level as a public
			' constant of type Integer with a value of -32737.
			UpdateUnitsInStock = conErrNoMatch
			GoTo CleanExit
		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 it used optimistic locking, lock errors would occur when you
		' update a record. 

		.Edit
		![UnitsInStock] = intUnitsInStock
		.Update
	
	End With
	
CleanExit: 
	rstProducts.Close
	dbs.Close
	Exit Function

ErrorHandler:
	Select Case Err
		Case 3197
			' Data in the recordset has changed since it was opened.
			' Try to edit the record again. This automatically refreshes
			' the recordset to display the most recent data.
			Resume

		Case 3260							' The record is locked.
			intLockCount = intLockCount + 1
			' Tried to get the lock twice already.
			If intLockCount > 2 Then		' Let the user cancel or retry.
				intChoice = MsgBox(Err.Description & " Retry?", _
					vbYesNo + vbQuestion)
				If intChoice = vbYes Then
					intLockCount = 1
				Else
					' conErrRecordLocked is defined at the module level as a public
					' constant of type Integer with a value of -32,736.
					UpdateUnitsInStock = conErrRecordLocked
					Resume CleanExit
				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 " & Err & ": " & Error, vbOKOnly, "ERROR"
			' conFailed is defined at the module level as a public
			' constant of type Integer with a value of -32,761.
			UpdateUnitsInStock = conFailed
			Resume CleanExit

	End Select

End Function

Function RecordLocked(rst As Recordset) As Boolean
	Dim blnLock As Boolean

	On Error GoTo ErrorHandler

	' Save current value of LockEdits property.
	blnLock = rst.LockEdits

	' Set pessimistic locking.
	rst.LockEdits = True

	' Try to edit the record. This generates error 3197 if the
	' record is locked.

	rst.Edit
	RecordLocked = False
	rst.CancelUpdate

	' Restore original value of LockEdits property.
	rst.LockEdits = blnLock

	Exit Function

ErrorHandler:
	Select Case Err
		Case 3197
			Resume Next
		Case Else
			RecordLocked = True
			Resume Next
			Exit Function
	End Select

End Function