ACC2000: Use Connection Control to Prevent User Log On at Run Time

• Microsoft Access 2000

SUMMARY

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

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 as well how to output a list of users who are already logged on to the database.

MORE INFORMATION

The Connection Control

• 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 1 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.

The User List

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

Code Example

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Copy the Northwind example database to the C:\ directory.



2. In Microsoft Access, open C:\Northwind.



3. Create a new module called ShutDownDB.



4. On the Tools menu, click References.



5. In the References dialog box, click to select the following reference:




Microsoft ActiveX Data Objects 2.1 Library

6. 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 i, j As Long

    On Error GoTo ErrHandler
    
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=c:\Northwind.mdb"
    
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=c:\Northwind.mdb"
    
    ' Restrict other users from opening the database
    cn.Properties("Jet OLEDB:Connection Control") = 1
    
    ' Attempt to open another connection to the database
    cn3.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=c:\Northwind.mdb"
    
    ' 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, , _
    "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    
    ' Output the list of all users in the current database.
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", 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, , _
    "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", 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 "Conn Err Num : "; cn.Errors(j).Number
       Debug.Print "Conn Err Desc: "; cn.Errors(j).Description
    Next j
    
    For j = 0 To cn2.Errors.Count - 1
       Debug.Print "Conn Err Num : "; cn2.Errors(j).Number
       Debug.Print "Conn Err Desc: "; cn2.Errors(j).Description
    Next j
    
    For j = 0 To cn3.Errors.Count - 1
       Debug.Print "Conn Err Num : "; cn3.Errors(j).Number
       Debug.Print "Conn Err Desc: "; cn3.Errors(j).Description
    Next j
    
    Resume Next

End Sub 



7. Close the Northwind database and when 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.)



8. Re-open C:\Northwind.mdb.



9. Press CTRL+G to bring up the Immediate Window in the Visual Basic Editor.



10. Type the following in the Immediate window and press ENTER:





ShowUserRosterAndPassiveShutdown 

Additional query words: inf

Keywords          : kbdta AccCon KbVBA 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 12, 1999