I'm working with Access Security for the first time. I've read the FAQ, and I feel like I have a good grasp of how it works overall.

The code on my main form creates a query and saves it as a queryDef. However, I need this query to be able to pull records from a table that is off-limits to my users. I've set the run permissions for the query to Owner's, and set the owner to the Admins group, which has access to everything.

The problem I'm running into is that it seems that the query can't be modified without the logged-in user being the owner, but I need the owner to be the Admins group so that it'll be able to pull records from my tables.

However, it seems like I should simply be able to give my users "modify design" permissions on the queries. But this won't work....I've only gotten it to work when I'm the owner.

What's going on? Is there any way around this?

The queries should be built by the owner (you) and saved with the owner's run permissions, NOT the users. This will allow the user to RUN the query and have what ever data access the query owner has but will prevent the user from making his own queries or modifying table data directly. I know it is confusing but this is the only method I have seen that will completely lock down data access to ONLY what your forms and queries provide.

That makes sense to me, but the problem is that my users (or rather, my code that runs while my users are logged into my database) need to be able to modify queries. My entire database is built around a query that is created by the options my users choose on the attached form. When they hit "Run Query" a bunch of VBA code runs that contructs the appropriate SQL query string and saves it as a querydef SQL to an existing query.

So, I want this query to be modifiable by my users, while being set up with Owner's Run Permissions, and have the Owner remain as the user who originally created the query. Is this possible? I'm starting to think that Access security isn't flexible enough for this.

If it was feasible to make a seperate query for each combination of options (rather than constructing the SQL on the fly and saving a querydef), I would do that, but with as my options as I've provided, there's probably a good 500-1000 (if not more) different queries that are possible here.

Thanks!

I don't have a secured db to try this in so I can't verify that it will work but I would NOT save the querydef. I would just run the SQL string.

I'll give that a shot, but I read in the security FAQ that you can only use owner run permissions on a saved query, not on an SQL string that you run using DoCmd.RunSQL.

I know that. I should have been more specific. Leave the run permissions out.

If that doesn't work, you can try creating a query and setting the permissions for that query only to allow users to update it. Then have your code update that query. This will only work if the users have their own copies of the fe. You cannot modify objects in shared databases. Not to mention the problem of two users trying to use the same object at the same time.

I've gotten it to work! I couldn't get any of your ideas to work (anytime I modified a query, it's Run Permissions got set back to User's automatically and running the SQL via code wouldn't allow Owner's permissions either), but your ideas did get me thinking in the right directly, and I'm very grateful for the help. Here's what I did to get it work:

I created a simple query ("SELECT * from tblSecureFinanceRecords") and set the run permissions on this query to Owner's. Since I never have to change this query, it's run permissions can remain as Owner's. Then, the query that my form constructs pulls records from this query rather than from tblSecureFinanceRecords itself. Everything runs right, and the users are restricted from accessing the table itself.