Well there is no way to completely eliminate the human factor
in the equation (AKA the "McFly Factor"). People will make
mistakes with any software application, not just databases.
Ohh the stories I could tell....
I have extensive experience with non-technical people so these
are some guidelines I would follow.
1. Patience, patience, patience!
Realize that problems will happen and just deal with them one at a time.
Look on the bright side and remember that problems can be somewhat
of a job security thing. Always nice to have the paycheck keep coming
and you look good too!
I have one person from a store site that whenever there is a problem she
always starts by saying, "Jeff, how much do you love me?" I always
respond with, "I love that you keep me employed. What can I do for you?"
2. Make sure all of the data entry for the database is through forms. PERIOD!
Do NOT expose the tables to anyone. I cannot emphasize this point enough.
An Access database is not a spreadsheet so you should never let data entry
people enter information directly into a table. You have much less control
over what the person will do versus using a form. Forms have events that
you can use to "keep on eye on what the person is doing."
3. Make sure you have created all Relationships and Indexes properly. If
you cannot have duplicate Employee Numbers for instance, then you better
make sure you define that in Table Design View. The Jet Engine will be
there to back you up so nothing slips by. Creating Relationships correctly
between 1-M tables, for example, can eliminate Orphan records.
4. Learn to use the Form's BeforeUpdate event. This event allows you the
developer to "check" on things just before the user wants to move onto
something else. Think of this as your first line of defense and the Jet Engine
as the second (and most powerful).
5. Make sure you have proper error handling in every procedure. This can
be as generic or as sophisticated as you want. A simple message of,
"Oh oh, call Tech Support" is much easier for a user to understand than
the error messages that Access will kick out.
6. Similar to above, make sure you have pleasant, easy-to-understand
messages throughout the application when the user does something wrong.
I am not referring to run-time error messages here. If a user, for example,
selects an eight day time frame and it has to be seven, present a nice message
box to them and explain what they need to do to correct the problem. I
have a personal goal that users should never, ever see a built-in Access
message. Takes a lot of work, yes, but well worth the trouble for a more
*polished* looking application.
7. Create custom menu bars and toolbars and hide all the built-in Access ones.
If you are concerned about the users hitting the "Delete Record" button on
the toolbar, well don't expose it to them! Out of sight, out of mind.
8. Teach ALL the users the awesome power of the Escape key! It reigns
supreme over all other keys. Do NOT keep this vital information just to
yourself.
9. Set limited startup options under Tools | Startup. Do not expose the Database
Window to the users and do not tell anyone about the Shift key bypass. If
you have users aware of this feature, then use code to turn it off. (It is
possible to do this).
10. Test, test, test and then test some more! I spend hours and hours of
time trying to think of *every* possible thing a user could do. You would
be surprised by what a user *could* do AND what you may have missed.
Have someone else test the database as well and watch them. Again, you
may be surprised at what you find.
11. Backups are your best friend.
When people ask me, "How often should I back up my work?" I simply
reply back with a question of my own: "How much work are you willing to re-do?"
12. Applying full-blown Access User Level Security to a database will
not make it "fool-proof." That is not the intention of ULS at all. If you
need more control over where users can go, need to hide sensitive information,
etc, then Yes, this is the way to go. Just make sure you completely
understand ULS by reading up on the subject and practice on dummy
databases before beginning.
13. Split the database into a Front End (FE) and Back End (BE). Lots
of additional problems will be eliminated by doing this.
14. Distribute only MDE files to your users. Do you really want them messing
up your code and then complaining to you when it does not work?
I hope that helps answer your question.
