RWOP Queries

[Dave Griffiths]

Does anyone know a way using VBA to set the Run Permissions property to
Owner's for all the queries in a database.

I want to do this as recommended in the Security FAQ, but I have so many
queries I don't want to edit each one manually.

I tried the following code to enumerate the properties in a query, but
RunPermissions doesn't appear to be one. So how can I set it?

Dim db As Database
Dim qdef As QueryDef
Dim prp As Property

Set db = CurrentDb

For Each qdef In db.QueryDefs
For Each prp In qdef.Properties
MsgBox prp.Name
Next prp
Next qdef

Any help gratefully received.
Thanks
Dave

[Brendan Reynolds]

AFAIK (some one please tell me if I'm wrong) the "WITH OWNERACCESS OPTION"
clause is always the last part of a SQL statement? If so, this should work
(it worked in my brief tests, but of course that doesn't necessarily mean it
will work with every query).

Public Sub RWOP()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
For Each qdf In db.QueryDefs
strSQL = qdf.SQL

'At first I thought Left$(strSQL, Len(strSQL) - 1)
'would do it, but it doesn't work - there must be
'a carriage return or something after the semi-colon.
strSQL = Left$(strSQL, InStr(1, strSQL, ";") - 1)
strSQL = strSQL & " WITH OWNERACCESS OPTION;"
qdf.SQL = strSQL
Next qdf
Set qdf = Nothing
Set db = Nothing

End Sub

--
Brendan Reynolds
bren...@indigo.ie
http://www11.ewebcity.com/brenreyn

Dave Griffiths <da...@k2computers.co.uk> wrote in message
news:978734051.5689.0...@news.demon.co.uk...

[Pete B]

You can only create an OP query by saving it as such from the design window.
In other words, you cannot reset a previously saved querydef object to a
RWOP query, even though it includes that SQL clause. The property seems to
get set only when the querydef object is saved. That is why, for example,
if you import RWOP queries into a new database, you must manually open each
such query in design mode and resave it as an OP query (while logged in as
the owner, of course).

--
Pete B

Brendan Reynolds <bren...@indigo.ie> wrote in message
news:b0t56.4474$s4....@news.indigo.ie...

[Brendan Reynolds]

OK, how about this. When I run this code and then open the query in design
view, the Run Permissions property says 'Owner's'. But I don't have a
secured MDB on this machine to test it on.

Public Sub RWOP()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
For Each qdf In db.QueryDefs
strSQL = qdf.SQL

'At first I thought Left$(strSQL, Len(strSQL) - 1)
'would do it, but it doesn't work - there must be
'a carriage return or something after the semi-colon.
strSQL = Left$(strSQL, InStr(1, strSQL, ";") - 1)
strSQL = strSQL & " WITH OWNERACCESS OPTION;"
qdf.SQL = strSQL

'new code.
DoCmd.OpenQuery qdf.Name, acViewDesign
DoCmd.Close acQuery, qdf.Name, acSaveYes

Next qdf
Set qdf = Nothing
Set db = Nothing

End Sub
--
Brendan Reynolds
bren...@indigo.ie
http://www11.ewebcity.com/brenreyn

Pete B <bar...@datatek.com> wrote in message
news:t5ee9vn...@news.supernews.com...

[Pete B]

That should work for the purpose of saving qdef objects as OP from code, but
I am not too sure that your code will produce valid SQL strings. In any
case, the point is that without the lines to open and save the object, I
believe you could not have an OP query that way. IOW opening the querydef,
modifying its' SQL string to add the OP clause, and then running the query
would not work, you must explicitly save it first, close it. and then reopen
it.

And of course, doing so in an otherwise unsecured database, as you mentioned
you are doing, would accomplish nothing, because the owner of the qdef
object would be the Admin user; since OP queries cannot be modified by
anyone other than the owner, you have only restricted the modification
ability to the known universe (to wit, the Admin users), rather than to a
particular secured individual administrator :=). Making an OP query in an
unsecured database is a pointless exercise, except to test your code
perhaps. But I am sure you are aware of that.

The time to make a query into an OP query is after you have otherwise
secured the database completely. Also, of course, after you modify and save
the qdef object to make it an OP query owned by a secure administrator, you
would then have to follow up and set permits on the object for the other
users.

--
Pete B
Brendan Reynolds <bren...@indigo.ie> wrote in message

news:99K56.4683$s4....@news.indigo.ie...

[Brendan Reynolds]

Indeed, Pete, yes, I ran this code in an unsecured database only to test it,
and only because I didn't have a secured database with which to test it. I
have received an e-mail from the original poster who says it is working for
him. I hope he'll let us know if he discovers any problems with it down the
road.

--
Brendan Reynolds
bren...@indigo.ie
http://www11.ewebcity.com/brenreyn

Pete B <bar...@datatek.com> wrote in message

news:t5jlchi...@news.supernews.com...