Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to change RunPermissions via code

Posted on 2006-10-26
10
Medium Priority
?
322 Views
Last Modified: 2012-06-27
How do I change the RunPermissions of all exiting and new queries in the current database to 'Owner' using VBA code?
0
Comment
Question by:Milewskp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17814903
In your query design you should just put the statement:

With Owneraccess

that will make the query run with the owner's access rights
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17814933

Application.SetOption ("Run Permissions"), 0     'owner

Application.SetOption ("Run Permissions"), 1     'user
0
 
LVL 1

Author Comment

by:Milewskp
ID: 17815439
Thanks cap, that explains how to set the run permissions for new queries, but is there a way to set the run permissions of the existing queries (other than setting the SQL string for each qeury)?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17815471
you have to modify the Sql of the queryDef

do you need the codes?
0
 
LVL 1

Author Comment

by:Milewskp
ID: 17815674
please
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17815730
do you want to delete or add  WITH OWNERACCESS OPTION
0
 
LVL 1

Author Comment

by:Milewskp
ID: 17815743
add
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 17815790


Function AlterQDef(strPerm As String)
Dim qd As QueryDef, sSql As String
For Each qd In CurrentDb.QueryDefs
    If InStr(qd.Name, "~") = 0 Then
        If InStr(qd.sql, strPerm) = 0 Then
            sSql = Replace(qd.sql, ";", "")
            sSql = sSql & " " & strPerm
            qd.sql = sSql
        End If
    End If
Next
End Function

to use
AlterQDef("WITH OWNERACCESS OPTION")



0
 
LVL 1

Author Comment

by:Milewskp
ID: 17815856
Thanks cap!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 17815869
U R Welcome!!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

722 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