One more feature that's worthwhile in ADO is the capability to see a list of users who are logged in to a database. This is useful only if you're using Access security.
ADO and Jet let you list logged-in users through a method of the OpenSchema connection object. You then pass a GUID that tells ADO you want to see the Jet schema roster. You can see this in Listing 22.17 by calling ap_ListUsers.
Option Compare Database
Public Const JET_SCHEMA_USERROSTER = _
"{947bb102-5d43-11d1-bdbf-00c04fb92675} "
Function ap_ListUsers() As String
Dim cnnCurr As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Dim strName As String
' Open the connection
cnnCurr.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
CurrentProject.Path & "Chap22BE(ADO).mdb;"
' Open the user roster schema rowset
rstCurr.Open cnnCurr.OpenSchema(adSchemaProviderSpecific, , _
JET_SCHEMA_USERROSTER)
' Print the results to the debug window
With rstCurr
.MoveFirst
Do While Not .EOF
If rstCurr.Fields("CONNECTED").Value = True Then
strName = rstCurr.Fields("LOGIN_NAME").Value
strName = ap_TrimNumChar(strName)
Debug.Print strName
End If
.MoveNext
Loop
End With
rstCurr.Close
cnnCurr.Close
End Function
Public Function ap_TrimNumChar(strName As String) As String
Dim intLoc As Integer
intLoc = InStr(strName, Chr(0))
If intLoc > 0 Then
ap_TrimNumChar = Left$(strName, intLoc - 1)
End If
End Function
This code takes the roster and dumps it to the Immediate window. You can see this in action in Figure 22.22.
Figure 22.22. If you're not using Access security, you will show up as Admin in the user list.
You can see in Figure 22.22 that Scott has logged in to the backend, as well as Admin. Admin is actually the user who is running the routine to print out the names (in this case, you).
If the applications are using ADO and have multiple connections to the back end, you might see the name repeated and have to program for it accordingly.