For this Tip, we will show you an extremely handy, multi-user, feature of Jet that allows you to manage Users more effectively. You can create a special, provider-specific Recordset in ADO that supplies information about the current Users in the Database. This is accomplished by using the OpenSchema() Method of the Connection Object, which will fill a recordset with varied sets of different Database Schema information. The output of this OpenSchema() Method will be written to a List Box (lstUsers) with the pre-defined headings of Computer, UserName, Connected?, and Suspect?.
I intentionally decided to display this Tip at this specific point in time because it is intimately related to the prior Tip, Passive Shutdown. They are both extremely helpful, multi-user, features that Jet 4 exposes, and they will greatly assist in the implementation of certain administrative tasks such as: Updates, Backups, etc. There will be certain instances when you, as the administrator of a multi-user Database, will want to be 100% sure that no Users are currently logged on to your back end database. Through the use of Passive Shutdown, when no additional Users will be allowed to log on, and the User List, which will display any currently logged on Users, you will be 100% guaranteed that no currently logged on individual will interfere with a critical process.
The code segment below will demonstrate how you can display this User List in a List Box on a Form. There are ample comments scattered through the code, but if you need any further explanation on any specific area, please feel free to ask.
[CODE=vb]
'The User List Schema information requires this magic number. For anyone
'who may be interested, this number is called a GUID or Globally Unique
'Identifier - sorry for digressing
Const conUsers = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Dim cnn As ADODB.Connectio n, fld As ADODB.Field, strUser As String
Dim rst As ADODB.Recordset , intUser As Integer, varValue As Variant
Set cnn = CurrentProject. Connection
Set rst = cnn.OpenSchema( Schema:=adSchem aProviderSpecif ic, SchemaID:=conUs ers)
'Set List Box Heading
strUser = "Computer;UserN ame;Connected?; Suspect?"
With rst 'fills Recordset (rst) with User List data
Do Until .EOF
intUser = intUser + 1
For Each fld In .Fields
varValue = fld.Value
'Some of the return values are Null-Terminated Strings, if
'so strip them off
If InStr(varValue, vbNullChar) > 0 Then
varValue = Left(varValue, InStr(varValue, vbNullChar) - 1)
End If
strUser = strUser & ";" & varValue
Next
.MoveNext
Loop
End With
Me!txtTotalNumO fUsers = intUser 'Total # of Users
'Set up List Box Parameters
Me!lstUsers.Col umnCount = 4
Me!lstUsers.Row SourceType = "Value List"
Me!lstUsers.Col umnHeads = False
lstUsers.RowSou rce = strUser 'populate the List Box
'Routine cleanup chores
Set fld = Nothing
Set rst = Nothing
Set cnn = Nothing[/CODE]
OUTPUT:
[CODE=text]
Computer UserName Connected? Suspect?
DEZII Admin True
IGUANA Suzanne True
LIZARD Paul True
FINANCE Leonard True[/CODE]
I intentionally decided to display this Tip at this specific point in time because it is intimately related to the prior Tip, Passive Shutdown. They are both extremely helpful, multi-user, features that Jet 4 exposes, and they will greatly assist in the implementation of certain administrative tasks such as: Updates, Backups, etc. There will be certain instances when you, as the administrator of a multi-user Database, will want to be 100% sure that no Users are currently logged on to your back end database. Through the use of Passive Shutdown, when no additional Users will be allowed to log on, and the User List, which will display any currently logged on Users, you will be 100% guaranteed that no currently logged on individual will interfere with a critical process.
The code segment below will demonstrate how you can display this User List in a List Box on a Form. There are ample comments scattered through the code, but if you need any further explanation on any specific area, please feel free to ask.
[CODE=vb]
'The User List Schema information requires this magic number. For anyone
'who may be interested, this number is called a GUID or Globally Unique
'Identifier - sorry for digressing
Const conUsers = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Dim cnn As ADODB.Connectio n, fld As ADODB.Field, strUser As String
Dim rst As ADODB.Recordset , intUser As Integer, varValue As Variant
Set cnn = CurrentProject. Connection
Set rst = cnn.OpenSchema( Schema:=adSchem aProviderSpecif ic, SchemaID:=conUs ers)
'Set List Box Heading
strUser = "Computer;UserN ame;Connected?; Suspect?"
With rst 'fills Recordset (rst) with User List data
Do Until .EOF
intUser = intUser + 1
For Each fld In .Fields
varValue = fld.Value
'Some of the return values are Null-Terminated Strings, if
'so strip them off
If InStr(varValue, vbNullChar) > 0 Then
varValue = Left(varValue, InStr(varValue, vbNullChar) - 1)
End If
strUser = strUser & ";" & varValue
Next
.MoveNext
Loop
End With
Me!txtTotalNumO fUsers = intUser 'Total # of Users
'Set up List Box Parameters
Me!lstUsers.Col umnCount = 4
Me!lstUsers.Row SourceType = "Value List"
Me!lstUsers.Col umnHeads = False
lstUsers.RowSou rce = strUser 'populate the List Box
'Routine cleanup chores
Set fld = Nothing
Set rst = Nothing
Set cnn = Nothing[/CODE]
OUTPUT:
[CODE=text]
Computer UserName Connected? Suspect?
DEZII Admin True
IGUANA Suzanne True
LIZARD Paul True
FINANCE Leonard True[/CODE]
Comment