Access query/table changing owner in multi-user database

17 Oct 2006 8:51 AM

Neil Morris

I run a multi-user database at work and have a problem with a query/table
changing owner. I have a simple query which filters some data for the user
and puts the result in a table (user select an option from a combo box which
is the criteria for the query). The user then views a form based on the
table. It all works fine, but in my secured database I cant work out what
permissions I need to set for the users to run this. if I give them read
design/data insert/update/delete for the table they get a "no permissions"
error. If I give them modify design as well, when they run it the table
changes owner to that user and then next user then doesnt have permissions.
I can't make any sense of the online help and would be very grateful for any
advice.

many thanks
Neil

17 Oct 2006 9:55 AM

Keith Wilby

Show quote Hide quote

"Neil Morris" <Neil Mor***@discussions.microsoft.com> wrote in message
news:C80EB24D-97BE-4202-9413-CE1739E9E876@microsoft.com...
>I run a multi-user database at work and have a problem with a query/table
> changing owner. I have a simple query which filters some data for the
> user
> and puts the result in a table (user select an option from a combo box
> which
> is the criteria for the query). The user then views a form based on the
> table. It all works fine, but in my secured database I cant work out what
> permissions I need to set for the users to run this. if I give them read
> design/data insert/update/delete for the table they get a "no permissions"
> error. If I give them modify design as well, when they run it the table
> changes owner to that user and then next user then doesnt have
> permissions.
> I can't make any sense of the online help and would be very grateful for
> any
> advice.
>
> many thanks
> Neil

I think your problem stems from the fact that your users are expected to
create a table in a secured app. Why not just base your form on a select
query instead of a make table query? The data in the new table will be
redundant and will bloat the file size.

HTH - Keith.
www.keithwilby.com

23 Oct 2006 9:10 PM

Andy Bleese

Did you manage to solve your problem - if so, how ?
I seem to have a similar problem; multi user enviroment, all editing info
stored on one table (X). They call up the required record on a form which
edits the record number stored on another table (Z). Another form cross-refs
the two tables and displays all the relevant details.
Error occurs when another user edits the number stored in table Z. The
second user gets the record he wants to display whilst the first users form
'switches' to also show the second users record.
How do i lock the first users record whilst he edits it but allow the second
user to edit a different record.

Thanks.