[AccessD] adodb returning error code from stored procedures - CODE

FWIW, I'm using DAO and am returning the rows affected from a SQL stored
procedure as follows (code is abbreviated):

1. In my SQL sproc, called 'mySPROC':

BEGIN
SET NOCOUNT ON (this is apparently important)
....
T-SQL statements
....
SELECT @@ROWCOUNT as myRowCount
END

2. In Access:

  Dim qd As QueryDef
  Dim rs As Recordset

  Set qd = CurrentDb.QueryDefs("qDummyPassThroughQuery")
  qd.Connect = "ODBC;DRIVER=SQL Server;Server= " & myServer & ";DATABASE=" &
mySQLDatabase & ";Trusted_Connection=Yes"
  qd.SQL = "exec mySPROC"
  qd.ReturnsRecords = True
  Set rs = qd.OpenRecordset
  MsgBox rs.Fields("myRowCount")

Doug Steele


On Thu, May 14, 2009 at 8:08 AM, jwcolby <jwcolby at colbyconsulting.com>wrote:

> The following is the code I currently use for executing stored procedures.
>  As I mentioned in the
> previous email, it works, but it does not return any value from the SP.  I
> have played around trying
> to make it do so but I think this is one of those cases where I could play
> for months and never get
> results without help.
>
> Any assistance is greatly appreciated.
>
>