Change Run Permissions property

[Tom]

Is there a simple way for me to change the Run Permissions property from the
default "user's" to owners?
I have tried running a few routines to view the property so I can change it,
but when the properties return that one is not in the list for some reason.
Thanks in advance.
TK

 

[Scott McDaniel]

Are you talking about changing the Run perms of a query?

 

[Tom]

Yes - sorry about forgetting that little detail
Tom

 

[Tom]

I forgot to mention - I want to do it on all the queries in the db and there
are quite a few, plus there are quite a few sql's in modules.
Tom

 

[Tim Ferguson]

Is there a simple way for me to change the Run Permissions property
from the default "user's" to owners?

Just edit the SQL property of the query:

dim qdf as QueryDef

Set qdf = QueryDefs("somequery")
qdf.SQL = qdf.SQL & vbNewLine & "WITH OWNERACCESS;"


Actually, you need to check if the query ends with a semicolon and strip it
off first if it's there, but you get the picture.


HTH


Tim F

 

[Tom]

Tim - or anyone,
That worked great except for one thing - it did not change the permissions
in any of the sql statements used outside of the actual queries themselves.
e.g. combo boxes based on sql statements, sql statements behind forms or
that form record sources are based on, etc.
Is there any way to do that?
I thought I would get it by looping through every query def but that did not
work. Here is the code I ran:

Function SetProp()
On Error GoTo Err_SetProp
Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim iFound As Integer
Set db = CurrentDb()

For Each qdf In db.QueryDefs
qdf.sql = Mid(qdf.sql, 1, Len(qdf.sql) - 3) 'this get's rid of the
; at the end of the sql statement
'Debug.Print qdf.sql

qdf.sql = qdf.sql & vbNewLine & " WITH OWNERACCESS OPTION;"

Next qdf

Exit_SetProp:
Exit Function

Err_SetProp:
If Err.Number = 3000 Or Err.Number = -8169 Then
Resume Next
ElseIf Err.Number = 3137 Then
qdf.sql = qdf.sql & ";"
Resume Next
Else
MsgBox Err.Number & " " & Err.Description
Debug.Print qdf.sql
Resume Next
End If

'Next qdf

End Function
As you can see I had to trap some specific errors which I don't know why.
The -8169 was saying I did not have permissions on the combo box on one of
the forms, but I am on as the administrator with full permissions and I am
also the owner of all objects. The error 3137 said there was a problem in
the FROM part of two of the SQL's, but everything has been working perfectly
fine.
Thanks in advance for your help.
Tom

 

[Tim Ferguson]

sql statements used outside of the actual queries themselves.
e.g. combo boxes based on sql statements, sql statements behind forms or
that form record sources are based on, etc.
Is there any way to do that?

Ummm: in a word, "no".

Actually, there are ways of helping you do this -- check out some tools
like SpeedFerret which are designed to help in database-wide searches. I
haven't used this myself, but I know lots of people round here are very
complimentary about it. Check out <http://www.fmsinc.com>.

If you cannot afford this, then you will have to fall back on the original
documentation (you did... didn't you? <g>) or just go through each form by
hand.

Can't help you with the specific error messages, I'm afraid.

Best wishes



Tim F