ACC: How to Change User Passwords Programmatically Using DAO

• Microsoft Access versions 7.0, 97

SUMMARY

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

At times, you may want to reset or change a user's password without using the user interface. This article demonstrates several sample functions that you can use to accomplish this task.

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

Resetting or Changing a User's Password When Password Has Been Forgotten

1. Open your database in Microsoft Access 7.0 or 97.



2. In the Database window, click the Modules tab, and then click New.



3. Type or paste the following code in the module that you have just created:




    Public Sub ChangeResetPassword(StrAction As String, StrUsername As _
    String, StrAdminLogon As String, StrAdminPass As String, Optional _
    StrNewPassword As Variant)

    Dim ws As Workspace
    On Error GoTo ChangeResetPassword:

    ' Create a new Administrative Workspace. If The StrAction passed to the
    ' function is "Change" then change the Password of the User named in
    ' StrUsername to the password saved in StrNewPassword.
    ' If the StrAction passed is "Reset", Then reset the password of
    ' the User mentioned in StrUsername. If neither "Change" or "Reset"
    ' is passed to the function in the StrAction argument, inform the
    ' user of an error and exit the procedure.

    Set ws = DBEngine.CreateWorkspace("AdminWorkspace",StrAdminLogon, _
    StrAdminPass)
    If StrAction = "change" Then

         If Not IsNull(StrNewPassword) Then
            ws.Users(StrUsername).NewPassword "", StrNewPassword
            MsgBox "Password Change Successful", vbOKOnly
         Else
             MsgBox "When Attempting to Change A User's Password, You " & _
               "Must Include a New Password", vbOKOnly
         End If

    ElseIf StrAction = "reset" Then
        ws.Users(StrUsername).NewPassword "", ""
        MsgBox "Password Successfully Reset", vbOKOnly
    Else
        MsgBox "You must Select a StrAction of either '" & "Change'" & _
          "' or '" & "Reset'.", vbOKOnly
    End If

    ws.Close
    Set ws = Nothing
    Exit Sub

    ChangeResetPassword:
         MsgBox Err.Description
   End Sub 

4. Save the module as SecurityCode and close it.

Allowing a User to Change His or Her Own Password

1. Open your database in Microsoft Access 7.0 or 97.



2. In the Database window, click the Modules tab, and select the SecurityCode module that you created in the example above.



3. Click Design, and type or paste the following code:




    Public Sub ChangeUserPassword(StrUsername As String, StrOldPassword _
    As String, StrNewPassword As String)

    On Error GoTo ChangeUserPassword_Err:
    DBEngine(0).Users(StrUsername).NewPassword StrOldPassword, _
      StrNewPassword

    MsgBox "Password Change Successful", vbInformation
    Exit Sub

    ChangeUserPassword_Err:
        MsgBox Err.Description

    End Sub 

4. Save the Module and close it.

Usage Example

   - Change their own password. 

   - Reset a user's password.
   - Change a user's password. 

1. Create a new blank form and save it as frmChangePasswords.



2. With the frmChangePasswords form open in Design View, add the following text box, label, and command button controls. Each label is associated with a specific text box and should be placed near the text box on the form.




      Text Box:
      -----------------
      Name: txtUserName

      Label:
      --------------------
      Name: lblUserName
         Caption: UserName

      Text Box:
      -----------------------
      Name: txtOldPassword
         Input Mask: Password

      Label:
      ------------------------
      Name: lblOldPassword
         Caption: Old Password

      Text Box:
      ------------------------
      Name: txtNewPassword
         Input Mask: Password

      Label:
      ------------------------
      Name: lblNewPassword
         Caption: New Password

      Text Box:
      -----------------------
      Name: txtVerifyPassword
         Input Mask: Password

      Label:
      ---------------------------
      Name: lblVerify Password
         Caption: Verify Password

      Text Box:
      -----------------------
      Name: txtAdminUsername
         Input Mask: Password

      Label:
      -----------------------------------
      Name: lblAdminUsername
         Caption: Administrative UserName

      Text Box:
      -----------------------
      Name: txtAdminPassword
         Input Mask: Password

      Label:
      -----------------------------------
      Name: lblAdminPassword
         Caption: Administrative Password

      Command Button:
      -----------------------------------------------
      Name: CmdChange
         Caption: Change Password(Non-Administrative)
         Height: 720
         Width: 1440

      Command Button:
      -------------------------------------------
      Name: CmdChangeAdmin
         Caption: Change Password(Administrative)
         Height: 720
         Width: 1440

      Command Button:
      ------------------------------------------
      Name: CmdReset
         Caption: Reset Password(Administrative)
         Height: 720
         Width: 1440 

