asked on

Owner Access Option in DAO or ADO

In SQL, I can run an action query on a dataset "WITH OWNERACCESS OPTION" to get around security limitations on a user (skip example if you know what i'm talking about).

Example:
User belongs to Sales group... sales group does NOT have DELETE permissions on tblInvoices. The user cannot, therefore, delete any invoices. However, if I create a delete button and attach a query to it, tagging the "WITH OWNERACCESS OPTION" onto the query, this user can now delete a record.

If I have a need to use "RunCommand acCmdDeleteRecord" instead of a query (i have my reasons - not worth going into right now), how can I still get around the security limitation. Is there an equivelent "...OPTION" in the DAO/ADO 'world'?

ic

JimMorgan

You can use 'With OwnerAccess Option' with SQL Select statements but not with any other types of SQL code queries.

To do anything else you need to change the permissions on the fly for the user to do this without using a query. If you forget to change the premissions back, you could get into trouble.

There is an out, which the same one we are using. We just lock out all tables for any users. Then we create table queries which so nothing more than select all the fields from each table. Then a 'RunCommand acCmdDeleteRecord' based on the select record of one of these table queries, will work as the user has the owner access options for the query.

A user can not make their own queries on the tables but they can make a query based on the table query. So if the user has no way to get to the database window to create their own query and no way to create code modules, you are protected.

Jim

ASKER

How do I change permissions "on the fly" for a given user?

ic

ASKER

Let's say my code looks like:

If [criterion = true] then
msgbox "You have already entered this record"
RunCommand acCmdDeleteRecord
End If

I get an error because the user cannot delete in the current table being accessed.

I'm looking for something to the effect of:

RunCommand acCmdDeleteRecord, With OwnerAccess Option

or:

CurrentUser.Permissions = Owner
RunCommand acCmdDeleteRecord
[set permissions back to normal]

These are hypothetical and will not work in VBA, of course, but is there a syntax would go something like this? This is the HOW TO? I am asking for. (Thanks for your help so far (!) by the way).

IC

ASKER CERTIFIED SOLUTION

JimMorgan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ah, okay, so there's some considerable work involved. Perhaps it's not the way. Just as an FYI, I have some code that loops through and tells the user if s/he has entered the item twice (invoice subform) - I'm not sure why I don't have a "No Duplicates" setting in the invoice details table; I thought there was a reason, but I can't remember anymore, maybe I'll just go with that instead.

Now that I'm also hip to using temp tables, I can grant necessary permissions on the temp tables. Been good chattin' with ya.

ic