How to Keep Certain Users from Editing When In Tables

  • Thread starter Thread starter doyle60
  • Start date Start date
D

doyle60

I have a split Access 2007 Database (.accdb). Each user has a front-
end
on their desktop and the back-end is on a shared drive.

1) Is there any way I can keep certain users from accessing tables,
or
limit their ability to add / delete / edit data after they have opened
a table?

2) If I cannot keep users out of the tables, can I keep track of what
was added / edited by whom and when?

Matt
 
The only way I've been able to do this is by setting up a custom user
security feature within the app. I'm not sure if MS provides a way to handle
it natively, but here's a quick rundown on what mine consists of.

1) A Users table setup with related records for what areas the users will be
able to see/edit (ex tblUsers ---(1toMany)---> tblUsers_Security)

2) A login/out setup so that you know which user is active while the changes
are being made (if'd you'd like you can use just the computername in
substitute of the whole login/out module, but I prefer any user to be able to
log in from any machine).

3) A procedure for verifying and setting the rights for the user on the
opening of all data entry points (and also restricting those particular
fields in records or other output points). I have a standard procedure in
each form and report that is checked on startup, which then sets certain form
and control properties based on the security level I store in
tblUsers_Security


It's a pretty lengthy setup, but works great when it's all said and done (I
also use it to store the user and time that each record is edited, and for
some forms, each field change). If you're interested I can go into more
detail.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Jack Leach wrote:
<< 3) A procedure for verifying and setting the rights for the user on
the
opening of all data entry points (and also restricting those
particular
fields in records or other output points). I have a standard
procedure in
each form and report that is checked on startup, which then sets
certain form
and control properties based on the security level I store in
tblUsers_Security >>

1) Does this include tables? You say "data entry points" and am not
sure this is for tables or not.

2) Is there a way to keep all users out of tables? (If I want them to
go into one directly, I could just build a form/sub for it). I'm just
concerned if a user goes directly to a table how all this works.

Thanks,

Matt (asking for another)
 
Jack Leach wrote:
<< 3) A procedure for verifying and setting the rights for the user on
the
opening of all data entry points (and also restricting those
particular
fields in records or other output points). I have a standard
procedure in
each form and report that is checked on startup, which then sets
certain form
and control properties based on the security level I store in
tblUsers_Security >>

1) Does this include tables? You say "data entry points" and am not
sure this is for tables or not.

2) Is there a way to keep all users out of tables? (If I want them to
go into one directly, I could just build a form/sub for it). I'm just
concerned if a user goes directly to a table how all this works.

Thanks,

Matt (asking for another)
 
1) Does this include tables? You say "data entry points" and am not
sure this is for tables or not.
Click to expand...

This is based on the fact that no users should ever come directly in contact
with a table. Thus I use the phrase data entry points in place of forms,
which is where users should be editing data from (I think that one of the
reasons users should not come in contact with tables is for this reason...
regardless, this is one of the ten commandments of db development).
Occasionaly, user may end up executing queries, which can be restricted in
this way (programmatic selection of fields in the query based on user
security level), where tables cannot.

2) Is there a way to keep all users out of tables? (If I want them to
go into one directly, I could just build a form/sub for it). I'm just
concerned if a user goes directly to a table how all this works.
Click to expand...

Don't let tables be a part of the interface... again, they should never come
in contact with tables directly, but instead use forms/subforms, queries,
etc. In the startup properties for the db uncheck Show Database Window and
uncheck the option for using special keys (this gets rid of the user being
able to hit F11 to pull up the db window). This can be done programmatically
as well, but personally I think its much much easier to do it through the
startup options (in fact... I think that ALL of the access interface should
be removed... menus, toolbars, db window, special keys, etc, and replaced
with such that are relevant to your application). I generally do the db
window and special keys right before deployment to make the development
process a bit easier to handle.

By setting these startup commands and deploying and MDE file, users should
have no methods of coming into contact with the tables, no matter how hard
they try (unless of course you give them an option to somehow).


(startup commands are based on access 2000 - 2003 (not sure about 07))

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
1) Does this include tables? You say "data entry points" and am not
sure this is for tables or not.
Click to expand...

This is based on the fact that no users should ever come directly in contact
with a table. Thus I use the phrase data entry points in place of forms,
which is where users should be editing data from (I think that one of the
reasons users should not come in contact with tables is for this reason...
regardless, this is one of the ten commandments of db development).
Occasionaly, user may end up executing queries, which can be restricted in
this way (programmatic selection of fields in the query based on user
security level), where tables cannot.

2) Is there a way to keep all users out of tables? (If I want them to
go into one directly, I could just build a form/sub for it). I'm just
concerned if a user goes directly to a table how all this works.
Click to expand...

Don't let tables be a part of the interface... again, they should never come
in contact with tables directly, but instead use forms/subforms, queries,
etc. In the startup properties for the db uncheck Show Database Window and
uncheck the option for using special keys (this gets rid of the user being
able to hit F11 to pull up the db window). This can be done programmatically
as well, but personally I think its much much easier to do it through the
startup options (in fact... I think that ALL of the access interface should
be removed... menus, toolbars, db window, special keys, etc, and replaced
with such that are relevant to your application). I generally do the db
window and special keys right before deployment to make the development
process a bit easier to handle.

By setting these startup commands and deploying and MDE file, users should
have no methods of coming into contact with the tables, no matter how hard
they try (unless of course you give them an option to somehow).


(startup commands are based on access 2000 - 2003 (not sure about 07))

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Back
Top