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

Doug,

Thanks for the response.

To this point I have not actually used any data inside of Access though I may need to eventually. 
All of my processing occurs out in sql server, with data imported into sql server, arranged into 
tables, exported back out etc.  I use Access only as a control process, a place to enter data into 
tables (in Access so far) that control this rather complex process.

I really should be doing the whole thing in .Net but so far I have needed to spend my available time 
getting the SQL Server side of things down.  The Access side is really quite tame (for me anyway) 
and because I can do Access in my sleep it allows me to focus on the process rather than the control 
of the process.

John W. Colby
www.ColbyConsulting.com


Doug Steele wrote:
> 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.
>>
>>