retrieve records affected count from ADO?

Hello,

If I run an action SP from MS Access using ADO:
...
cmd.execute

where the SP is something like Create...
Update tbl1 set fld1 = 'something' where...

how can I retrive the count of records affected like from Query
analyzer?

Thanks,
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rich Protzel (rpng123@.aol.com) writes:
> If I run an action SP from MS Access using ADO:
> ..
> cmd.execute
> where the SP is something like Create...
> Update tbl1 set fld1 = 'something' where...
> how can I retrive the count of records affected like from Query
> analyzer?

The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.

>>The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.
<<

May I ask how I go about retrieving the Count of records affected back
into MS Access?

Dim CountRecsAffected As Long
...
cmd.Parameters("@.bDate").Value = sDate
cmd.Execute
CountRecsAffected = cmd.?
or
CountRecsAffected = ?
or
CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
the count of parameters being used?

Thanks again for your reply.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rich Protzel (rpng123@.aol.com) writes:
> May I ask how I go about retrieving the Count of records affected back
> into MS Access?
> Dim CountRecsAffected As Long
> ..
> cmd.Parameters("@.bDate").Value = sDate
> cmd.Execute
> CountRecsAffected = cmd.?
> or
> CountRecsAffected = ?
> or
> CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
> the count of parameters being used?

cmd.Execute CountRecsAffected

Assuming that Access works like Visual Basic.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Assuming that Access works like Visual Basic.

For most things, including ADO, it does.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||Thank you all for your replies. I think I get the idea now about how to
retrieve the count of records affected from an action sp.

One more question if I may:

If I set my sp to

SET NOCOUNT ON

would that improve the performance of my sp? It is not critical for me
to retrieve the count of records affected, mostly just a check. But if
the sp works consistently, and setting

SET NOCOUNT ON

significantly improve performance, then maybe I should consider that.
Most of my action sp's are affecting over 100,000 records of tables with
nearly 200 fields (no redundant fields) with over 1,000,000 records.

Thanks again,

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!