Microsoft Access Back-End Security.

Now that your front-end is secure you’ll want to make sure that users can’t just go to the directory and open the back-end and alter data. Here I learned something new: I had thought it was impossible to mask an input box, but some code written by Daniel Klann allows you to mask the password in an input box. I found the code for this at: http://www.tek-tips.com/faqs.cfm?fid=4617

The beauty of using an input box is that the user can interact only with the input box until log in is successful. Nothing else in the application will work while the input box is open and the database will close if the input box is closed or canceled. Using the input box also makes the password case sensitive.

To secure your back-end you can either put Mr. Klann’s code in an existing module, or in a new module. I made a new module and named it basLogIn so that I can just import it into other databases as needed. Then add the following function:

Public Function LogIn()

Dim strPassword As String

'Pick up the password from the first line in tblUsers.

strPassword = DLookup("[Password]", "tblUsers", "[ID] = 1")

'You can just replace the DLookUp() function with the password like this:

'strPassword = "Password"

'But by putting in a table you will be able to change it from the front end

'using a "change password" accessible only to administrators.

'Debug.Print "strPassword = " & strPassword

If InputBoxDK("enter password", "Log In") = strPassword Then

      'A successful log in closes the input box and allows the user to open tables.

Else:

      'An unsuccessful log in gives the user a second chance to log in.

      MsgBox ("Sorry, Incorrect Password" & vbNewLine & "Please enter Password")

      If InputBoxDK("enter password", "Log In") = strPassword Then

      'A successful log in closes the input box and allows the user to open tables.

      Else:

            'An unsuccessful log in gives the user a third chance.

            MsgBox ("Sorry, Incorrect Password" & vbNewLine & "Please enter Password")

            If InputBoxDK("enter password", "Log In") = strPassword Then

            'A successful log in closes the input box and allows the user to open tables.

            Else:

            'No more chances.

            'An unsuccessful log in closes the application.

            MsgBox ("Sorry you are not authorized to access this database.")

            DoCmd.Quit

            End If

      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 LogIn(). Save the macro. Now whenever the database is opened the function LogIn() will run before anything else happens.

Now I may have to go back and rework some of my front-end security to use input boxes instead of forms, all thanks to the excellent work of Daniel Klann.