Many database maintenance activities require
that the administrator have exclusive access to the database. The database
cannot be opened exclusively if other people already have the database open.
With DAO, the administrator had no way of determining who was logged in to the
database, making it difficult to determine who was blocking the administrator's
attempt to open the database exclusively.
ADO and the Microsoft Jet Provider expose a
schema rowset that contains information about who currently has the database
open. This is a provider specific schema rowset named
DBSCHEMA_JETOLEDB_USERROSTER. The following code demonstrates how to open this
schema rowset using ADO.
Sub ADOUserRoster()
Dim cnn As New ADODB.Connection
Dim rst As ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Open the user roster schema rowset
Set rst = cnn.OpenSchema(adSchemaProviderSpecific, , _
JET_SCHEMA_USERROSTER)
' Print the results to the debug window
Debug.Print rst.GetString
cnn.Close
End Sub
The first parameter, QueryType,
to the ADO OpenSchema method takes an enumeration
value. Values are defined for the schema rowsets defined in the OLE DB
specification. To use a provider-specific schema rowset such as
DBSCHEMA_JETOLEDB_USERROSTER, you must specify adProviderSpecific
and then provide the GUID for the schema rowset as the last parameter.
In this example, the constant JET_SCHEMA_USERROSTER is used in place of the
GUID. This constant is contained in the JetOLEDBConstants.txt file included in
Appendix C.
The following table describes the information
contained in each column of the schema rowset.
|
Column
|
Description
|
|
COMPUTER_NAME
|
The name of the workstation as specified using
the network icon in the control panel.
|
|
LOGIN_NAME
|
The name of the user used to log into the
database if the database has been secured. Otherwise the default value will
be Admin.
|
|
CONNECTED
|
True if there is a corresponding user lock in
the LDB file.
|
|
SUSPECTED_STATE
|
True if the user has left the database in a
suspect state; otherwise Null.
|
EmoticonEmoticon