Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

How to change RunPermissions via code

How do I change the RunPermissions of all exiting and new queries in the current database to 'Owner' using VBA code?
0
Milewskp
Asked:
Milewskp
  • 5
  • 4
1 Solution
 
yhwhlivesinmeCommented:
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
 
Rey Obrero (Capricorn1)Commented:

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

Application.SetOption ("Run Permissions"), 1     'user
0
 
MilewskpAuthor Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Rey Obrero (Capricorn1)Commented:
you have to modify the Sql of the queryDef

do you need the codes?
0
 
MilewskpAuthor Commented:
please
0
 
Rey Obrero (Capricorn1)Commented:
do you want to delete or add  WITH OWNERACCESS OPTION
0
 
MilewskpAuthor Commented:
add
0
 
Rey Obrero (Capricorn1)Commented:


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
 
MilewskpAuthor Commented:
Thanks cap!
0
 
Rey Obrero (Capricorn1)Commented:
U R Welcome!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now