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 (AT) aol (DOT) com) writes:
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 (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks for your reply.

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 (AT) aol (DOT) com) writes:
cmd.Execute CountRecsAffected

Assuming that Access works like Visual Basic.


--
Erland Sommarskog, SQL Server MVP, sommar (AT) algonet (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar (AT) algonet (DOT) se>
wrote:

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!

Thanks for the link. It was very informative.

http://www.algonet.se/~sommar/error-...I.html#NOCOUNT

Rich

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