Using Exclusive Mode

As previously mentioned, exclusive mode is the most restrictive way you can open a database. It prevents all other users from opening the database. This is generally useful for administrative or bulk changes to the database, such as repair or compact operations, or when making structural changes to the database’s 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 deploy your application in a multiuser environment, exclusive mode is no longer an option; you must open the database in shared mode.

Û To open a database in exclusive mode

1. Declare a database variable using the Dim statement.
   
   
2. Use the OpenDatabase method to open the database, specifying a value of True for the options argument.
   
   
3. Close the database object when finished to allow it to be accessed by other users.

The following code checks to see whether a database can be opened exclusively, using the CanOpenDbExclusively function defined in Chapter 2, “Introducing Data Access Objects.” If this function returns true, then the following code opens the database exclusively. In this example, strDbPath is the path to the database:

Function OpenDatabaseExclusive(strDbPath As String) As Database
	Dim dbs As Database
	  
	' Check whether database can be opened exclusively.
	If CanOpenDbExclusively(strDbPath) Then
		' Open database exclusively and return reference.
		Set dbs = OpenDatabase(strDbPath, True)
		Set OpenDatabaseExclusive = dbs
	Else
		MsgBox "Cannot open database exclusively." _
			& "You or another user may already have it open."
		' Return Nothing.
		Set OpenDatabaseExclusive = Nothing
	End If
End Function

You may want to prevent your application’s users from opening a shared database in exclusive mode. This is particularly useful if your users use applications such as Microsoft Access that allow databases to be opened exclusively through the user interface. You can accomplish this if you have established Microsoft Jet user-level security. Once user-level security is established, you can use the Microsoft Access user interface to prevent a user from opening a database in exclusive mode by removing that user’s Open Exclusive permission for the Database object. If you have defined user-level security groups, you can remove the Open Exclusive permission for the Database object for any group that user belongs to. Note that you probably want to allow administrative users the option of opening the database exclusively. You can set this permission using DAO by setting the Permissions property of the Databases Container object for that user or group to the dbSecDBExclusive constant.

See Also For more information about establishing user-level security and setting permissions, see Chapter 10, “Managing Security.”

When you are setting locks in code with any level of locking, it’s important that you handle any errors that occur. In Microsoft Jet, you don’t check to see if a lock can be set before doing it. Instead, you try the operation and check to see if it succeeded. This approach makes it unnecessary for Microsoft Jet to supply a large number of status functions that usually must be invoked before attempting an operation.

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 attempting to set a lock; you handle the error only if it occurs. When writing multiuser code, you should handle the error by displaying an informational message and giving the user an opportunity to retry the operation.

The OpenDatabaseExclusive procedure shown earlier in this section uses this technique.