First, one has to understand that Access is not the most secure platform. If you really need strong security on a database, you are better off using a more secure back end like SQL Server. But if you don’t need high level security, you can secure your database using VBA. This wiki will use a fairly simple example, but it should give you enough to expand on for more complex needs.

The first step is to set up the tables needed to drive the security. Two tables are needed:

• tblUser
  • UserID (PK Autonumber)
  • FName
  • LName
  • Password
  • PWReset
  • AccessLevelID (FK)

• tluAccessLevel
  • AccessLevelID (Integer)
  • AccessLevel

The second table is a lookup for the AccessLevelID in tblUser. For this example we will have four Access levels:

You can add more fields to tblUser if needed.

Once you have the tables setup, you can create the Login form. This will be an unbound form with two controls; a combobox to select the user and a textbox to enter the password. You will want to restrict this form by turning off the Min, Max and Close buttons. Also set the Navigation Bar and Record Selector properties to No. The suggested names are frmLogin for the form, cboUser for the combobox and txtPassword for the textbox.

For the combobox, you will use a rowsource of:

The user will then select their name from the combobox and then enter the password in the textbox. Now we get into the VBA that drives the security. This code will be entered in the After Update event of the password textbox.

The first task the code performs is to make sure a user has been selected. If, not focus is returned to the cboUser combo. The next step is to check if the password entered matches the stored password. If it doesn’t, focus is returned to the txtPassword control. If there is a match, then the Reset column is checked to see if this was a default password. If it is a form is opened to enter a new password. If Reset is false, then the main menu is opened. The login form is then hidden. This is so the cboUser control can be referenced in other parts of the application.

The Password reset form (frmPasswordChange) is bound to tblUser. So when it’s opened, it’s filtered for the selected user. Generally, I use unbound textboxes on that form so the user enters the new password twice. You use code to confirm that the passwords match. If they do, you populate a hidden textbox bound to the Password field so that the password is updated. Then open the main menu.

The final piece to this is using the AccessLevel to restrict access to various parts of the application. This is done by checking the Access level in one of two ways. You can reference the column in the combobox by using the expression:

or using the expression:

Once you have retrieved the Access level, you can use it to setup forms or other levels of access. Following are some examples of this:

Case 1: Restricting access to a form only to administrators In the On Open event of the form, you place code like:

Case 2: Making a form read only for Readers In the On Open event of the form you place code like:

Case 3: Restricting records on a form to ones assigned to the current user In the On Open event of the form you place code like:

These are just three possibilities. There are many other ways to use these techniques to restrict what a user can access in your application.

A sample of the techniques listed here can be downloaded at:

http://www.diamondassoc.com/sampleapps/loginexample.zip The zip file contains versions for both Access 2007 and 2002-2003. The password for each user is welcome.