?
Solved

Set Open/Run permissions for reports and queries using VBA

Posted on 2008-11-03
6
Medium Priority
?
405 Views
Last Modified: 2012-06-27
I have some large databases with many users. All of the users are members of a group named '2FullData'. I need to set Open/Run permissions for this group for all of the reports and queries. I need to do this using DAO (ADOX is problematic in my environment). There are an enormous amount of queries and reports in these databases. Doing it manually might take me the rest of the week.
I've been looking at setting permissions for documents in containers, but I don't see a constant for 'open/run'. Also I would need to set a 'user name' for the document before setting the permission; I don't know if a goup would work for that.
Even if containers were to work for reports, I don't see a container for queries, so I'm only half way there at best.
Does anyone have a solution for granting a group Op/run permissions for reports and queries?
Thanks,
Todd

0
Comment
Question by:shambalad
  • 3
  • 2
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22874330
How many databases have you got?

Using the security menu, it takes about 5 seconds to set open/run permissions for one group for all reports in a database.

And even less than that for queries !! (:-) because you can't set the open/open run permissions for queries and tables.
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 22875389
I agree with Peter - this might take an hour or so, but it'd take that much time (or much longer) to build a VBA utility to do this.

That said, you can certainly apply permissions to Groups, and you don't need to "assign" a user to a particular object. Security constants are hard to find; the only definitive source I've found is in the Microsoft Jet Database Engine Programmer's Guide (Second Edition), from Microsoft Press. This is out of print, but there are plenty of copies available on the big book websites.

The constants you might need for "open/run" for queries/tables would be:

dbSecReadDef
dbSecRetrieveData
dbSecDeleteData
dbSecReplaceData
dbSecInsertData

For forms and Reports:

acSecFrmRptExecute
acSecFrmRptReadDef
acSecFrmRptWriteDef

For databases and other:

dbSecDBOpen
dbSecReadSec
dbSecWriteSec

These all came from the above referenced book.

0
 
LVL 7

Author Comment

by:shambalad
ID: 22885604
Good morning -
First, please accept my apologies for not responding to this yesterday; I literally spent over 12 hours in my car yesterday driving between poll sites for the elections (US). But I'm back to the office today.
I am dealing with more than 10 databases that were written a while back by someone who was clever but had no inkling of programming standards. There are hundreds of queries of queries in many of these databases, and lots of reports too. There are about 500 users defined in the MDW, although most are obsolete, and there are usually no more than about 10 users in any database at a time.
All of these things will be cleaned up in the future. Right now, the first phase of this project is to split and secure the databases.
Anyway...
If there is a simple way to manually give a group permissions to run all reports and all queries, I'm all for it. It appeared to me that I would have to grant the permissions for each object individually. I've got some routines that I have set up to loop through the objects, but I can already tell they are not going to be as simple as I had hoped.
Thanks,
Todd
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 1000 total points
ID: 22885902
Tools>Security>User & Group Permissions
Select group from left panel.
Select Reports from Object Typedrop down.
Click New Reports in right panel.
Scroll to last report and Shift+Click on last report.
Select Open/Run
Click OK.
0
 
LVL 7

Author Closing Comment

by:shambalad
ID: 31512835
As someone who has no one else around who knows Access, you guys are my safety net. I greatly appreciate your time and efforts.
Todd
0
 
LVL 7

Author Comment

by:shambalad
ID: 22886201
As it is, I'm really going with Peter's suggestion. I had meant to acknowledge LSM with an assist.
Thanks,
Todd
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question