ADP & sp_password problesm

[ajrimmer]

I'm creating an ADP & internal audit want me to force users to update their passwords periodically. I'm having problems on two fronts. Firstly, the VBA to send the information to sp_password keeps giving me the error
"Procedure 'sp_password' expects parameter '@new', which was not supplied."

Here's the code (VBA) I'm using:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Set cnn = CurrentProject.AccessConnection
.
.
.
With cmd
    .ActiveConnection = cnn
    .CommandType = adCmdStoredProc
    .CommandText = "master.dbo.sp_password"
    .Parameters.Refresh
    .Parameters("@old").Direction = adParamInput
    .Parameters("@old") = oldpassword
    .Parameters("@new").Direction = adParamInput
    .Parameters("@new") = NewPassword
    .Parameters("@loginame").Direction = adParamInput
    .Parameters("@loginame") = mytestlogin
    .Execute
End With

I've tried using .parameters(1) etc but get the same result.

Second question - how can I check if the change has been succesful. I see sp_password returns 0 but how can I get at that value to check? I can then inform the user?

Thanks in advance.

Richard

 

[PWise]

What is the code of the Stored Procedure

 

[ajrimmer]

Stored procedure is the 'standard' M$ stored procedure sp_password (Expects 3 variables @Old, @New & @loginame - @old & @loginame can be null)

create procedure sp_password
    @old sysname = NULL,        -- the old (current) password
    @new sysname,               -- the new password
    @loginame sysname = NULL    -- user to change password on
as
.
.
.

 

[ajrimmer]

OK, found an answer in one of my (multitude of) Access books at home last night. For info it looks like this:

With cmd
    .ActiveConnection = cnn
    .CommandType = adCmdStoredProc
    .CommandText = "master.dbo.sp_password"
    .Execute Parameters:=Array(oldpass, newpass, sqllogin)
End With

The quick test I ran (with an incorrect old password) returned a sensible (ie in English) error message.