Startup Password to access switchboard

[Guest]

I have a situation where I want to just have a password to access the
switchboard of my database. I've created a database that I want to be able
to distribute to people, and I am disabling all the menus, but I want them to
be able to password protect their database because it will hold privacy act
data. I have found the following post that was helpful, but I want to modify
it by including a button to change the password. Any ideas?
Make a backup of your database BEFORE beginning!

1. Create a new small password form with the following properties:

-Scroll Bars No
-Record Selectors No
-Navigation Buttons No
-Dividing Lines No
-Auto Center Yes
-Pop Up Yes
-Modal Yes
-Border Style Thin
-Control Box No
-Min Max Buttons None
-Close Button No
-Shortcut Menu No

Name the form frmPassword.

Add a text box on this form called txtPassword with an Input Mask
of "Password" (without the quotations).

Add a label called lblPassword and have the caption say
something like "Please Enter Administrator Password."
Position just above the text box.

Add a command button called cmdCloseForm that simply
closes the form; nothing else in that code. Like so:

'Code Start
Private Sub cmdCloseForm_Click()
On Error GoTo ErrorPoint

DoCmd.Close acForm, "frmPassword"

ExitPoint:
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
' Code End

Add another command button called cmdShowAdminArea with
a caption of "OK" or something similar and enter the following code
into the Click event for this button:

'Code Start
Private Sub cmdShowAdminArea_Click()
On Error GoTo ErrorPoint

If Me.txtPassword <> "password" Then
' Substitute with your own password between the quotes
MsgBox "Incorrect Password", vbExclamation, "Access Denied"
DoCmd.Close acForm, "frmPassword"
Else
Forms!Switchboard.Filter = "[ItemNumber] = 0 And [SwitchboardID] = 2"
Forms!Switchboard.Refresh
DoCmd.Close acForm, "frmPassword"
End If

ExitPoint:
Exit Sub

ErrorPoint:
' Unexpected Error
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint

End Sub
' Code End

This assumes that the Admin Switchboard area you want to
protect is number "2" in the Switchboard Items TABLE. Make
sure to change that number to whatever matches your table
value. This also assumes that the NAME of your switchboard
form is actually just "Switchboard." Change the code if
you have changed the name. Dress up the message box with
whatever formatting and/or messages you desire.

Use the Switchboard Manager Wizard to create the entry to
go to this Admin area. Instead of using the option that
opens up a Switchboard, make it open the frmPassword
instead. Like this:

Edit Switchboard Item:
Text: Go To Admin Area
Command: Open Form In Add Mode
Form: frmPassword

Now when the person clicks on the Switchboard option that
says "Go To Admin Area" the Password form opens up on top.
Unless they enter the correct password, the Switchboard
will not change to show the Admin menu. It would also be a
good idea to hide the Database Window from the users.
Hope that gets you going,

 

[Guest]

I think it may be easier to create a different form that is password
protected. How do I go about creating a form to update the password, though?
I don't want to use ULS because I am creating this database to be
distributed to different people, and I don't know who ultimately will end up
with a copy, but I do want to provide the ability to require a password to
enter the database. Any thoughts?

 

[Scott McDaniel]

I think it may be easier to create a different form that is password
protected. How do I go about creating a form to update the password, though?
I don't want to use ULS because I am creating this database to be
distributed to different people, and I don't know who ultimately will end up
with a copy, but I do want to provide the ability to require a password to
enter the database. Any thoughts?

Are you referring to a Database password (i.e. one that Access handles) or are you going to code this yourself?

If it's the Access database password, then you can change it like this:

Function AlterPassword(NewPassword as String, OldPassword As String) As Boolean
CurrentProject.Connection.Execute "ALTER DATABASE PASSWORD '" & NewPassword & "' '" & OldPassword & "'"
End Function

Call it like this:

AlterPassword Me.ctlYourNewPasswordTextbox, Me.ctlYourOldPasswordBox

If this is something you're coding on your own then how you change it woudl depend on your table structure, but
basically you'd just update the table storing that value:

CurrentProject.Connection.Execute "UPDATE SomeTable SET YourPasswordField='" & Me.ctlYourNewPasswordTextbox & "'"






Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

I'm not storing the password in a table. Should I be? If so, how would I go
about doing that? Right now the password is stored in the code of form which
I know is not secure, but my users have zero knowledge of access (of which
I'm only doing slightly better then them).

 

[Scott McDaniel]

I'm not storing the password in a table. Should I be? If so, how would I go
about doing that? Right now the password is stored in the code of form which
I know is not secure, but my users have zero knowledge of access (of which
I'm only doing slightly better then them).

Actually it's probably easier for your users to get it from a table than from form code. The problem with storing it in
the form's code is it's hardcoded; to change the password, you'd have to alter the code. Storing it in a table allows
you to dynamically change the password, but it also allows any user to view the password by just viewing the table.

If all you're looking for is a password protected database, then just use the builtin Access feature (Tools - Security -
Set Database Password). There are some drawbacks (if a user changes the password, then all other users would be locked
out) but it's the simplest method.

If you'd prefer to store it in a table, then do this:

1) Build a table to store the password; name it tSettings and add two fields: sSettingName and sSettingValue, both of
them Text fields set to 255 characters.

2) Add a form (frmPassword) to your database, and set it to be the startup form (see the Startup options) Add a textbox
(txtPassword) and a button (cmdOpen)

3) In the click event of your button:

Sub cmdOpen_Click()
IfNz( DLookup("sSettingValue", "tSettings", "sSettingName='Password'"),"")=Me.txtPassword then
<password verified, open your main menu or form>
DoCmd.Close acForm, "frmPassword"
Else
Msgbox "Invalid Password"
End If
End Sub

Before shipping, prefill tSettings with a record:

sSettingname= "Password"
sSettingValue= "Admin"

Now tell your enduser that the default password is Admin ... you'd also want to provide them a utility to change their
password, which would be something as simple as a form with textbox and a button to save the change to tSettings:

Sub YourButton_Click()
Currentdb.Execute "UPDATE tSettings SET sSettingValue='"& Me.YourTextboxName & "' WHERE sSettingName='Password'"
End Sub



Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

When I put your code in the on_click event, I keep getting a compile error
expect: end of statment with "then" highligted.

IfNz( DLookup("sSettingValue", "tSettings",
"sSettingName='Password'"),"")=Me.txtPassword then
<password verified, open your main menu or form>

 

[Guest]

nevermind. the nz() function was missing it's parantheses.

 

[Guest]

If I wanted the form for changing the password to require the user to enter
the current password as well as have a text box to confirm the new password
they created, how would I do that? How about a dialog box saying they have
successfully changed their password?

 

[Scott McDaniel]

Sub YourButton_Click()
Currentdb.Execute "UPDATE tSettings SET sSettingValue='"& Me.YourTextboxName & "' WHERE sSettingName='Password'"
End Sub

If I wanted the form for changing the password to require the user to enter
the current password as well as have a text box to confirm the new password
they created, how would I do that?

Add a textbox (txtVerify), then compare the value they enter with the stored password before the UPDATE line:

If Nz(DLoopup("sSettingValue", "tSettings", "sSettingName='Password'"),"") = Me.txtVerify Then
'/now do the update
Currentdb.Execute "UPDATE tSettings SET sSettingValue='"& Me.YourTextboxName & "' WHERE sSettingName='Password'"
Msgbox "Your password has been updated
DoCmd.Close acForm, "NameOfYourForm"
Else
Msgbox "Your existing password could not be verified."
Me.txtVerify.SetFocus
End If

How about a dialog box saying they have

successfully changed their password?

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com