** Urgent Help please ...

[Me]

I am trying to run the sql server procedure thru' Access form.

I want to run the sql server procedure sp_password to
change the sql server database password for logged on user.
How can I do it?

I tried using DoCmd.RunSQL SQL

where SQL = EXEC sp_password NULL, 'newpwd', 'username'

but it seems it won't accept statements other than 'SELECT/INSERT/UPDATE/ or
the like thereof'

I also tried creating a function with the following -
Application.CurrentProject.Connection.Execute s SQL , but when I execute it

I get the error EXEC isn't found or something like that.

Thank you,
-Me

 

[smk23]

You need a pass-through query to run a SQL stored procedure.
1) Define a pass-through query and call it something ("MyPassThru" for this
example)

2) You will need to supply a new query definition to get the new parameters
to SQL:

strSQL = "EXEC sp_password NULL, " & 'newpwd' & ", " & 'username'

CurrentDb.QueryDefs("MyPassThru").SQL = strSQL
CurrentDb.QueryDefs("MyPassThry").Execute

HTH

 

[Me]

smk23,

I get the same error, sql should use select/insert/update/procedure
statements.
I tried procedure, but it doesn't work.

Any ohter ideas?

Thanks for your help!
-Me

 

[smk23]

I personally use ADO to connect to SQL SPs:
In the code below, mConn must be a valid ADO connection but I believe that
"Application.ActiveConnection" is valid. (Not sure because I define a
connection string myself, but try this)

Notice the line "msgbox strText" > that will allow you to read your command
prior to executing in case there is some typo.

Dim mCmd AS ADODB.Command
Dim mConn AS ADODB.Connection
Dim rs AS ADODB.Recordset
Dim strText as String

strText= "EXEC sp_password NULL, " & 'newpwd' & ", " & 'username'

msgbox strText

Set mConn=Application.ActiveConnection

Set mCmd = New ADODB.Command
With mCmd
.ActiveConnection = mConn
.CommandText = strText
.CommandType = adCmdStoredProc

Set rs = New ADODB.Recordset
Set rs = .Execute
End With

rs.Close
set rs=Nothing

 

[Me]

Hi Sam,

Thank you very much for taking time to reply my question, I really
appreciate it.
But it didn't work, I got the same error on execute, it won't all a
procedure to
execute as it expects only the select/update/insert statements.

I gave up and I am resorting to another solution as I have to rollout my
application.

-Regards,
-Me

 

[Me]

Dirk,

I re-checked to ensure I had the pass-thru' query, but still it doesn't
work, as even with pass-thru' queries, it allow only select/update/inserts.

Thanks for your help!

-Me

 

[Cynthia]

How do you create a pass-through query? I am trying to run a stored
procedure from access and need to pass an argument to the stored procedure.
I have an update query that runs another stored procedure, but it is not
passing any arguments.

 

[Bill Edwards]

Either of the following describes creating a pass through query that accepts
parameters:

http://support.microsoft.com/kb/131534
http://msdn2.microsoft.com/en-us/library/aa140021(office.10).aspx

 

[Cynthia]

Thank you for the info, I look forward to getting this worked out