Friday, January 28, 2011

Microsoft Access Front-End Security. Part 1: Locking the Front End

In this week’s post I am assuming that you have split your database into two distinct files: one for the front end, which is what your users interact with, and one for the back end, which is where the data are actually stored. There are several things you can do to make you data and database more secure. I will discuss three of them here. First there is protecting the back end and your VBA code, Second is requiring a password to open the database, third is limiting certain functions to certain people.

Locking down the back end and VBA Code. By default anyone using Microsoft Access, even with a split database, can open up the tables and work directly in it. They can also open the VBA editor and alter the code. To prevent this you need to lock down the front end.

Important! Be sure to make a back-up copy of your deployed production front end and store it in a different directory. There may well be a way to unlock a front end, but I don’t know what it is. It’s safer to have a back up anyway.

In Access 2007 you lock down by going to the Office button, then select “Access Options” at the bottom. In Access Options choose “Current Database”. In Access 2003 go to “Tools”, “Start up”, “Advanced”. Now, unselect “Use Special Keys”, “Enable Layout View…”, “Enable design changes…”, “Allow Full Menus”, and “Allow Default Menu Shortcuts”. Then click “OK”.

When the database is next opened no user will be get to the back end tables except through the forms and will not be able to alter the forms or the VBA code.

Next week: Part 2: Requiring a password to open the database.

No comments:

Post a Comment