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

Tuesday, October 25, 2011

Users and Groups Listing from Workgroup Information File

Introduction

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

This is only for users of Microsoft Access 2003 or earlier version databases implemented with Microsoft Access Security. You can use earlier version databases in Access2007 or in Access 2010 without converting them.

You can run the Workgroup Administrator program from Access2007 to link to the Workgroup Information File (.mdw). Please refer to the Article: Running Workgroup Admin Program from Access2007 for details.

Frankly speaking, I am not happy with the idea of frequent changes in versions, irrespective of what advantages they provide. It is true that I am really excited to learn new features, but not at the cost of what we have already learned and implemented. I still wonder why the Menus and Toolbars are jumbled around and presented with fancy names like Ribbons in Access2007. These kinds of changes will only add to the confusion and waste of time to find out things we are already familiar with in the earlier versions. By the time users are out of the woods with the new changes, they are slapped with a new version and start all over again looking for things, which were in easy reach earlier.

Upgrades are good if drawbacks and bugs of earlier Versions are corrected and implemented with real enhancements, which users can find them easily.

Coming back to the topic of taking print out of Users and Groups, there is an option already available in Access. The only problem is that the listing will be dumped directly into the Printer.

To use this option select Tools - - > Security - - > Users and Group Accounts - - > Users - - > Print Users and Groups.

In Microsoft Access2007, open a database of Access2003 or earlier version first, then select Database Tools - - > Administer - - > Users and Permissions - - > User and Group Accounts . . . - - > Users - - > Print Users and Groups.

But, why you should waste stationery when you have a better option? You can create a list of Users and Groups in a Text file and save it on your disk with the VBA Program given below. Copy and paste the following Code into a Standard Module and save 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 the Code directly from the Debug Window or from a Command Button Click on a Form. The Text File will be created in the current database folder with the name UserGroup.txt and it will be opened in Windows Notepad automatically.

Whenever you run this program the earlier file will be over-written. You may rename the file, for the safe-keep.

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

If you need a printout of Users and Groups then you may print it from the text file.

LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, October 25, 2011