SQL Server stored procedure I'm calling from VBA (1 Viewer)

BennyLinton

Registered User.
Local time
Today, 14:58
Joined
Feb 21, 2014
Messages
263
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:

Code:
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

Wino Moderator
Staff member
Local time
Today, 14:58
Joined
Aug 30, 2003
Messages
34,924
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

Super Moderator
Staff member
Local time
Tomorrow, 09:58
Joined
Jan 20, 2009
Messages
12,219
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.

Code:
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.

Code:
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.
 
Last edited:

Users who are viewing this thread

Top Bottom