set query permissions in VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CathyJ - 03 May 2007 22:26 GMT

I have a form which has a button that runs code to create a new query in VBA
every time it is clicked (the old query is first deleted). I would like other
users to be able to run this code also, but have not found a way to set the
permissions in VBA to do give them permissions for this query.

I'm sure there must be a way. Can anyone help?

Many thanks!!!!!

Reply to this Message

Scott McDaniel - 03 May 2007 22:55 GMT

>I have a form which has a button that runs code to create a new query in VBA
>every time it is clicked (the old query is first deleted). I would like other
>users to be able to run this code also, but have not found a way to set the
>permissions in VBA to do give them permissions for this query.

In the User and Group Permissions dialog (Tools - Database Security - User and Group Permissions), select Query in the
Object Type dropdown, then provide the relevant group full permissions (or whatever subset is appropriate) for the <New
Tables/Queries> Object Name. Now when a new query is added, the Group you selected earlier will have the permissions you
indicated.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Reply to this Message

CathyJ - 04 May 2007 14:52 GMT

Thanks Scott.

I tried giving both the user and the group Read, Update, Insert & Delete
permissions for New Queries, and I still get the same error. Could it have
something to do with the Tables being in a different place (back-end)? This
user has full permissions for those tables.

Or do I need to give her Administer access? I'd hate to start doing that,
although it doesn't really matter since she can't get to it anyway.

Thanks in advance!

> >I have a form which has a button that runs code to create a new query in VBA
> >every time it is clicked (the old query is first deleted). I would like other
[quoted text clipped - 9 lines]
> scott@takemeout_infotrakker.com
> www.infotrakker.com

Reply to this Message

Aaron Kempf - 03 May 2007 23:48 GMT

you should look up

GRANT EXECUTE

in SQL Server books online

you shouldn't use DAO crap for this; it's ridiculous because DAO has been
depecrated

> I have a form which has a button that runs code to create a new query in VBA
> every time it is clicked (the old query is first deleted). I would like other
[quoted text clipped - 4 lines]
>
> Many thanks!!!!!

Reply to this Message

CathyJ - 04 May 2007 15:07 GMT

Thanks Aaron.

could you please send me a link to an SQL server book that has this in it?
I'm not a programmer, just a lowly marketing type! You would probably have a
heart attack if you saw my code.

> you should look up
>
[quoted text clipped - 16 lines]
> >
> > Many thanks!!!!!

Reply to this Message

BruceM - 04 May 2007 17:50 GMT

Ignore Aaron, a bitter and unhappy troll who hates Access, and who has taken
up residence here of late. There are perfectly valid reasons for using SQL
server, but they probably don't apply to the current project.

> Thanks Aaron.
>
[quoted text clipped - 24 lines]
>> >
>> > Many thanks!!!!!

Reply to this Message

Joan Wild - 04 May 2007 15:36 GMT

The users would need at minimum read data permissions on the source tables involved in this query.

Signature

Joan Wild
Microsoft Access MVP

>I have a form which has a button that runs code to create a new query in VBA
> every time it is clicked (the old query is first deleted). I would like other
[quoted text clipped - 4 lines]
>
> Many thanks!!!!!

Reply to this Message

CathyJ - 04 May 2007 15:54 GMT

they have read, update, insert & delete permissions for the four tables
involved in the query. in both the back-end module and the front-end one (I
never know if they need both, so I err on the side of safety).

thanks

> The users would need at minimum read data permissions on the source tables involved in this query.
>
[quoted text clipped - 6 lines]
> >
> > Many thanks!!!!!

Reply to this Message

Joan Wild - 04 May 2007 22:04 GMT

Do any of those four tables have lookup fields defined in them? If so then they need permissions on those as well.

It would help if you posted the text of the error message that occurs when users run the code.

Signature

Joan Wild
Microsoft Access MVP

> they have read, update, insert & delete permissions for the four tables
> involved in the query. in both the back-end module and the front-end one (I
[quoted text clipped - 12 lines]
>> >
>> > Many thanks!!!!!

Reply to this Message