Access Cookbook - Ken Getz [214]
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.