Creating Login Security using Access VBA

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:

AccessLevelID	AccessLevel
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 5, the ColumnWidths to 0;1;0;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.

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 in the RowSource is checked to see if the user is required to set a new 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. Alternatively you can save the value in a global variable and close the form.

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!”, vboOkOnly + vbExclamation
Me.txtPassword = Null
Me.txtPassword.SetFocus
End If
End If
End Sub
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 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 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.

© 2010 Scott B. Diamond — Diamond Computing Associates