Microsoft KB Archive/304319

= How to use ADOX to change Jet user passwords in Access 2002 =

Article ID: 304319

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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
 * How to Reset or Change a User's Password When the User Has Forgotten the Password
 * How to Allow a User to Change His or Her Own Password
 * Example of How to Use the Functions

REFERENCES



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 &quot;admins&quot; group, for example) be passed for the function to complete its work.

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

NOTE: You must reference this library for this code to work.  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 &quot;Change&quot;, then change the Password of the User named in  ' StrUser to the password saved in StrNew. ' If the StrAction passed is &quot;Reset&quot;, then reset the password of  ' the User mentioned in StrUser. If neither &quot;Change&quot; nor &quot;Reset&quot; ' 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 = &quot;Change&quot; Then If strNew <> &quot;&quot; Then cat.Users(strUser).ChangePassword strOld, strNew MsgBox &quot;Password Change Successful&quot;, vbOKOnly Else MsgBox &quot;When Attempting to Change A User's Password, You &quot; & _ &quot;Must Include a New Password&quot;, vbOKOnly End If   ElseIf strAction = &quot;reset&quot; 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 &quot;Password Successfully Reset&quot;, vbOKOnly Else cat.Users(strUser).ChangePassword &quot;&quot;, strNew MsgBox &quot;Password Successfully Reset&quot;, vbOKOnly End If   Else MsgBox &quot;You must Select a StrAction of either '&quot; & &quot;Change'&quot; & _ &quot;' or '&quot; & &quot;Reset'.&quot;, 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  Save the module as SecurityCode.</li></ol>

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:

<ol> Open your database in Microsoft Access.</li> In the Database window, click Modules under Objects, and then select the SecurityCode module that you created in the &quot;How to Reset or Change a User's Password When the User Has Forgotten the Password&quot; section.</li>  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 &quot;Password Changed&quot; End Sub </li> Save the Module, and then close it.</li></ol>

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: <ol> Create a new blank form, and then save it as frmTestPassword.</li> Open the frmTestPassword form in Design view.</li>  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. <pre class="fixed_text">  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) </li>  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 </li>  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 &quot;The New and Verified Passwords Do Not Match. &quot; & _ &quot;Please Re-enter&quot;, vbCritical Me.txtNewPassword = &quot;&quot; Me.txtVerifyPassword = &quot;&quot; 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 &quot;Leaving the Old Password textbox empty will cause &quot; & _ &quot;an error unless you currently have no password&quot;, vbOKOnly Call changeuserpassword(CurrentUser, &quot;&quot;, Me.txtNewPassword) Else Call changeuserpassword(CurrentUser, Me.txtOldPassword, _       Me.txtNewPassword) End If End If

Exit Sub

CmdChange_err: If Err.Number = 94 Then MsgBox &quot;You have not entered a password. If the password is blank &quot; & _ &quot;then enter double quotes&quot; Resume Next Else MsgBox Err.Description & &quot; &quot; & 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 &quot;The New and Verified Passwords Do Not Match. Please&quot; & _ &quot; Re-enter&quot;, vbCritical Me.txtNewPassword = &quot;&quot; Me.txtVerifyPassword = &quot;&quot; Me.txtNewPassword.SetFocus

Else Call ChangeResetPassword(&quot;Change&quot;, Me.txtAdminUserName, _        Me.txtAdminPassword, Me.txtUser, Me.txtOldPassword, _          Me.txtNewPassword) End If Else MsgBox &quot;The textboxes for UserName, New Password, Verified &quot; & _ &quot;Password, Admin UserName and Admin Password must be complete &quot; & _ &quot;for this function to operate correctly.&quot; & _ &quot; If the passwords are blank enter double quotes&quot;, 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 &quot;&quot;. If Err.Number = 94 Then MsgBox &quot;You have not entered a password. If the password is blank &quot; & _ &quot;then enter double quotes&quot; Resume Next Else MsgBox Err.Description & &quot; &quot; & 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(&quot;Reset&quot;, Me.txtAdminUserName, Me.txtAdminPassword, _ Me.txtUser, Me.txtOldPassword, Me.txtNewPassword) Else MsgBox &quot;The textboxes for UserName, New Password, Verified &quot; & _ &quot;Password, Admin UserName and Admin Password must be complete &quot; & _ &quot;for this function to operate correctly.&quot; & _ &quot; If the passwords are blank enter double quotes&quot;, vbOKOnly End If Exit Sub

CmdReset_err: If Err.Number = 94 Then MsgBox &quot;You have not entered a password&quot; & _ &quot; If the password is blank then enter double quotes for the password&quot; Resume Next Else MsgBox Err.Description & &quot; &quot; & Err.Number End If End Sub </li> Save your changes, and then close the Module window.</li> Save and then close the form.</li></ol>

back to the top

<div class="references_section">