retrieve records affected count from ADO?

[Rich Protzel]

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!

[Erland Sommarskog]

Rich Protzel (rp*****@aol.co m) 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, so****@algonet. se

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

[Rich Protzel]

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 CountRecsAffect ed As Long
...
cmd.Parameters( "@bDate").V alue = sDate
cmd.Execute
CountRecsAffect ed = cmd.?
or
CountRecsAffect ed = ?
or
CountRecsAffect ed = 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!

[Erland Sommarskog]

Rich Protzel (rp*****@aol.co m) writes:

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

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

cmd.Execute CountRecsAffect ed

Assuming that Access works like Visual Basic.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

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

[Trevor Best]

On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases. ms-sqlserver, Erland Sommarskog <so****@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)

[Rich Protzel]

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!