How to protect tables

[Guest]

Hello,

I want to force users to use my form which has data validation to enter new
records into my table. The problem is, they can easily open the table and
input whatever they like, including blank records.

Is there a way to prevent users from opening the table directly to enter
empty fields because they are lazy?

-Sky

 

[Jeff Conrad]

I want to force users to use my form which has data validation to enter new
records into my table. The problem is, they can easily open the table and
input whatever they like, including blank records.

Is there a way to prevent users from opening the table directly to enter
empty fields because they are lazy?

A really good wedgie may do the trick, but failing that......

The only way to do this is to implement full blown Access User Level
Security (ULS). You would then deny all permissions on the tables
themselves and create RWOP queries for the users to access the table
information. Assigning appropriate permissions to various database objects
would also be needed.

You could also distribute MDE files (a good idea anyway) which would prevent
tampering with your code and prevent importing of forms, macros,
reports, and modules.

In addition you should create custom menu bars and toolbars to limit
what the users can do. Check under Tools | Startup and set limited
startup properties for your users. Hide the Database Window, select
a startup form, uncheck Allow Special Keys, etc. You may also want
to disable the Shift key bypass for those users who are wedgie resilient.
Details on that technique can be found here:

http://www.mvps.org/access/modules/mdl0011.htm

http://www.mvps.org/access/general/gen0040.htm

If you have never used ULS before, be prepared for a lot of work ahead
of you. ULS is not a trivial undertaking at all so I would recommend
ALL of the following reading material before beginning. Also, practice
on dummy databases until you are really comfortable with it.

Access User-Level Security:

Security FAQ (the Security Bible):
http://support.microsoft.com/?kbid=207793

Jack Macdonald's Security Document:
http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm

Joan Wild's Tips:
http://www.jmwild.com/security02.htm

The Security Whitepaper is also worth reading:
http://support.microsoft.com/?id=148555

Keith Wilby's Instructions (See step-by-step link)
http://www.keithwilby.com/

Other Good Information:
http://www.access-experts.com/default.aspx?selection=TutorialSecurity&sm=18

Other Microsoft KB articles of interest:

Description of how to help protect a Access 2000 database:
http://support.microsoft.com/?id=254372

Description of the role of workgroup information files in Access security:
ACC97: http://support.microsoft.com/?id=303941
ACC2000: http://support.microsoft.com/?id=305541
ACC2002/2003: http://support.microsoft.com/?id=305542

Exploring Microsoft Access Security:
http://msdn.microsoft.com/library/d...s/dnacc2k2/html/odc_AcSecurity.asp?frame=true

Good luck,

 

[Guest]

Jeff,

Thank you very much for the comprehensive listing related to Access
security. While reading through your reply message I realized things I never
knew could be done in Access. It seems like a large undertaking, but I don't
mind that at all because I think I'm becoming an Access junkie as well

In your listed references I recognized Lynn Trapp's name. I've seen her
posts in other Access forums (forms coding) etc.

I tried the user level security thing before, but got in over my head and
ended up locking even myself out of Access. I must be very careful while
proceeding along this road. The good thing though, I'll have a lot more
resources at my disposal should things go awry -- many thanks to you!

-Sky

 

[Jeff Conrad]

in message:

Hi Sky,

Comments inline....

Thank you very much for the comprehensive listing related to Access
security. While reading through your reply message I realized things I never
knew could be done in Access. It seems like a large undertaking, but I don't
mind that at all because I think I'm becoming an Access junkie as well

You're welcome for the list.
There are many levels of "locking people out." It all depends on how
Access-savvy your users are. Simply hiding the Database Window, unchecking
some of the options under Tools | Startup, and disabling the Shift key bypass
can usually keep out most people. You might experiment with just doing
that and see if that keeps your people from messing things up.

And don't forget the most obvious (and easiest) method: communcation.
Talk to your people and explain to them that they need to view and enter
data only through the forms. Get management behind you and discuss
consequences of not following directions.

As far as becoming an Access Junkie......exceeeeeeellent!!
Welcome to the Collective.....he...he....he....

In your listed references I recognized Lynn Trapp's name. I've seen her
posts in other Access forums (forms coding) etc.

Yes, Lynn is very, very knowledgable about Access security, Access
in general, and many other things. However, I do not think Lynn would
look very attractive in a dress. A kilt, maybe, but dress no. Many have
made this mistake in years past (myself included), but I think Lynn's
wife may take offense to you calling Lynn a "she."

I tried the user level security thing before, but got in over my head and
ended up locking even myself out of Access. I must be very careful while
proceeding along this road. The good thing though, I'll have a lot more
resources at my disposal should things go awry -- many thanks to you!

Access Security is very powerful, but it *IS* very complicated until
you get the hang of it. Don't feel bad about messing up the first time,
I'm sure all of us have been there before. The key to success is to educate
yourself first and then practice on dummy databases *several* times
until you really have it mastered. Then work on the key database(s),
but always keep good backups in case of a problem.

Remember you can always come here with questions when you
need help. There are no stupid questions here. An unasked question
is an unaswered one.

Best of luck in your project.

 

[Keith]

Yes, Lynn is very, very knowledgable about Access security, Access
in general, and many other things. However, I do not think Lynn would
look very attractive in a dress. A kilt, maybe, but dress no. Many have
made this mistake in years past (myself included), but I think Lynn's
wife may take offense to you calling Lynn a "she."

Well blow me down, I had also made that assumption. I really must have a
clearout of my preconceptions and stereotypes ;-)

Keith.

 

[onedaywhen]

don't forget the most obvious (and easiest) method: communcation.
Talk to your people and explain to them that they need to view and enter
data only through the forms. Get management behind you and discuss
consequences of not following directions.

If a database proves to be useful there may be a desire by management
to use the data in other 'reporting' tools and before you know it you
have the same user with the same permissions using the same database
but connecting directly from Excel. So another thing that gets
overlooked is that MS Access Forms should not be used in place of
proper constraints to maintain data integrity. If you haven't been
using ADO or Access2003 in 'ANSI-92' mode, take a look at the Jet 4.0
help files. There have been a number of enhancements, the most
significant of which IMO are CHECK constraints. Do everything you
possibly can to prevent bad data from entering your tables and don't be
afraid to duplicate your efforts in every front end you build
application you build.

Jamie.

--