How to use ADOX to change Jet user passwords in Access 2002 (304319)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q304319
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

IN THIS TASK

SUMMARY

This article shows you how to use ActiveX Data Objects Extensibility for DDL and Security (ADOX) to change a user's password.

This article uses the same function that is in the following article, except that this article shows you how to use this function in ADOX rather than in Data Access Object (DAO).

200665 ACC2000: How to Change User Passwords Programmatically Using DAO

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.

back to the top

How to Reset or Change a User's Password When the User Has Forgotten the Password

The following sample function allows a user to reset or change the password of another user if the other user has forgotten his or her password. The user who is changing the password must be a member of the Admins group. The old password value is ignored.

This method also allows a user who is a member of the Admins group to change his or her own password. In this case, however, the old password must be supplied.

And finally, this method allows a user who is not a member of the Admins group to change his or her own password as long as he or she has not forgotten the old password because the user must know the old password in order to use this method.

NOTE: It is not advisable to allow all users to reset or change the passwords of others. Therefore, these functions require that the username and password of a user with administrative rights (a member of the "admins" group, for example) be passed for the function to complete its work.

To create this example, follow these steps:
  1. Open your database in Microsoft Access 2002.
  2. In the Database window, click Modules under Objects, and then click New.
  3. On Tools menu, click References.
  4. Scroll down until you see the Microsoft ADO Ext. 2.6 for DDL and Security check box, and then click to select it.
  5. Click OK.

    NOTE: You must reference this library for this code to work.
  6. Type or paste the following code in the new module:
    Sub ChangeResetPassword(strAction As String, strAdmin As String, _
 strPassword As String, strUser As String, strOld As String, _
 strNew As String)
 
   ' If the StrAction passed to the
   ' function is "Change", then change the Password of the User named in
   ' StrUser to the password saved in StrNew.
   ' If the StrAction passed is "Reset", then reset the password of
   ' the User mentioned in StrUser. If neither "Change" nor "Reset"
   ' is passed to the function in the StrAction argument, inform the
   ' user of an error and exit the procedure.
   
   On Error GoTo ChangeResetPassword:
   
    Dim cat As ADOX.Catalog
    Dim usr As ADOX.User
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set usr = cat.Users(strUser)
    
    If strAction = "Change" Then
        If strNew <> "" Then
           cat.Users(strUser).ChangePassword strOld, strNew
           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
       'When the current user is in the admins group and reseting
       'his/her own password, the current password must be supplied.
       'In all other cases, the current user password is not needed
       'for a reset.
       If strUser = cat.Users(strUser) And strAdmin = cat.Users(strUser) Then
              cat.Users(strUser).ChangePassword strOld, strNew
              MsgBox "Password Successfully Reset", vbOKOnly
       Else
              cat.Users(strUser).ChangePassword "", strNew
              MsgBox "Password Successfully Reset", vbOKOnly
       End If
    Else
       MsgBox "You must Select a StrAction of either '" & "Change'" & _
         "' or '" & "Reset'.", vbOKOnly
    End If
 
    Set usr = Nothing
    Set cat = Nothing
    Exit Sub
ChangeResetPassword:
    If Err.Number = -2147217911 Then
        Resume Next
    Else
        MsgBox Err.Description & Err.Number
    End If
End Sub
  7. Save the module as SecurityCode.
back to the top

How to Allow a User to Change His or Her Own Password

The next sample function allows a user to change his or her own (and only his or her own) password. For the function to operate correctly, it must be passed the Username, Old Password, and New Password of the user.

To create this example, follow these steps:

  1. Open your database in Microsoft Access.
  2. In the Database window, click Modules under Objects, and then select the SecurityCode module that you created in the "How to Reset or Change a User's Password When the User Has Forgotten the Password" section.
  3. Click Design, and then type or paste the following code:
    Public Sub ChangeUserPassword(strUser As String, strOld As String, _
 strNew As String)
    Dim cat As ADOX.Catalog
    Dim usr As ADOX.User
    
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    
    Set usr = cat.Users(strUser)
    usr.ChangePassword strOld, strNew
    MsgBox "Password Changed"
End Sub
  4. Save the Module, and then close it.
back to the top

Example of How to Use the Functions

The following sample form uses both functions that you created earlier in this article to demonstrate their functionality. The form contains text boxes that the user must fill in with appropriate information. This information is then fed to the sample functions and command buttons to run the functions themselves.

The form allows users to:
  • Change their own password
The form allows administrators to:
  • Reset a user's password
  • Change a user's password
  • Change their own password
To create the form, follow these steps:
  1. Create a new blank form, and then save it as frmTestPassword.
  2. Open the frmTestPassword form in Design view.
  3. Add the following text box, label, and command button controls to the frmTestPassword form. Each label is associated with a specific text box. Place each label near the text box.
       Text Box:
   -------------
   Name: txtUser

   Label:
   ---------------------
   Name: lblUser
      Caption: User Name

   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: lblVerifyPassword
      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)
      
   Command Button:
   ------------------------------------------
   Name: CmdReset
      Caption: Reset Password(Administrative)
  4. On the View menu, click Code, and then type the following line in the Declarations section of the form module if the line is not already there:
    Option Explicit
  5. 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.txtVerifyPassword = ""
   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:
    If Err.Number = 94 Then
        MsgBox "You have not entered a password. If the password is blank " & _
         "then enter double quotes"
        Resume Next
    Else
        MsgBox Err.Description & " " & Err.Number
    End If
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.txtUser) Then
    If Me.txtNewPassword <> Me.txtVerifyPassword Then
       MsgBox "The New and Verified Passwords Do Not Match. Please" & _
         " Re-enter", vbCritical
       Me.txtNewPassword = ""
       Me.txtVerifyPassword = ""
       Me.txtNewPassword.SetFocus

   Else
       Call ChangeResetPassword("Change", Me.txtAdminUserName, _
         Me.txtAdminPassword, Me.txtUser, Me.txtOldPassword, _
          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." & _
    " If the passwords are blank enter double quotes", vbOKOnly
End If
Exit Sub

CmdChangeAdmin_err:
    'If the old password is null you will get an invalid use of null.
    'This will trap the error and let the user know to enter "".
    If Err.Number = 94 Then
        MsgBox "You have not entered a password. If the password is blank " & _
         "then enter double quotes"
        Resume Next
    Else
        MsgBox Err.Description & " " & Err.Number
    End If
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.txtUser) Then
  Call ChangeResetPassword("Reset", Me.txtAdminUserName, Me.txtAdminPassword, _
  Me.txtUser, Me.txtOldPassword, Me.txtNewPassword)
Else
  MsgBox "The textboxes for UserName, New Password, Verified " & _
    "Password, Admin UserName and Admin Password must be complete " & _
    "for this function to operate correctly." & _
    " If the passwords are blank enter double quotes", vbOKOnly
End If
Exit Sub

CmdReset_err:
  If Err.Number = 94 Then
        MsgBox "You have not entered a password" & _
         " If the password is blank then enter double quotes for the password"
        Resume Next
    Else
        MsgBox Err.Description & " " & Err.Number
    End If
End Sub
  6. Save your changes, and then close the Module window.
  7. Save and then close the form.
back to the top


REFERENCES

For more information about changing passwords programmatically, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type ChangePassword in the Office Assistant or the Answer Wizard, and then click Search to view the topic.


back to the top







Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbHOWTOmaster KB304319 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.