Set Open/Run permissions for reports and queries using VBA

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

LVL 7
shambaladAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shambaladAuthor Commented:
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

peter57rCommented:
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
shambaladAuthor Commented:
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
shambaladAuthor Commented:
As it is, I'm really going with Peter's suggestion. I had meant to acknowledge LSM with an assist.
Thanks,
Todd
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.