Solved

How to change RunPermissions via code

Posted on 2006-10-26
10
269 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
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:yhwhlivesinme
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility

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

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

Author Comment

by:Milewskp
Comment Utility
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
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
you have to modify the Sql of the queryDef

do you need the codes?
0
 
LVL 1

Author Comment

by:Milewskp
Comment Utility
please
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
do you want to delete or add  WITH OWNERACCESS OPTION
0
 
LVL 1

Author Comment

by:Milewskp
Comment Utility
add
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility


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
Comment Utility
Thanks cap!
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
U R Welcome!!!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

744 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

15 Experts available now in Live!

Get 1:1 Help Now