password restricted forms

Question

Hi Everyone

I have a DB to create and keep track of purchase orders, in this DB I created a form that is password protected and is just accessed by the CIO.  The purpose of the form will allow the CIO to approve the purchase orders and that is why I made it password protected, However so far this password was created by me and it resides in a table.  I would like to create an option so that CIO can create his own password.

Can someone give me an idea of how to do this?

Thanks very much in advance.

Answer 1

Regardless of the password issue, each user should have their own front end, either on their local machine or in a 'personal' folder in the system.  The tblPassword table should reside in the front end not the back end, so the password will be specific to each user.

I don't think there is any way of getting the text entered into an input box to show as asterisks.  It's done by the InputMask property of a column (field) or control, so you'd have to use a dialogue form rather than calling the InputBox function.  I have done this before, so I'll put together a little demo file and post the link here when it's available.  The approach I've used is not to store the password in a table but as a property of the form.  Bear in mind that however you do it, it will only provide a very low level of security.  In reality it hardly merits the term 'security' at all; it's really just a means of controlling who has Access to what in the day-to-day operation of the database.  Any moderately competent meddler could crack it very easily.

Answer 2

Thanks Ken

 I entered the code in the on open event of the passaword protected form and it looks like it works until some extend, is perfect but it works for just one user, in other words if I enter the password of another user it tells me invalid password.  I also understand what you mean as I had set the passwords in the table to show as asterisks but how can I make the password shows the same way as the user is entering it?

Answer 3

In which event procedure are you prompting for the password, and what is the code?  For a simple solution you could use code in the form's Open event procedure like this:

Private Sub Form_Open(Cancel As Integer)

    Const MESSAGETEXT = "Invalid password"

    Dim strPassword

    strPassword = DLookup("Password", "tblPassword")

    If StrComp(InputBox("Enter password:", "Password"), strPassword, vbBinaryCompare) <> 0 Then

        MsgBox MESSAGETEXT, vbExclamation, "Password"

        Cancel = True

    End If

End Sub

but a better alternative would be to open an unbound form in dialogue mode rather than calling the InputBox function as this would allow you to format the prompt as a password so that it shows asterisks rather than the actual characters entered.

Answer 4

Hi Ken and thank you so much for your help your code worked perfectly as fas as changing the password however I have a form that is password protected and supposed to use this password to access it.  The problem is when I try to open this password protected form and  I get the password prompt if I dont' type anything the form opens with no information on it which is ok but if I type anything whether is the right password or not the form opens with all the information.

Any suggestions?

Answer 5

You can include this in the same form by means of an unbound text box, txtPassword say.  In bare outline the code in its AfterUpdate event procedure, if we assume that the table is named tblPassord and has a single row with a single column named Password, would be like this:

Const MESSAGETEXT = "Password updated."

Dim strSQL As String

strSQL = "UPDATE tblPassword SET Password = """ & Me.txtPassword & """"

If Not IsNull(Me.txtPassword) Then

CurrentDb.Execute strSQL, dbFailOnError

MsgBox MESSAGETEXT, vbInformation, "Change Password"

End If

You might want to develop this further so that the user has to confirm the current password before changing it, in case they leave the form open and someone else maliciously changes the password.

Whatever you do, this sort of password protection provides only a very low level of security of course, and is very easily subverted by anyone with a modest knowledge of how MS Access works.