ACC2000: How to Create a Password Protected Form or Report

This article was previously published under Q209871
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

Microsoft Access has two built-in security features for protecting yourdatabase:
  • User/Group accounts and permissions
  • Database passwords
This article shows you how you can also set individual passwords for eachform and for each report in your database.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Using Code to Password Protect a Form

By using code, you can prompt for a password when a user opens a form or areport. If the correct password is entered, the form or the report is opened.

The following example shows you how you can password protect the Ordersform in the sample database Northwind.mdb:
  1. Start Access and then open the sample database Northwind.mdb.
  2. Press ALT+F11 to start the Microsoft Visual Basic editor.
  3. On the Insert menu, click Module.
  4. In the module sheet, type the following procedure:
    Public MyPasswordPublic Function KeyCode(Password As String) As Long   ' This function will produce a unique key for the   ' string that is passed in as the Password.   Dim I As Integer   Dim Hold As Long   For I = 1 To Len(Password)      Select Case (Asc(Left(Password, 1)) * I) Mod 4      Case Is = 0         Hold = Hold + (Asc(Mid(Password, I, 1)) * I)      Case Is = 1         Hold = Hold - (Asc(Mid(Password, I, 1)) * I)      Case Is = 2         Hold = Hold + (Asc(Mid(Password, I, 1)) * _            (I - Asc(Mid(Password, I, 1))))      Case Is = 3         Hold = Hold - (Asc(Mid(Password, I, 1)) * _            (I + Len(Password)))   End Select   Next I   KeyCode = HoldEnd Function					
  5. Press ALT+F11 to return to Access.
  6. In the Database window, under Objects, click Tables, and then click New.
  7. In the New Table dialog box, double-click Design View.
  8. Create a new table as follows:
       Table: tblPassword   ---------------------------   Field Name: ObjectName      Data Type: Text      Field Size: 50   Field Name: KeyCode      Data Type: Text      Field Size: 25      Input Mask: Password   Table Properties: tblPassword   -----------------------------      PrimaryKey: ObjectName					
  9. Open the tblPassword table and then enter the following data:
         ObjectName: Orders       KeyCode: 2818					
  10. Create a new form in design view and save the form as frmPassword.
  11. Add a single textbox to frmPassword called Text0, and a command button called CheckPassword.
  12. Set the Input Mask property of Text0 to "PASSWORD" (minus the quotation marks).
  13. Add the following code to the OnClick Event of the CheckPassword button and then save the form:
    If IsNull(Forms!frmPassword!Text0.Value) Then                 MsgBox "You cannot enter a blank Password. Try again."                 Me!Text0.SetFocus            Else                 MyPassword = Me!Text0.Value                 DoCmd.Close acForm, "frmPassword"            End If 					
  14. Open the Orders form in Design view.
  15. If the property sheet is not visible, click Properties on the View menu.
  16. Type the following event procedure in the module for the OnOpen property of the form:
    Private Sub Form_Open(Cancel as Integer)   Dim Hold As Variant   Dim tmpKey As Long   Dim I As Integer   Dim rs As DAO.Recordset   Dim db As DAO.Database   On Error GoTo Error_Handler   ' Prompt the user for the Password.   DoCmd.OpenForm "frmPassword", acNormal, , , , acDialog            Hold = MyPassword' Open the table that contains the password.   Set db = CurrentDb   Set rs = db.OpenRecordset("tblPassword", dbOpenTable)   rs.Index = "PrimaryKey"   rs.Seek "=", Me.Name   If rs.NoMatch Then      MsgBox "Sorry cannot find password information. Try Again"      Cancel = -1   Else      ' Test to see if the key generated matches the key in      ' the table; if there is not a match, stop the form      ' from opening.      If Not (rs![keycode] = KeyCode(Cstr(Hold))) Then         MsgBox "Sorry you entered the wrong password." & _            "Try again.", vbOKOnly, "Incorrect Password"         Cancel = -1      End If   End If   rs.Close   db.Close   Exit SubError_Handler:   MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number   Exit SubEnd Sub					
  17. Close and then save the Orders form.
  18. Open the Orders form and then type PASSWORD when you are prompted for a password.

    Notice that the Orders form opens. The KeyCode that is generated by PASSWORD matches the KeyCode in the tblPassword table, and is dependent on the case of the letters in the password entered.
  19. Close and then reopen the Orders form and then type PassWord when you are prompted for a password.

    Notice that you receive the message:
    Sorry you entered the wrong password. Try again.
    The Orders form does not open because the password procedure is case-sensitive.
  20. To determine what the corresponding KeyCode is for a particular string, type thefollowing in the Immediate window and then press ENTER:
    The earlier example returns 5864.
  21. To hide the tblPassword table in the Database window, right-click the tblPassword table, and then click Properties. In the Properties window, click to select the Hidden check box, and then click OK.

Article ID: 209871 - Last Review: 12/05/2015 11:13:43 - Revision: 3.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto KB209871