Microsoft Office 2000/Visual Basic Programmer's Guide

Connection Control

The Jet 4.0 database engine provides a new feature called connection control, which prevents users from connecting to a database. This feature is also called passive shutdown because when connection control is invoked, users who are currently connected to the database remain unaffected until they disconnect. This capability is useful if you need to acquire exclusive access to a database to perform maintenance operations, such as compacting the database, or make updates to the database's design.

When connection control is invoked, users who are currently connected to a database remain unaffected until they disconnect from the database. At that point they will be unable to reconnect to the database until the user who invoked connection control revokes it or closes his or her connection to the database.

The following scenarios provide examples of how the connection control feature works. Assume there are five users in the database and that user five has invoked connection control on the database:

• Scenario 1   User six tries to connect to the database, but is denied access, and an error message is returned stating that user five is preventing the database from being opened.
  
  
• Scenario 2   User one closes the database and tries to reconnect to the database, but is denied access, and an error message is returned stating that user five is preventing the database from being opened.
  
  
• Scenario 3   User five closes the database. User six tries to open the database and is successful. This is because connection control only persists while the user who invoked it remains connected to the database.
  
  
• Scenario 4   Users one through four exit the database. User five uses the ADO OpenSchema method to retrieve a list of users in the database and determines that no other users are in the database. User five compacts the database, closes the database, and connection control is automatically revoked.

To use connection control, set the Jet OLEDB:Connection Control provider-specific property on an ADO Connection object. The Jet OLEDB:Connection Control property is a Long value that can be set to 1 to invoke connection control, or to 2 (the default value) to revoke connection control. The following procedure either invokes or revokes connection control for the current Access database, based on the value of the opgConnectionControl enumeration, which can be either INVOKE_CONTROL (which equals 1) or REVOKE_CONTROL (which equals 2).

Sub ConnectionControl(lngState As opgConnectionControl)
   CurrentProject.Connection. _
      Properties("Jet OLEDB:Connection Control") = lngState
End Sub

The ConnectionControl procedure can be found in the MultiuserIssues 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.