3. With the frmTestPassword form open in Design view, on the View menu, click Code, and then type the following line in the Form module Declarations section if it is not already there:




Option Explicit 

4. Type or paste the following code in the Form module:




Private Sub CmdChange_Click()

On Error GoTo CmdChange_err
' Make sure the password typed in the New Password text box
' matches what has been typed in the Verify Password text box.
If Me!txtNewPassword <> Me!txtVerifyPassword Then
   MsgBox "The New and Verified Passwords Do Not Match. " & _
     "Please Re-enter", vbCritical
   Me!txtNewPassword = ""
   Me!txtNewVerifyPassword = ""
   Me!txtNewPassword.SetFocus
Else
   ' Check to make sure the Old Password has been filled in and
   ' if it has, change the password.
   If IsNull(Me!txtOldPassword) Then
       MsgBox "Leaving the Old Password textbox empty will cause " & _
         "an error unless you currently have no password", vbOKOnly
     Call ChangeUserPassword(CurrentUser(), "", Me!txtNewPassword)
   Else
     Call ChangeUserPassword(CurrentUser(), Me!txtOldPassword, _
       Me!txtNewPassword)
   End If
End If

Exit Sub

CmdChange_err:
          MsgBox Err.Description
End Sub

Private Sub cmdChangeAdmin_Click()

On Error GoTo CmdChangeAdmin_err

' Test to make sure appropriate text boxes are filled in because this
' is an administrative function.

If Not IsNull(Me!txtAdminPassword) And Not _
  isNull(Me!txtAdminUsername) And Not IsNull(Me!txtUserName) Then
    If Me!txtNewPassword <> Me!txtVerifyPassword Then
       MsgBox "The New and Verified Passwords Do Not Match. Please" & _
         " Re-enter", vbCritical
       Me!txtNewPassword = ""
       Me!txtNewVerifyPassword = ""
       Me!txtNewPassword.SetFocus

   Else
       Call ChangeResetPassword("Change",Me!txtUserName, _
         me!txtAdminUsername, me!txtAdminPassword, Me!txtNewPassword)
   End If

Else

 MsgBox "The textboxes for UserName, New Password, Verified " & _
    "Password, Admin UserName and Admin Password must be complete " & _
    "for this function to operate correctly.", vbOKOnly
 End If
Exit Sub

CmdChangeAdmin_err:
   MsgBox Err.Description

End Sub

Private Sub cmdReset_Click()

On Error GoTo CmdReset_err

' Test to make sure appropriate textboxes are filled in because this is
' an administrative function.

If Not IsNull(Me!txtAdminPassword) And Not _
  IsNull(Me!txtAdminUsername) And Not IsNull(Me!txtUserName) Then
  Call ChangeResetPassword("Reset", Me!txtUserName, _
  Me!txtAdminUsername, Me!txtAdminPassword)
Else
  MsgBox "The textboxes for UserName, New Password, Verified " & _
    "Password, Admin UserName and Admin Password must be complete " & _
    "for this function to operate correctly.", VBOKOnly
End If
Exit Sub

CmdReset_err:
  MsgBox Err.Description
End Sub 

5. Save your changes and close the Module window. Save and close the form.

REFERENCES

For more information about changing passwords programmatically, search the Help Index for "NewPassword Method."

Additional query words: pass word forget lost forgot

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