docmd.runsql

[Guest]

Server = SQL Server 2000sp3a
Client = Access 2000 SP3

I have a onClick event on a command button which runs a stored procedure.

docmd.runsql "exec sp_mySproc '" & me.cmbo1 & "', '" & me.cmbo2 & "'"

This results in the error:
2342 A RunSQL action requires an argument consisting of an SQL statement

I've tried typing out the actual thing in VBA, ie not passed from objects
but hard coded, but it still fails. I know the SQL is right because I can
msgbox it and copy the result into QA and run ok from there.

I have no problems running straight docmd.runsql "exec
sp_MySproc_No_Params", just as long as there are no parameter values expected.

Is this normal?

Should I be using this instead?:

Dim adoRST As ADODB.Recordset, adoCON As ADODB.Connection, strSQL As String
Set adoRST = New ADODB.Recordset
Set adoCON = CurrentProject.Connection
strSQL = "exec sp_mySproc '" & me.cmbo1 & "', '" & me.cmbo2 & "'"
adoRST.open strSQL, adoCon


Or is there a better way?
Thanks.

Steve'o

 

[Graham R Seach]

Steve,

You should be using ADO.

If you don't need to return a recordset, then the following will work:
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

On Error GoTo Proc_Err

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "sp_mySproc"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@Param1") = Me.cmbo1
.Parameters("@Param2") = Me.cmbo2
.Execute

Debug.Print .Parameters("@Return_Value")
End With

Set cmd = Nothing
cn.Close
Set cn = Nothing

'= = = = = = =
If you want to return a recordset, then the following will work:
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

On Error GoTo Proc_Err

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "sp_mySproc"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@Param1") = Me.cmbo1
.Parameters("@Param2") = Me.cmbo2
End With

Set rs = cmd.Execute
Debug.Print rs!SomeColumn

rs.Close
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia