ACC: How to Create a Password Protected Form or Report

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Microsoft Access has two built-in security features for protecting your database: User/Group accounts and permissions, and database passwords.

This article shows you how you can also set individual passwords for each form and report in your database.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Open the sample database Northwind.mdb.



2. Create a module and type the following procedure:




      Public 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 = Hold
      End Function 

3. Create a new table as follows:




      Table: tblPassword
      ---------------------------
      Field Name: ObjectName
         Data Type: Text
         Field Size: 50
      Field Name: KeyCode
         Data Type: Number
         Field Size: Long Integer
         Input Mask: Password

      Table Properties: tblPassword
      -----------------------------
      PrimaryKey: ObjectName 

4. Open the tblPassword table and enter the following data:




      ObjectName: Orders
      KeyCode: 2818 

5. Open the Orders form in Design view.



6. Set the form's OnOpen property to the following event procedure:




      Private Sub Form_Open(Cancel as Integer)
         Dim Hold As Variant
         Dim tmpKey As Long
         Dim I As Integer
         Dim rs As Recordset
         Dim db As Database

         On Error GoTo Error_Handler
         ' Check to see if the user is passing in the Password.
         If IsNull(Me.OpenArgs) Then
            Hold = InputBox("Please Enter Your Password", "Enter Password")
         Else
            Hold = Me.OpenArgs
         End If
         ' 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 info. Please 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.  " & _
                  "Please try again.", vbOKOnly, "Incorrect Password"
               Cancel = -1
            End If
         End If
         rs.Close
         db.Close
         Exit Sub

      Error_Handler:
         MsgBox Err.Description, vbOKOnly, "Error #" & Err.Number
         Exit Sub
      End Sub 

7. Close and save the Orders form.



8. Open the Orders form and enter "PASSWORD" when prompted for a password. Note the Orders form opens.



9. Close and reopen the Orders form and enter "PassWord" when prompted for a password.
   
   Note that you receive the message "Sorry you entered the wrong password. Please try again." and the Orders form does not open.

How to Change a Form or a Report Password

1. Type the following line in the Debug window, and then press ENTER:

   ?KeyCode("TestString")

   The function returns the value 5864, which will be the new KeyCode.



2. Open the tblPasswords table in Datasheet view; remember, it may be hidden.



3. Find the record that you created where ObjectName = Orders.



4. Type 5864 into the KeyCode field.



5. Close the tblPassword table.

Additional query words: password-protected pass word

Keywords : OffVBA FmsHowto PgmHowto ScrtOthr
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto