Group Permissions and SQL

[croy]

Split, secured database. Everything working well, except...

In the After Update event for a control on a form, I have
this code:

Private Sub grpLocType_AfterUpdate()

Dim SQL As String
SQL = "DELETE qrysfrmLocPicker.GeoLocId " & _
"FROM tblLocPicker WITH OWNERACCESS OPTION"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Me![sfrmLocPickerObj].Form.Requery

End Sub

It works fine with the Admins group, and with another group
"Field-Admins". But it won't work with another group having
lesser permissions.

The error is "Run-time error '3112': Records cannot be
read; no read permissions on 'tblLocPicker'", and it leads
to the SQL statement above.

But this groups permissions *do* have permissions to do
anything but administer or modify that linked table. I've
gone so far as to give that group administer permission on
every table, every query, and every form, and it made no
difference. And the SQL refers to a saved query with "Run
with Owner's Permissions".

What am I missing?

 

[Joan Wild]

With Owneraccess option in the SQL statement is pointless if qrysfrmLocPicker is a saved RWOP query (is it?)

Do the users have delete permissions on qrysfrmLocPicker?

--
Joan Wild
Microsoft Access MVP
: Split, secured database. Everything working well, except...
:
: In the After Update event for a control on a form, I have
: this code:
:
: Private Sub grpLocType_AfterUpdate()
:
: Dim SQL As String
: SQL = "DELETE qrysfrmLocPicker.GeoLocId " & _
: "FROM tblLocPicker WITH OWNERACCESS OPTION"
: DoCmd.SetWarnings False
: DoCmd.RunSQL SQL
: DoCmd.SetWarnings True
:
: Me![sfrmLocPickerObj].Form.Requery
:
: End Sub
:
: It works fine with the Admins group, and with another group
: "Field-Admins". But it won't work with another group having
: lesser permissions.
:
: The error is "Run-time error '3112': Records cannot be
: read; no read permissions on 'tblLocPicker'", and it leads
: to the SQL statement above.
:
: But this groups permissions *do* have permissions to do
: anything but administer or modify that linked table. I've
: gone so far as to give that group administer permission on
: every table, every query, and every form, and it made no
: difference. And the SQL refers to a saved query with "Run
: with Owner's Permissions".
:
: What am I missing?
:
: --
: croy

 

[Wolfgang Kais]

Hallo "croy".

Split, secured database. Everything working well, except...

In the After Update event for a control on a form, I have
this code:

Private Sub grpLocType_AfterUpdate()

Dim SQL As String
SQL = "DELETE qrysfrmLocPicker.GeoLocId " & _
"FROM tblLocPicker WITH OWNERACCESS OPTION"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Me![sfrmLocPickerObj].Form.Requery

End Sub

The SQL looks strange. Usually tblLocPicker would be the name of a
table but qrysfrmLocPicker would be the name of a query.

It works fine with the Admins group, and with another group
"Field-Admins". But it won't work with another group having
lesser permissions.

The error is "Run-time error '3112': Records cannot be
read; no read permissions on 'tblLocPicker'", and it leads
to the SQL statement above.

But this groups permissions *do* have permissions to do
anything but administer or modify that linked table. I've
gone so far as to give that group administer permission on
every table, every query, and every form, and it made no
difference. And the SQL refers to a saved query with "Run
with Owner's Permissions".

What am I missing?

The "owneraccess option" doesn't strike through to the backend db,
it only affects the "table link". You will either have to give the
users delete permissions on the backend table or use an IN clause
in the query that contains the path to the backend database:
DELETE * FROM tblLocPicker
IN 'C:\somepath\backend.mdb'
WITH OWNERACCESS OPTION
But wait: Who is the owner of a sql string in code? So you should
save the delete query in the database and execute it using
DoCmd.OpenQuery

 

[Joan Wild]

:
: The SQL looks strange. Usually tblLocPicker would be the name of a
: table but qrysfrmLocPicker would be the name of a query.

There's nothing wrong with having a SQL statement (or even a saved query) based on a query.


: The "owneraccess option" doesn't strike through to the backend db,
: it only affects the "table link". You will either have to give the
: users delete permissions on the backend table or use an IN clause
: in the query that contains the path to the backend database:

That is not true. The RWOP means run the query as though the owner (of the query) is running it. As long as the owner has the appropriate permissions on the tables, then it will run.

 

[Wolfgang Kais]

Hello Joan.

There's nothing wrong with having a SQL statement (or even a saved
query) based on a query.

Correct, nothing wrong with a query based on a query.
But how about the column qualifier qrysfrmLocPicker.GeoLocId that
refers to a table alias of qrysfrmLocPicker that does not appear
in the FROM clause?

That is not true. The RWOP means run the query as though the owner
(of the query) is running it. As long as the owner has the appropriate
permissions on the tables, then it will run.

I have tested that right now, and ... you're right, sorry.
I don't remember what was wrong with that, but I know that I ran into
problems with this some years ago. Maybe it was an issue in Access 97?

But how about using WOP in code? Is the owner of the module also the
owner of the SQL string that is run from VBA?

 

[Keith Wilby]

But how about using WOP in code? Is the owner of the module also the
owner of the SQL string that is run from VBA?

If you have no permissions to the table that your VBA SQL is querying then
you will get an access denied error. You need to query a stored RWOP query.

Keith.
www.keithwilby.com

 

[Joan Wild]

: Hello Joan.
:
: Joan Wild wrote:
:
: > There's nothing wrong with having a SQL statement (or even a saved
: > query) based on a query.
:
: Correct, nothing wrong with a query based on a query.
: But how about the column qualifier qrysfrmLocPicker.GeoLocId that
: refers to a table alias of qrysfrmLocPicker that does not appear
: in the FROM clause?

You are correct; that isn't right - I didn't actually read the SQL statement details.

: I have tested that right now, and ... you're right, sorry.
: I don't remember what was wrong with that, but I know that I ran into
: problems with this some years ago. Maybe it was an issue in Access 97?

I've never experienced the problem (even with 97). Users don't need any permissions on the backend tables, or the links for RWOP to work. You might have been tripped up by the table lookup wizard.

: But how about using WOP in code? Is the owner of the module also the
: owner of the SQL string that is run from VBA?

No. When a SQL statement is run from VBA, a temporary query is created, and that is owned by the user who created it (likely the current user) - so it won't work.