In the first part of user-proofing your Access Database, we talked about some basic ways you can protect your data against well-intended but inexperienced users. While these methods won't keep out an experienced hacker, they will provide a level of safety similar to that provided when you childproof your home. This article contains more sophisticated tips appropriate when your users start going to Microsoft Access advanced training.

Startup and Shutdown Macros

Your users learn many exciting new features of the software in Microsoft Access advanced training, possibly too much for their own good. They can now find things like security features and macro code and may decide to "help" by changing some things. An AutoExec macro can reset security settings once a database is opened.

When a database is opened, Access looks for a macro named AutoExec. If it finds it, it automatically runs it before displaying the database. Create an AutoExec macro and have it call a startup function created by you which will examine and reset security settings to what you want them to be. For example, your startup function might set properties such as StartupShowDBWindow to false to prevent the database window from being displayed.

You can do something similar in a close task in order to immediately reset any security setting a user might have changed. A common use for this is to set AllowBypassKey to false to prevent users from using the Shift key to bypass the standard startup processes.

Error Handling

Microsoft Access advanced training introduces the concept of creating macros. These open up a new world of functionality and a new world of security problems. All but the simplest macros should include their own error handling to close one of these problems.

If a macro hits an error it displays an error message that includes a Debug option. A user fresh from Microsoft Access advanced training might decide to fix the macro so they click on the error message. However, even in the unlikely event the user can fix it, changes of this nature should go through the database administrator.

By including a simple error handler, the error is still reported but without the Debug security hole. The message can be phrased in more user friendly fashion than the default Access error message and include instructions unique to your organization on how to handle the problem.

Execute Only Databases

An extreme way to prevent changes is to create a front end that is execute-only. Under Database Tools you will find either Make MDE File or, for Access 2007, Make ACCDE File. These convert the database into an object that can only be executed, similar to the way a compiler converts interpreted code to complied code.

Users won't have access to objects or VBE code so can't make changes even if desired. Execute-only status doesn't protect many objects, such as tables or relationships, so is not complete protection against intrusion.