User-level access with automatic filters

[SpyderiteTech]

I'm somewhat new to Access and I am wondering if user-level access can do
what I want it to do. I have gone through the wizard and have setup accounts
and permissions, but is it possible to go a step further and limit a user to
only be able to edit information entered by that user?

For example, user: Brenda logs in under her name and password and enters
data into a form linked to table "customers". Teresa logs in under her info
and enters data into the same form. Is it possible that Brenda can only see
the records created by her account and not those by Teresa automatically?

If this isn't possible, I can still accomplish the same affect by creating
user specific forms and tables (i.e., Customers_Brenda), but can I control
the user access to individual forms and not others?

Any help would be happy.

~Chris K.

 

[Graham Mandeno]

Hi Chris

This can be done easily by using a "run with owner permissions" (RWOP)
query.

First, change the permissions on your table(s) to deny all access to
ordinary users. Then create a query on which to base your form. Add a
filter criterion for this query to show only those records where the
[EnteredBy] field is equal to CurrentUser().

Now, in the query's properties sheet, change Run Permissions from "User's"
to "Owner's", and grant access to the saves query for anyone who needs to
use the form.

This means that although Brenda and Teresa have no access to the table, they
can access the data via the query because it runs with the same access as
the owner. However, the query only shows records created by the current
user, so Brenda and Teresa have access to their own records, but not to each
other's.

 

[SpyderiteTech]

This sounds like it can work...just two questions:

1) You say the users would not have access to the table. Do you mean they
won't be able to view it, or they won't be able to add info to it via the
form?

2) I'm a bit gunshy with queries still (I've explored everything else saving
them for last). Would you be able to detail a step-by-step procedure for the
strategy you have suggested?

Again, thanks!

~Chris K.

Hi Chris

This can be done easily by using a "run with owner permissions" (RWOP)
query.

First, change the permissions on your table(s) to deny all access to
ordinary users. Then create a query on which to base your form. Add a
filter criterion for this query to show only those records where the
[EnteredBy] field is equal to CurrentUser().

Now, in the query's properties sheet, change Run Permissions from "User's"
to "Owner's", and grant access to the saves query for anyone who needs to
use the form.

This means that although Brenda and Teresa have no access to the table, they
can access the data via the query because it runs with the same access as
the owner. However, the query only shows records created by the current
user, so Brenda and Teresa have access to their own records, but not to each
other's.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm somewhat new to Access and I am wondering if user-level access can do
what I want it to do. I have gone through the wizard and have setup
accounts
and permissions, but is it possible to go a step further and limit a user
to
only be able to edit information entered by that user?

For example, user: Brenda logs in under her name and password and enters
data into a form linked to table "customers". Teresa logs in under her
info
and enters data into the same form. Is it possible that Brenda can only
see
the records created by her account and not those by Teresa automatically?

If this isn't possible, I can still accomplish the same affect by creating
user specific forms and tables (i.e., Customers_Brenda), but can I control
the user access to individual forms and not others?

Any help would be happy.

~Chris K.

 

[Graham Mandeno]

Hi Chris

1) Your users will have whatever access to whatever rows/columns of the
table that you wish to grant them, but not directly - only via the RWOP
queries that you design.

2) Get to know queries! Without them, a relational database is completely
useless! It is a query that takes the data from the "buckets" that are
related tables and presents it them in a meaningful, orderly way.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

This sounds like it can work...just two questions:

1) You say the users would not have access to the table. Do you mean they
won't be able to view it, or they won't be able to add info to it via the
form?

2) I'm a bit gunshy with queries still (I've explored everything else
saving
them for last). Would you be able to detail a step-by-step procedure for
the
strategy you have suggested?

Again, thanks!

~Chris K.

Hi Chris

This can be done easily by using a "run with owner permissions" (RWOP)
query.

First, change the permissions on your table(s) to deny all access to
ordinary users. Then create a query on which to base your form. Add a
filter criterion for this query to show only those records where the
[EnteredBy] field is equal to CurrentUser().

Now, in the query's properties sheet, change Run Permissions from
"User's"
to "Owner's", and grant access to the saves query for anyone who needs to
use the form.

This means that although Brenda and Teresa have no access to the table,
they
can access the data via the query because it runs with the same access as
the owner. However, the query only shows records created by the current
user, so Brenda and Teresa have access to their own records, but not to
each
other's.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message

I'm somewhat new to Access and I am wondering if user-level access can
do
what I want it to do. I have gone through the wizard and have setup
accounts
and permissions, but is it possible to go a step further and limit a
user
to
only be able to edit information entered by that user?

For example, user: Brenda logs in under her name and password and
enters
data into a form linked to table "customers". Teresa logs in under her
info
and enters data into the same form. Is it possible that Brenda can
only
see
the records created by her account and not those by Teresa
automatically?

If this isn't possible, I can still accomplish the same affect by
creating
user specific forms and tables (i.e., Customers_Brenda), but can I
control
the user access to individual forms and not others?

Any help would be happy.

~Chris K.