Make table query and permissions

[jtkjames]

Hello

I have an issue with a make-table query. Im not sure if I should have posted this in teh Table or Query sections of the forum, so I apologise if this is inappropriately classified.

I am using Access XP/2002 and the MDB is Access 2002 format and has user level security enabled.

The requirement is that members of a certain group can run a make table query. The purpose of the make table query is to "install" the data from a linked table to an internal table. Using append and delete queries isn't an option because I am using a macro that runs 50+ of these make table queries in a row! (only takes a few minutes to do though)

Anyway when the user runs the macro, unless they have Admins membership, they get the error message

"Could not create; no modify design permission for table or query <tablename>".

At first I reckoned that it is because it deletes the table first (the one with permission) then creates a new one which wont have permissions set.

I have been googling for a few days now how to solve this problem and have tried:

- setting user group and users permissions for "<New Tables / Queries>" to "Administer" (the obvious solution)

- putting "WITH OWNERACCESS OPTION" at the end of the make-table query's SQL

- changing the owner of the table and query to the user and group concerned

- making the group both manually and via the wizard

But it still doesnt work. Alarmingly I have found a few people who say it is impossible, but I can't believe that, because this seems a fundamental feature of userlevel security - to allow people to run make table queries without granting full Admins membership.

Anyone had this issue and solved it or could shed some light on a solution please? its been bugging me for days now.

Thanks!

James

 

[Rabbie]

James, it seems your design is flawed if you need to run 50 Maketable queries in succession. It seems to me to be open to a high risk of corruption. It should be possible to move the data from the linked table to a permanent table by using an append query which should be safer.

Allowing user to create tables is always risky and should be tightly controlled.

 

[jtkjames]

Thanks so much for the quick response Rabbie

I appreciate that it would be more secure and less prone to corruption to use append queries, but for my purposes I need to be able to use make table queries.

Do I presume that my fears are true and it is in fact impossible given the structure of the user level security in Access?

Thanks again for your help

James