How to Protect Your Sensitive Data with a Login Form in Access

Access Database Recovery, Access Solutions June 15, 2017

A login form is a must for databases that contain sensitive information. Follow the steps below so you can create your own login form to protect your database from invalid access.

Download Now

If you want to start to use the feature as soon as possible, then you can:

Download the Sample Database with VBA Codes Now

Otherwise, if you want to DIY, you can read the contents below.

Steps to Create a Login Form in Access

1. Create a login table

The first thing that we need to do is to create a table that will store all the login information.

  1. Create a new table and name it as tbl_login.
  2. Open the table in design view.
  3. Add the following fields with their corresponding data types:
Field Name Data Type
UserID AutoNumber
FirstName ShortText
LastName ShortText
UserName ShortText
Password ShortText

 

  1. Edit the password’s input mask so that it will be displayed as a series of asterisks (*).Edit The Password's Input Mask
  • Select the Password field.
  • Go to the General tab > Input Mask > …
  • When the Input Mask Wizard pops up, select Password.
  • Click Finish.
  1. Open the table in Data Sheet view and enter the first record (your login details).Enter Your Login Details
  2. Close the table and save the changes made.

2. Create the Login form

Create a blank form with 2 textboxes and 2 command buttons (as shown below). Skip the command button wizard.Create The Login Form

We are going to refer to these form controls in our code later so it would be best to rename them so that they can be easily identified:

  • txt_username
  • txt_password
  • cmd_login
  • cmd_cancel

Next, change the input mask of the password textbox so that it will also be displayed as a series of asterisks (*).

  1. While in design view, select the password textbox.
  2. Open the Property Sheet (ALT + Enter).
  3. Go to the Data tab > Input Mask.
  4. Select Password and click Finish.

Now, we are going to change some of the properties of the form for aesthetic purposes and most importantly, for making sure that users cannot bypass this form without entering the correct login details.

  1. While in design view, select the form (as shown below).Select The Form
  2. Open the Property Sheet (ALT + Enter) and update the following properties:
  • Under the Format tab
    • Caption: Login
    • Record Selectors: No
    • Navigation Buttons: No
    • Scroll Bars: Neither
    • Border Style: Dialog
    • Close Button: No
  • Under the Other tab
    • Pop Up: Yes
    • Modal: Yes
    • Shortcut Menu: No
  1. Save the form as frm_login.

3. Add the code

Add an On Click event to the Login button.

  1. While in design view, select the Login button.
  2. Go to the Property Sheet > Event Tab > On Click > …
  3. Select Code Builder from the Choose Builder menu and click OK.
  4. This will open the VBA Editor.
  5. Copy the code below.
Private Sub cmd_login_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
 
  If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbInformation, title:="Username Required"
    Me.txt_username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbInformation, title:="Password Required"
    Me.txt_password.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  strSQL = "SELECT FirstName FROM tbl_login WHERE Username = """ & Me.txt_username.Value & """ AND Password = """ & Me.txt_password.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="Login Error"
    Me.txt_username.SetFocus
  Else
    MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
    DoCmd.Close acForm, "frm_login", acSaveYes
  End If
 
 Set db = Nothing
 Set rst = Nothing

End Sub

This code will:

  1. Check if the username and password fields are not left blank. If they are, a prompt will appear.
  2. Check if the login details are correct by comparing them with the values in the login table (tbl_login).
  3. Once the login details are verified, a prompt will appear welcoming the user.

The next step is to add the code for the Cancel button so that once it is clicked, the database will close.

  1. Add an On Click event to the Cancel button (same procedure with the Login button).
  2. Copy the code below.
Private Sub cmd_cancel_Click()
   DoCmd.Quit acQuitSaveAll
End Sub

4. Set the Login Form as the Display Form

Finally, set the Login Form as the default form that users will see upon opening the database.

  1. Go to File > Options > Current Database.
  2. Set Display Form to frm_login.
  3. Click OK.

You will have to close the database and reopen it to see how it works.

Recover Your Access Database

Congratulations! You now know how to protect your precious database from invalid access. Now, I’m going to give you an extra tip. If, for an unfortunate reason, your database has been corrupted, DO NOT panic. DataNumen Inc., an expert in data recovery technologies, provides a software that repairs corrupted Access database. Your database can still be recovered.

Author Introduction:

Jayme Stack is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including sql recovery and excel recovery software products. For more information visit www.datanumen.com

Comments are closed.