Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 10.5 Programmatically Track Users and Groups

10.5.1 Problem

As the database
administrator, you want to be able to track users and their groups
within your workgroup. How can you gather the information you need?

10.5.2 Solution

Using Data Access Objects (DAO), you can
retrieve all the information you need about users'
names and groups. Once you have that information, you can use it in
creating your applications.

The sample form frmUserGroups in

10-05.MDB fills
tables with the information you need and presents it to you in a list
box. To test it, open and run frmUserGroups. Figure 10-20 shows the form in use for a sample workgroup.

Figure 10-20. frmUserGroups shows users and groups for a sample workgroup

To gather this information in your own applications, follow these
steps:

1. Create the tables you'll need to hold the
   information. Either import the three tables from
   

   10-05.MDB , or use the information in Table 10-13 to create your own.

2. If you created your own tables in
   Step 1, you'll need to add an index to tblGroups. In
   the Indexes properties sheet (available by choosing View
   Indexes when tblGroups is open in design mode), add a row as
   described in Table 10-14 for the index properties.
   Table 10-14 also shows the primary key row that
   should already exist in the Indexes properties sheet.

3. Either import the module basListUsers from
   

   10-05.MDB , or enter the following code into a
   global module. This is the code you'll use to fill
   the three tables you just created:

   Public Sub acbListUsers( )
   ' Create tables containing all
   ' the users and groups in the current
   ' workgroup.
   '
   ' The results will be in:
   '    tblUsers, tblGroups and
   '    tblUserGroups.
   ' Run qryUserGroups to see sorted list.
   Dim db As DAO.Database
   Dim wrk As DAO.Workspace
   Dim rstUsers As DAO.Recordset
   Dim rstGroups As DAO.Recordset
   Dim rstUserGroups As DAO.Recordset
   Dim usr As User
   Dim intI As Integer
   Dim intJ As Integer
   ' Set up object variables.
   Set wrk = DBEngine.Workspaces(0)
   Set db = wrk.Databases(0)
   Set rstUsers = db.OpenRecordset("tblUsers")
   Set rstGroups = db.OpenRecordset("tblGroups")
   Set rstUserGroups = db.OpenRecordset("tblUserGroups")
   ' Refresh the Users and Groups collections
   ' so we see any recently added members
   wrk.Users.Refresh
   wrk.Groups.Refresh
   ' Clear out the old values
   db.Execute "DELETE * FROM tblUserGroups"
   db.Execute "DELETE * FROM tblUsers"
   db.Execute "DELETE * FROM tblGroups"
   ' Build up a list of all the groups in tblGroups
   For intI = 0 To wrk.Groups.Count - 1
   rstGroups.AddNew
   rstGroups("Group") = wrk.Groups(intI).Name
   rstGroups.Update
   Next intI
   ' Loop through all the users, adding
   ' rows to tblUsers and tblUserGroups.
   For intI = 0 To wrk.Users.Count - 1
   ' Add a user to tblUsers.
   Set usr = wrk.Users(intI)
   rstUsers.AddNew
   rstUsers("UserName") = usr.Name
   rstUsers.Update
   rstUsers.Move 0, rstUsers.LastModified
   ' Now loop through all the groups
   ' that user belongs to, hooking up the rows
   ' in tblUserGroups.
   For intJ = 0 To usr.Groups.Count - 1
   rstGroups.Index = "Group"
   rstGroups.Seek "=", usr.Groups(intJ).Name
   If Not rstUserGroups.NoMatch Then
   rstUserGroups.AddNew
   rstUserGroups("UserID") = rstUsers("UserID")
   rstUserGroups("GroupID") = rstGroups("GroupID")
   rstUserGroups.Update
   End If
   Next intJ
   Next intI
   rstUsers.Close
   rstGroups.Close
   rstUserGroups.Close
   End Sub

4. Either import the query qryUserGroups from
   

   10-05.MDB , or create a new query, as follows.
   When Access asks you to add a table, just close the dialog. In design
   mode, click on the SQL button on the toolbar and enter the following
   expression:

   SELECT tblUsers.UserName, tblGroups.Group
   FROM tblUsers INNER JOIN (tblGroups INNER JOIN tblUserGroups 
   ON tblGroups.GroupID = tblUserGroups.GroupID) 
   ON tblUsers.UserID = tblUserGroups.UserID
   ORDER BY tblUsers.UserName, tblGroups.Group;

   Then save the query as qryUserGroups.

5. To produce the current list of users and groups, execute the code in
   

   acbListUsers . You can call it directly, use a
   button whose Click event calls the procedure, or call it from the
   debug window. (The sample form calls
   

   acbListUsers from the Click event of the
   cmdRequery button on the form.) Once you've executed
   that code, you'll have filled in the three tables.
   You can use qryUserGroups to retrieve the information you need, or
   create your own queries based on the three tables.

10.5.3 Discussion

This solution relies on the DAO
object model to gather its information. The DBEngine object is at the
root (the highest level) of the DAO object hierarchy, and it has a
single collection, the Workspaces collection. Each workspace
represents a session of the Access database engine (and unless
you're writing sophisticated applications,
you'll most likely never see more than a single
concurrent workspace). The default workspace contains information
about the collection of open databases (only one is open in the user
interfaceall others must be opened via VBA code) along with
the available user and group collections. These are the collections
you'll need for filling tables with the usernames
and their groups. The code in the

