User-Proofing Your Access Database: Part 1

Data is money to modern business and should be guarded from even casual intrusion. A few basic security steps will user-proof your database. This is not to say your users are deliberately destructive, only that a level of protection is comparable to childproofing your home. It acts as a safety barrier against innocent curiosity but is not protection against deliberate malice.

Out Of Sight, Out Of Mind

A little knowledge is a dangerous thing and basic Access training can convince your users they are database administrators. Hiding as much of the database is possible will cause the users to focus in your interface objects and interact with the database through them as intended.

Open the options menu introduced in Access training. Uncheck "Display Database Window" so that database itself remains invisible. Also uncheck "Use Access Special Keys" so users can't use F11 to bring up the hidden window. Under Access 2007, hide the Navigation Pane rather than the database.

Set the properties of non-interface objects such as tables and queries by opening their Properties windows and checking Hidden. The ultimate goal is to prevent users from seeing anything other than the objects they will need to do their jobs, but nothing more.

Split The Database

Another way to protect vulnerable parts of the database is to split it into a back end and a front end. Splitting is easily done using the Database Splitter wizard included in Access which was probably demonstrated in your Access training.

The back end will reside on the server. Anything that the users shouldn't touch such as tables or relationships should be on the back end, out of harm's way. There are other benefits to splitting the backend such as the fact the database will be included on the regular server backups.

The front end is the piece installed on each user's computer. This is a separate database that contains the interface object and communicates with the back end database through linked tables.

Don't Compact on Close

Database compaction is a critical maintenance function to keep your database clean, efficient and robust. It's so important that Access includes a Compact on Close function that will automatically perform this procedure every time a user finishes with a database. Unfortunately this is a giant security hole.

Compact on Close creates a number of temporary files on the user's computer and isn't efficient about cleaning those up. You can have all the password protection you like on the server containing the back end, but if copies of your information are in the temp directory of every client out there you have a problem. Don't automate compaction; do it manually then go in and clean up the temporary files afterward.

Again, these steps will do little to protect against deliberate intrusion but will go a long way to keep out well-meaning amateurs fresh from Access training from destroying your data with a single click.