Modifying Queries in a Secure Database

[R2D2]

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?

 

[R2D2]

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!

 

[R2D2]

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.

 

[R2D2]

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.