---Posted by Vinod Kumar--- 27/3/2001
ACC2000: How to Create a Password Protected Form or Report
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.
CAUTION: Following the steps in this example will modify the sample
database Northwind.mdb. You may want to back up the Northwind.mdb file and
perform 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 ensure that the Microsoft DAO 3.6 Object Library
check box is selected.
By using code, you can prompt
for a password when a user opens a form or a report. If the correct password is
entered, the form or report is opened.
The following example shows you how you can password-protect the Orders form in
the sample database Northwind.mdb:
Start Microsoft Access and open the sample database Northwind.mdb.
Start the Visual Basic editor. (Press ALT+F11.)
On the Insert menu, click Module.
In the module sheet, 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
Press ALT+F11 to return to Microsoft Access.
In the Database window, under Objects, click Tables and then click New.
In the New Table dialog box, double-click Design View.
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
Open the tblPassword table and then enter the following data:
ObjectName: Orders
KeyCode: 2818
Open the Orders form in Design view.
If the property sheet is not visible, click Properties on the View menu.
Type the following event procedure in the module for the form's OnOpen property:
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
' 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
Close and save the Orders form.
Open the Orders form and then type PASSWORD
when prompted for a password.
Note that the Orders form opens. The KeyCode generated by PASSWORD
matches the KeyCode in the tblPassword table, and is dependent upon the case
of the letters in the password entered.
Close and reopen the Orders form and type PassWord
when prompted for a password.
Note that you receive the message:
Sorry you entered the wrong password. Please try again.
The Orders form does not open because the password procedure is case sensitive.
To determine what the corresponding KeyCode is for a given string, type the following into the Immediate window and press ENTER:
?KeyCode("TestString")
The
above example returns 5864.
To hide the tblPassword table in the Database window, right-click on the
tblPassword table, and then click Properties. In the Properties window,
click to select the Hidden check box, and then click OK.
Additional query words: password