acbListUsers
subroutine does all the work.

The

acbListUsers function starts out by setting up
object variables to refer to several recordset objects, and refreshes
the Users and Groups collections of the workspace. This is necessary
to make sure we see any recent changes to these collections made via
the Access user interface or by another Access session. The relevant
code is:

' Set up object variables.
Set wrk = DBEngine.Workspaces(0)
Set db = wrk.Databases(0)
Set rstUsers = db.OpenRecordset("tblUsers")
Set rstGroups = db.OpenRecordset("tblGroups")
Set rstUserGroups = db.OpenRecordset("tblUserGroups")
' Refresh the Users and Groups collections
' so we see any recently added members
wrk.Users.Refresh
wrk.Groups.Refresh

The next step entails deleting all
the existing rows in the three tables, using the Execute method of
the database object:

' Clear out the old values
db.Execute "DELETE * FROM tblUserGroups"
db.Execute "DELETE * FROM tblUsers"
db.Execute "DELETE * FROM tblGroups"

Once these lines of code have executed, the three tables will be
empty.

The next step is to build up a
list of all the groups. This is accomplished by looping through all
the elements of the workspace's Groups collection.
Just like all other collections in Access, the Groups collection
provides a Count property indicating how many elements it contains.
These items are numbered from 0 through Count-1, and we loop through
them all, adding a row to tblGroups for each group in the collection:

' Build up a list of all the groups in tblGroups
For intI = 0 To wrk.Groups.Count - 1
rstGroups.AddNew
rstGroups("Group") = wrk.Groups(intI).Name
rstGroups.Update
Next intI

Once tblGroups is filled in, we do the same for users. Just as the
workspace contains a collection of groups, it also contains a
collection of users. We can walk through the Users collection, adding
a row at a time to tblUsers, as shown here:

    ' Loop through all the users, adding
' rows to tblUsers and tblUserGroups.
For intI = 0 To wrk.Users.Count - 1
' Add a user to tblUsers.
Set usr = wrk.Users(intI)
rstUsers.AddNew
rstUsers("UserName") = usr.Name
rstUsers.Update
rstUsers.Move 0, rstUsers.LastModified
' See the next code example...
Next intI

Once a user is added, rows are added to tblUserGroups for each group
that contains the current user. This is accomplished by enumerating
through the Groups collection for the current user. (Note that there
was a choice here. Each member of the workspace's
Users collection has its own Groups collection, listing the groups to
which it belongs, and each member of the workspace's
Groups collection has its own Users collection, listing the members
of the group. The code can either walk through the users, looking at
the Groups collection in each, or walk through the groups, looking at
the Users collection in each. This example walks through the
workspace's Users collection, one at a time,
studying the Groups collection in each one.) The following code loops
through every item in the user's Groups collection,
finding the matching name in tblGroups, and then adding a row to
tblUserGroups containing both the user's UserID
field (from tblUsers) and the GroupID field (from tblGroups). This
way, tblUserGroups contains a single row for every user/group pair.
The code is:

' Now loop through all the groups
' that user belongs to, hooking up the rows
' in tblUserGroups.
For intJ = 0 To usr.Groups.Count - 1
rstGroups.Index = "Group"
rstGroups.Seek "=", usr.Groups(intJ).Name
If Not rstUserGroups.NoMatch Then
rstUserGroups.AddNew
rstUserGroups("UserID") = rstUsers("UserID")
rstUserGroups("GroupID") = rstGroups("GroupID")
rstUserGroups.Update
End If
Next intJ

Once the code has looped through all the users and all the groups to
which each user belongs, it closes all the objects:

rstUsers.Close
rstGroups.Close
rstUserGroups.Close

Now tblUsers, tblGroups, and tblUserGroups contain information about
each user and the groups to which he or she belongs.

Once you've filled the three tables, you can easily
perform lookups in your VBA code or create reports displaying
security settings. You could also just lift pieces of the code from

acbListUsers for use in your own applications.
The next solution shows a simpler function,

acbAmMemberOfGroup , which uses a similar
technique to query on the fly if the current user is a member of a
specific group.

The

acbListUsers procedure is not production-quality
code. To keep it simple, we left out the error-handling code, and any
procedure of this nature that manipulates tables must include
sufficient error-handling capabilities. Though it's
not likely, some other user may have locked the output tables or,
worse, deleted them, or you may not have permissions for the system
tables you need in order to gather this information. In a production
environment, it's best to trap errors and handle
them.

In the list of users
found in tblUsers, notice that there are two users that you might not
have seen before: Creator and Engine. These two users are created by
the Jet engine itself and cannot be used or manipulated by VBA code.
As you'll see in the Solution in Recipe 10.7, you can create a Workspace object for any
normal user, allowing that user to log into a new session of the Jet
engine, but you can't use Creator or Engine to
create new workspace objects. It's a good thing,
too! Since neither can have a password (their passwords are always
blank), this would otherwise provide a security breach. Because you
can neither log on manually nor log on using the CreateWorkspace
method with either user, these two special users
don't pose a security risk.

Once you know how to enumerate through collections, as shown in this
solution, you should be able to apply the same techniques to other
database collections and their objects. For more information, see
Chapter 4.