Users and Groups Listing from Workgroup Information File ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

How about taking a printout of Users and Groups from the active Workgroup Information File (the Microsoft Access Security File)?

This applies only to users of Microsoft Access 2003 or earlier databases that were implemented with Access Security. Such databases can still be opened and used in Access 2007 or Access 2010 without conversion.

To link to the Workgroup Information File (.mdw) from Access 2007, you can run the Workgroup Administrator program. For step-by-step instructions, please refer to the article: Running Workgroup Admin Program from Access 2007.

On a personal note, I am not particularly fond of the frequent version changes in Access. While new features are always exciting to explore, they often come at the cost of what we have already learned and implemented. For example, I still wonder why the familiar Menus and Toolbars were reorganized and rebranded as the Ribbon in Access 2007. Such changes often add confusion and waste time, forcing users to relearn tasks they could once do quickly. By the time users finally become comfortable with the new interface, a newer version appears—and the cycle starts all over again.

Upgrades are always welcome—provided they correct the drawbacks and bugs of earlier versions and implement genuine enhancements that users can easily locate and benefit from.

Coming back to the topic of printing Users and Groups from the active Workgroup Information File, Microsoft Access already provides a built-in option. The only limitation is that the listing is sent directly to the printer, which may not be ideal.

To use this option in Access 2003 or earlier:

Select Tools → Security → Users and Group Accounts → Users → Print Users and Groups.

In Access 2007 (with an Access 2003 or earlier database open):

Go to Database Tools → Administer → Users and Permissions → User and Group Accounts → Users → Print Users and Groups.

But why waste stationery when there’s a more flexible option? You can generate a list of Users and Groups and save it to a text file on your disk instead. Below is a simple VBA procedure you can use for this purpose. Copy and paste the following code into a Standard Module, then save and run it:

Database UsersList() Function.

Public Function UsersList() 
'------------------------------------------------------------- 
'Author : a.p.r. pillai 
'Date   : Oct 2011 
'Rights : All Rights Reserved by www.msaccesstips.com 
'Remarks: Creates a list of Users & UserGroups from the 
'       : active Workgroup Information File and saves 
'       : the list into a text file: UserGroup.txt 
'       : in the current database path 
'------------------------------------------------------------- 
Dim wsp As Workspace, grp As Group, usr As User 
Dim fs As Object, cp_path As String 
Dim a, txt 
Const ten As Integer = 10 

cp_path = CurrentProject.Path 
Set wsp = DBEngine.Workspaces(0) 
'Create a Text file: UserGroup.txt with FileSystemObject 
Set fs = CreateObject("Scripting.FileSystemObject") 
Set a = fs.CreateTextFile(cp_path & "\UserGroup.txt", True) 

'Write headings 
a.writeline "SYSTEM-DEFAULT GROUPS" 
a.writeline "-----------------------" 
a.writeline ("User-Groups  User-Names") 
a.writeline ("-----------  ----------") 

'List Default Admins & Users Group First 
For Each grp In wsp.Groups 
   txt = grp.Name 
   If txt = "Admins" Or txt = "Users" Then 
     a.writeline txt & Space(ten - Len(grp.Name)) & Space(3) & "..." 
     For Each usr In grp.Users 
        txt = Space(Len(grp.Name) + (ten - Len(grp.Name))) & Space(3) & usr.Name 
        a.writeline txt 
     Next: a.writeline crlf 
   End If 
Next 

'Groups, except Default Admins & Users 
a.writeline "ADMINISTRATOR-DEFINED GROUPS" 
a.writeline "----------------------------" 
a.writeline ("User-Groups  User-Names") 
a.writeline ("-----------  ----------") 

For Each grp In wsp.Groups 
   txt = grp.Name 
   If txt = "Admins" Or txt = "Users" Then 
      GoTo nextitem 
   Else 
      a.writeline txt & Space(ten - Len(grp.Name)) & Space(3) & "..." 
      For Each usr In grp.Users 
         txt = Space(Len(grp.Name) + (ten - Len(grp.Name))) & Space(3) & usr.Name 
         a.writeline txt 
      Next: a.writeline crlf 
   End If 
nextitem: 
Next 
a.Close 
'Open UserGroup.txt file with the list User list. 
Call Shell("Notepad.exe " & CurrentProject.Path & "\UserGroup.txt", vbNormalFocus) 

End Function

Running UsersList() Function

You can run this program either directly from the Immediate Window (Debug Window) or by calling it from the Click event of a Command Button on a Form.

When executed, the program will create a text file named UserGroup.txt in the same folder where the current database resides. Once the file is generated, it will automatically open in Windows Notepad for review.

Keep in mind that each time you run this program, the existing file will be overwritten. If you would like to preserve previous listings, remember to rename or move the file to a safe location before running the procedure again.

The result of a sample run of the program is given below: