UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Login Security    
Login Security

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:

AccessLevelID AccessLevel
1 Developer
2 Administrator
3 Editor
4 Reader

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:

SELECT tblUser.UserID, [Lname] & ", " & [FName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID
FROM tblUser
ORDER BY tblUser.LName, tblUser.FName;

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.

Private Sub txtPassword_AfterUpdate()

'Check that User is selected
If IsNull(Me.cboUser) Then
   MsgBox "You need to select a user!", vbCritical
   'Check for correct password
   If Me.txtPassword = Me.cboUser.Column(2) Then
       'Check if password needs to be reset
       If Me.cboUser.Column(3) Then
           DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.cboUser
       End If
       DoCmd.OpenForm "frmMainMenu"
       Me.Visible = False
       MsgBox "Password does not match, please re-enter!", vboOkOnly
       Me.txtPassword = Null
   End If
End If
End Sub

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:

Dlookup("[AccessLevel]","tblUser","[UserID] = " & Forms!frmLogin!cboUser)

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:

If Forms!frmLogin!cboUser.Column(4) <> 1 Then
   MsgBox “You are not authorized to open this form!”, vbOkOnly
   DoCmd.Close acForm, “formname”
End If

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

If Forms!frmLogin!cboUser.Column(4) = 3 Then
   Me.AllowEdits = False
   Me.AllowAdditions = False
   Me.AllowDeletes = False
   Me.AllowEdits = True
   Me.AllowAdditions = True
   Me.AllowDeletes = True
End If

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:

Me.Filter = “[UserID] = “ & Forms!frmLogin!cboUser
DoCmd.RunCommand acCmdApplyFilterSort

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 here: Media:Loginexample.zip

The zip file contains versions for both Access 2007 and 2002-2003. The password for each user is welcome.

There is more detailed information in this Code Archive post: db Login & Security

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 47,006 times.  This page was last modified 11:54, 14 August 2017 by BruceM. Contributions by Mark Davis, ScottGem, Jack Leach and Alan Greenwood and others  Disclaimers