Microsoft Access Front-End Security. Part 2: Requiring Authentication to Open the Database

If you need, or want, to limit who can open your database there are at least two ways of doing so. One is easy and passive, it looks at the user ID for the computer and either opens the database, or not depending on whether or not you have allowed that user access. The other takes a little more work and requires input from the user.

Passive Authentication:
You can use this method if you are working in an environment that requires users to log onto their computers. Each computer stores the user ID and that can be picked up in the VBA code. An AutoExec macro triggering a user defined function and a table containing the allowed user IDs will do the trick. First you must define your function. To do this you can either put it in an existing module, or create a new module for it, either way we’ll call this function “Authentication()”.

Public Function Authentication()

'This function runs from the AutoExec macro when the database is opened.

Dim strUser As String          'This is the user ID picked up from the computer.

Dim intUser As Integer         'This is derived from the table of allowed users tblUsers.

'Pick up the user ID with the Environ() function.

strUser = Environ("username")

'Assign either a 1 or 0 to intUser by comparing strUser with the IDs in tblUsers.

'the DCount() function will count the number of times strUser appears in tblUsers.

'Be sure to user the null function Nz() to account for the null value returned when

'strUser does not match any value in tblUsers and convert it to a 0.
'This way any user in tblUsers will have a value of 1 and anyone not in the table
'will have a value of 0.

intUser = Nz(DCount(“[User]”, “tblUsers”, “[User] =” & strUser & “”), 0)

'If intUser = 0 the user ID is not in tblUsers and not allowed to open the database.

'In that case tell the user they are not authorized and close the application.

'Otherwise allow the database to open.

      If intUser = 0 then

          If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

              DoCmd.Quit acQuitSaveNone

          Else:

              'Correct log in opens whatever form you want your users to start with.
              DoCmd.OpenForm "frmStartPage"

          End If

     End If

End Function

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter RunCode and under “Arguments” enter Authentication(). Save the macro. Now whenever the database is opened the function Authentication() will run before anything else happens.


Interactive Authentication:
This requires the user to enter a password, which can be either unique to each user or the same one for everyone. In either case you will want to mask the password as it is entered, so you will need to use a form instead of a function. For this type of authentication your users table, tblUsers, will have two columns. One column for the user name or ID and one for the password, can be the same for everyone. The form will need three text boxes. One text box for the username, one for the password, and a third one, set not to display, for the counter. There also needs to be a “Log In” button. The OnClick event for this button will run the following code:

Private Sub cmdLogIn_Click()

Dim strUser As String                      'This is the user name entered by the user.

Dim strPassword As String              'This is the password entered by the user.

Dim strAuthenticate As String           'This is the password from tblUsers.

Dim intCounter As Integer                'This keeps track of number of log in attempts.

intCounter = Nz(Me.txtCounter, 0)

'Me.txtCounter refers to a control on the Log In with its “visible” property set to “No.”

strUser = Me.txtUser

strPassword = Me.txtPassword

strAuthenticate = Nz(DLookup("[Password]", "tblUsers", "[User] = '" & strUser & "'"), “None”)

If strAuthenticate = “None” Then

       'Is someone not included in the users table attempts to log in close the database.

       If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

       DoCmd.Quit acQuitSaveNone

       End If

Else:

       If strPassword = strAuthenticate Then

             'Correct log in opens whatever form you want your users to start with.

             DoCmd.OpenForm "frmStartPage"

             'Then close the Log In form.

             DoCmd.Close acForm, "frmLogIn"

       Else:

             Me.txtUser = strUser

             Me.txtPassword = ""

             Me.txtPassword.SetFocus

             intCounter = intCounter + 1

            Me.txtCounter = intCounter

            'After three incorrect log in attempts close the database.

            If intCounter = 3 Then

                 If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

                      DoCmd.Quit acQuitSaveNone

                End If

            Else:

                MsgBox "Incorrect Log In. Please try Again"

           End If

      End If

End If

DoCmd.Close acForm, "frmLogIn"

End Sub

To mask the password as it is typed you will need an On Load event for the form:

Private Sub Form_Load()

       Me.txtUser.InputMask = "Password"

End Sub

If you would like the password to be case sensitive replace
If strPassword = strAuthenticate Then
With
If CBool(InStrB(strPassword, strAuthenticate)) Then

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter OpenForm and under “Arguments” enter frmLogIn. Save the macro. Now whenever the database is opened the log in form will open before anything else happens.

Of course, a user could just close or minimize the log in form, but if you had locked down the front end as described in the last post they would not be able to open any forms or interact with any of the tables.

Next week: Part 3: Authentication at the individual control level.