Microsoft KB Archive/287655

= How to use connection control to prevent users from logging on at run time in Access 2002 =

Article ID: 287655

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q287655



Advanced: Requires expert coding, interoperability, and multiuser skills.

For a Microsoft Access 2000 version of this article, see 198756.

IN THIS TASK
SUMMARY
 * The Connection Control
 * The User List
 * Code Example

REFERENCES



SUMMARY
By using Microsoft Visual Basic for Applications in Microsoft Access, you can prevent users from logging on to a Jet database. If users are already in the database, they will remain logged on; however, no other users will be able to open the database. This is called a passive shutdown.

The code example in this article demonstrates how to perform a passive shutdown and also how to output a list of users who are already logged on to the database.

back to the top

The Connection Control
The connection control (also known as passive shutdown) feature prevents users from connecting to a database. This capability is useful for a database administrator who needs to acquire exclusive access to a database to perform maintenance, for example, to compact the database, or who needs to make updates to the database schema or applications.

When connection control is invoked, users currently connected to a database will remain unaffected until they disconnect. At that point, they are unable to reconnect until connection control is revoked.

The following scenarios provide additional insight into how this capability works:
 * Five users are in the database. User five initiates passive shutdown. 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.
 * Five users are in the database. User five initiates passive shutdown. 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.
 * Five users are in the database. User five initiates passive shutdown. User five closes the database. User six tries to open the database and is successful. This is because passive shutdown only persists while the user that called it remains connected to the database.
 * Five users are in the database. User five initiates passive shutdown. Users one through four exit the database. User five calls the user list functionality and determines that no other users are in the database. User five closes the database and immediately compacts the database.

back to the top

The User List
The user list feature provides a way of determining who is currently connected to a Microsoft Jet database. The list can be obtained via the ADO programming interface and returns the following information for each user:


 * Name of the computer being used.
 * Security name, that is, the user ID.
 * Whether or not the user is currently connected to the database (A user's ID remains in the lock database until the last user disconnects or until the slot is reclaimed for a new user connection.).
 * Whether or not the user connection was terminated normally.

The user list capability can be used in conjunction with the connection control capability to determine which users are still connected, so that they can be asked to disconnect.

The user list capability is also useful in isolating problems with database corruption that is associated with the activities of a specific user.

back to the top

Code Example
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To demonstrate the code example, follow these steps:  Copy the sample database Northwind.mdb to the root directory of drive C. In Microsoft Access, open C:\Northwind.mdb. Create a new module called ShutDownDB. On the Tools menu, click References. In the References dialog box, click to select the following reference:

Microsoft ActiveX Data Objects 2.1 Library

  Type the following code in the new module:

Sub ShowUserRosterAndPassiveShutdown Dim cn As New ADODB.Connection Dim cn2 As New ADODB.Connection Dim cn3 As New ADODB.Connection Dim rs As New ADODB.Recordset Dim j As Long

On Error GoTo ErrHandler cn.Provider = &quot;Microsoft.Jet.OLEDB.4.0&quot; cn.Open &quot;Data Source=c:\Northwind.mdb&quot; cn2.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _ & &quot;Data Source=c:\Northwind.mdb&quot; ' Restrict other users from opening the database cn.Properties(&quot;Jet OLEDB:Connection Control&quot;) = 1 ' Attempt to open another connection to the database cn3.Open &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _ & &quot;Data Source=c:\Northwind.mdb&quot; ' The user roster is exposed as a provider-specific ' schema rowset in the Jet 4 OLE DB provider. You have to use ' a GUID to reference the schema, as provider-specific schemas ' are not listed in ADO's type library for schema rowsets Set rs = cn.OpenSchema(adSchemaProviderSpecific,, _   &quot;{947bb102-5d43-11d1-bdbf-00c04fb92675}&quot;) ' Output the list of all users in the current database. Debug.Print rs.Fields(0).Name, &quot;&quot;, rs.Fields(1).Name, _ &quot;&quot;, rs.Fields(2).Name, rs.Fields(3).Name Do While Not rs.EOF Debug.Print rs.Fields(0), rs.Fields(1), _ rs.Fields(2), rs.Fields(3) rs.MoveNext Loop ' Close one of the remaining connections cn2.Close ' Reopen the user roster to verify that no other users are in the ' database Output the list of all users in the current database. Set rs = cn.OpenSchema(adSchemaProviderSpecific,, _   &quot;{947bb102-5d43-11d1-bdbf-00c04fb92675}&quot;) Debug.Print rs.Fields(0).Name, &quot;&quot;, rs.Fields(1).Name, _ &quot;&quot;, rs.Fields(2).Name, rs.Fields(3).Name Do While Not rs.EOF Debug.Print rs.Fields(0), rs.Fields(1), _ rs.Fields(2), rs.Fields(3) rs.MoveNext Loop cn.Close Exit Sub

ErrHandler:

For j = 0 To cn.Errors.Count - 1 Debug.Print &quot;Conn Err Num : &quot;; cn.Errors(j).Number Debug.Print &quot;Conn Err Desc: &quot;; cn.Errors(j).Description Next j   For j = 0 To cn2.Errors.Count - 1 Debug.Print &quot;Conn Err Num : &quot;; cn2.Errors(j).Number Debug.Print &quot;Conn Err Desc: &quot;; cn2.Errors(j).Description Next j   For j = 0 To cn3.Errors.Count - 1 Debug.Print &quot;Conn Err Num : &quot;; cn3.Errors(j).Number Debug.Print &quot;Conn Err Desc: &quot;; cn3.Errors(j).Description Next j   Resume Next

End Sub

 Close the Northwind database and when you are prompted, save changes to ShutDownDB. (This must be done to release an exclusive lock on the database due to the code you have just added.)</li> Reopen C:\Northwind.mdb.</li> Press CTRL+G to open the Immediate Window in the Visual Basic Editor.</li>  Type the following line in the Immediate window, and then press ENTER:

ShowUserRosterAndPassiveShutdown </li></ol>

Note the list of database users displayed in the Immediate window.

back to the top

<div class="references_section">