Set Open/Run permissions for reports and queries using VBA

Posted on 2008-11-03
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?

Question by:shambalad
    LVL 77

    Expert Comment

    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.
    LVL 84

    Accepted Solution

    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:


    For forms and Reports:


    For databases and other:


    These all came from the above referenced book.

    LVL 7

    Author Comment

    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.
    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.
    LVL 77

    Assisted Solution

    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.
    LVL 7

    Author Closing Comment

    As someone who has no one else around who knows Access, you guys are my safety net. I greatly appreciate your time and efforts.
    LVL 7

    Author Comment

    As it is, I'm really going with Peter's suggestion. I had meant to acknowledge LSM with an assist.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    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.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now