VBA – Write Current MS Access Permissions to MS Excel

[rjbudz]

I wanted to make a contribution since joining, and finally came up with something worth posting.

The following code will obtain all of the permissions currently in effect for the objects in a Microsoft Access database, create a nicely formatted (if I must say so myself) Microsoft Excel workbook and write the permissions to the workbook.

The user has the option of including as many database objects as needed (modules, forms, reports, etc.) and the option of extracting permissions for users and/or groups pertaining to those objects.

A summary worksheet can be included if desired to display the path and name of the database and workgroup file, a breakdown of the number of objects, and number of users and groups verified. Also included is the number of total permissions verified.

Depending on the size of your database and workgroup and the computer running it, this app may take quite a while to run. I’ve included liberal DoEvents and debug.Print statements so it’s easy to monitor progress.

Sample (actual) output in Immediate Window:

Starting PermissionsToExcelApp ...

Getting Names of Macros ...
Getting Names of Modules ...
Getting Names of Forms ...
Getting Names of Queries ...
Getting Names of Reports ...
Getting Names of Tables ...
Getting Names of Users ...
Getting Names of Groups ...

Creating Excel Workbook for data output ...
Deleting existing Excel workbook: C:\Generic.xls
Creating new Workbook: C:\Generic.xls ...
Excel Workbook successfully created.

Getting Group Macros Permissions ...
Getting Group Modules Permissions ...
Getting Group Forms Permissions ...
Getting Group Reports Permissions ...
Getting Group Tables Permissions ...
Getting Group Queries Permissions ...

Getting User Macros Permissions ...
Getting User Modules Permissions ...
Getting User Forms Permissions ...
Getting User Reports Permissions ...
Getting User Tables Permissions ...
Getting User Queries Permissions ...

Writing Users to Group worksheet ...
Writing Users/Group associations to Group worksheet ...

Writing Summary Data to worksheet ...

-- Results ----------------------------------------------------------------

Examined Objects in Database
- Macros: 55
- Modules: 24
- Forms: 43
- Queries: 99
- Reports: 26
- Tables: 5

Number of permission objects in current workgroup
- Groups: 8
- Users: 102

Number of permissions verified: 498,960

---------------------------------------------------------------------------


Saving Workbook ...
Successful save to C:\Generic.xls

Destroying Objects ...
Application run complete.