Re: How do you make an access database foolproof ?

From: tina (nospam_at_address.com)
Date: 01/27/05 

Date: Thu, 27 Jan 2005 07:40:20 GMT

very nice, Jeff. besides being excellent advice, your list also highlights
the fact that users everywhere are alike! <g>

"Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
news:e9t5Xb$AFHA.2572@tk2msftngp13.phx.gbl...
> 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 spread*** 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.
> --
> Jeff Conrad
> Access Junkie
> Bend, Oregon
>
> "AkhPotter" <AkhPotter@discussions.microsoft.com> wrote in message
> news:248DAC00-C3B9-4C46-9F1E-6E547BA98AAB@microsoft.com...
> > Data in my Access database has to be inserted/updated by non technical
persons.
> > How can I prevent them from introducing unwanted/faulty changes/entries
or
> > from deleting existing data ?
>
>