SQL Server stored procedure I'm calling from VBA

[BennyLinton]

I have a SQL Server stored procedure I'm calling from VBA and need to know how to make sure it is working by returning a simple record count. Any ideas? If I run the query from SQL Server it returns a record count:

Private Sub Form_Load()

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GCDF_DB;Data Source=BADLANDS"
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "UpdateStatus"
cmd.Execute

End Sub

 

[pbaldy]

I'd probably execute it from a pass through query by opening a recordset on the pass through. That gets you your count as well.

 

[Galaxiom]

If I run the query from SQL Server it returns a record count:

A Command that doesn't return a recordset will return RecordsAffected as an optional parameter.

Dim somevariable as Integer ' (or Long since an adInteger is the equivalent)
...
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "UpdateStatus"
cmd.Execute(somevariable)
Debug.Print somevariable

BTW A more general way to get an arbitrary return value from a command that runs a StoredProcedure is by using a parameter.

cmd.Parameters.Append(cmd.CreateParameter("ReturnValue", adInteger, adParamReturnValue))
cmd.Execute
somevariable = cmd.Parameters("ReturnValue")

Other output parameters from the procedure may be accessed similarly using adParamOutput.