Solved

How to change RunPermissions via code

Posted on 2006-10-26
10
279 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
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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

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

Author Comment

by:Milewskp
ID: 17815743
add
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 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 119

Expert Comment

by:Rey Obrero
ID: 17815869
U R Welcome!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

948 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

20 Experts available now in Live!

Get 1:1 Help Now