code to set run permissions on query's (1 Viewer)

[jonnie_c]

i want to set the 'Run Permission' property for all querys in my database to 'Owner'. There are around 400 querys in this database and i would not like to do it manually. can i do this programatically?
ie.
For Each query In my Database
Run permission property = Owner
Next

 

[jonnie_c]

errrrr.... queries, i mean

 

[jonnie_c]

*cough*

 

[The_Doc_Man]

First, I'm surprised it isn't already set this way. I might have been totally in error, but I thought that 'ByOwner' was the default. Oh, well, no matter. Check path

Tools>>Options>>Tables/Queries and set the default run permission that you want. This will take care of all new queries.

Your question almost contains your answer regarding older queries.

You have to open the database as an object. Then step through the querydefs collection.

For each querydef in the collection, you can see its properties. Set the one you want. Close it.

The code snippet might resemble

-----------------------------------------------------------
Dim dbCur as Database
Dim qdCur as QueryDef

Set dbCur = CurrentDB

For Each qdCur in dbCur.QueryDefs

qdCur.RunPermissions = ?

Next qdCur

---------------------------------------------------

My problem is that I can't find the constant you want, hence the question mark. Can someone else find that? I went through all the constants in the object browser and they didn't seem to apply.

 

[R2D2]

Not to resurrect a thread or anything...but I've been working on this very problem today, and have written a function that does this:

Public Function SetRunPermissions(qryName As String, SetToOwner As Boolean)
On Error GoTo SetRunPermissions_Error
    
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Dim mySQL As String
    
    mySQL = dbs.QueryDefs(qryName).SQL
    
    If (SetToOwner) Then
        If (InStr(1, mySQL, "WITH OWNERACCESS OPTION", vbBinaryCompare) <> 0) Then
            'MsgBox "Owner option already set."
            Exit Function
        Else
            mySQL = Left(mySQL, InStr(1, mySQL, ";", vbBinaryCompare) - 1) & " WITH OWNERACCESS OPTION;"
            'MsgBox "Setting owner option."
        End If
    Else
        If (InStr(1, mySQL, "WITH OWNERACCESS OPTION", vbBinaryCompare) <> 0) Then
            mySQL = Left(mySQL, InStr(1, mySQL, "WITH OWNERACCESS OPTION", vbBinaryCompare) - 1) & " ;"
            'MsgBox "Removing owner access option."
        Else
            'MsgBox "Owner option already removed."
            Exit Function
        End If
    End If
    
    dbs.QueryDefs(qryName).SQL = mySQL
    'MsgBox dbs.QueryDefs(qryName).SQL
    
    Exit Function

SetRunPermissions_Error:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    
End Function