Login Security using Access VBA

by Scott Diamond 2. April 2011 10:35

First, let me state 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. I’ll be using a fairly simple example for this article, but it should give you enough to expand on for more complex needs.

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

tblUser:

UserID (PK) Autonumber
FName Text
LName Text
Password Text
PWReset Yes/No
AccessLevelID Long Integer

 

tluAccessLevel:

AccessLevelID (PK) Autonumber
AccessLevel Text

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

AccessLevelIDAccessLevel
1 Developer
2 Administrator
3 Editor
4 Reader

You can use whatever and how many levels you want or need depending on your application. The above is provided as a basic example. You can also 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. I suggest the name frmLogin for the form, cboUser for the combobox and txtPassword for the textbox.

For the combobox, you will set the ColumnCount to 4, the ColumnWidths to 0;1;0;0 and the RowSource property to the following SQL statement or create a named query:

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

 

The user will select their name from the combobox and then the password in the textbox. Now we get into the VBA that drives the security. This code will be entered in the AfterUpdate 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
    Me.cboUser.SetFocus
Else
    ‘Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
        ‘Check if password needs to be reset
        If Me.cboUser.Column(4) Then
            DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.cboUser
        End If
        DoCmd.OpenForm "frmMainMenu"
        Me.Visible = False
    Else
        MsgBox "Password does not match, please re-enter!", vbOkOnly or vbExclamation
        Me.txtPassword = Null
        Me.txtPassword.SetFocus
    End If
End If
End Sub

 

The first task the code performs is to make sure a user has been selected (line 4). If, not focus is returned to the cboUser combo (line 6). The next step is to check if the password entered matches the stored password (line 9). If it doesn’t, we display a message (line 17) and focus is returned to the txtPassword control. If there is a match, then the Reset column in the RowSource is checked (line 11) to see if the user is required to set a new password. If it is, a form is opened to enter a new password (line 12). If Reset is false, then the main menu is opened (line 14). The login form is then hidden. This is so the cboUser control can be referenced in other parts of the application. Alternatively you can save the value in a global variable and close the form.

The Password Reset form (frmPasswordChange) is bound to tblUser. So when it’s opened, it’s filtered for the selected user.  The user then enters the new password twice to confirm it. Then we 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:

Forms!frmLogin!cboUser.Column(4)

or using the expression:

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

Once you have retrieved the Access level, you can use it to restrict access to forms and controls based on the access level assigned to the user. 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) <> 2 Then
    MsgBox “You are not authorized to open this form!”, vbOkOnly +vbExclamation
    Cancel = True
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) = 4 Then
    Me.AllowEdits = False
    Me.AllowAdditions = False
    Me.AllowDeletes = False
Else
    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
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.

I hope you find these tips useful.

You can download a sample of this here:

loginexample.zip (60.56 kb)

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

Tags:

Pingbacks and trackbacks (1)+

Comments are closed

About the author

Access Security Blog is a collaborative effort of several people, including Tom van Stiphout, Scott Diamond, Patrick Wood, and Paul Bardinelli.