RecordsAffected from query as connection method

[Chris Hohmann]

Database: Access 2000
MDAC Version: 2.7
Provider: Microsoft.Jet.OLEDB.4.0

Is there a way to access the number of records affected when executing a
query as a method of the connection object? For example, using the
Execute method of the connection I do the following:

sql = _
"EXEC spInsertActivityLog " &_
"@prmStart='2003-07-02 09:00'," &_
"@prmEnd='2003-07-02 10:00'," &_
"@prmActivity='Breakfast'"
conn.Execute sql, ra, &H81
Response.Write ra & " records were affected."

Using the query as a connection method I do the following:

conn.spInsertActivityLog _
"2003-07-02 09:00",_
"2003-07-02 10:00",_
"Breakfast"
Response.Write ??? & " records were affected."

The reason I ask is because in this situation, spInsertActivityLog only
inserts the record if it does not overlap existing data. This is one of
those rare cases where transactions are not sufficient to accomplish
what I'm after. I'm perfectly happy to continue using method 1, but if
possible I'd like to take advantage of method 2 since it does the
parameter data typing for me. Any insight is appreciated.

-Chris

[Ken Schaefer]

I have never seen the ability to specify the "query" as a method of the
connection object. It's not listed here in the docs:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaobj01_8.asp?frame=true[/url]

Do you have any links on how this works?

Cheers
Ken

"Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
news:OmwIpzcQDHA.3796@tk2msftngp13.phx.gbl...
: Database: Access 2000
: MDAC Version: 2.7
: Provider: Microsoft.Jet.OLEDB.4.0
:
: Is there a way to access the number of records affected when executing a
: query as a method of the connection object? For example, using the
: Execute method of the connection I do the following:
:
: sql = _
: "EXEC spInsertActivityLog " &_
: "@prmStart='2003-07-02 09:00'," &_
: "@prmEnd='2003-07-02 10:00'," &_
: "@prmActivity='Breakfast'"
: conn.Execute sql, ra, &H81
: Response.Write ra & " records were affected."
:
: Using the query as a connection method I do the following:
:
: conn.spInsertActivityLog _
: "2003-07-02 09:00",_
: "2003-07-02 10:00",_
: "Breakfast"
: Response.Write ??? & " records were affected."
:
: The reason I ask is because in this situation, spInsertActivityLog only
: inserts the record if it does not overlap existing data. This is one of
: those rare cases where transactions are not sufficient to accomplish
: what I'm after. I'm perfectly happy to continue using method 1, but if
: possible I'd like to take advantage of method 2 since it does the
: parameter data typing for me. Any insight is appreciated.
:
: -Chris
:
:
:

[Bob Barrows]

Not that I'm aware of. This technique (2) is only useful if you don't need
any of the functionality provided by a Command object beyond the execution
of the procedure. If you need records affected (or return or output
parameters), then you need to use either an explicit command object, or the
implicit one utilized by the connection object's execute method.

Bob Barrows

Chris Hohmann wrote:

> Database: Access 2000
> MDAC Version: 2.7
> Provider: Microsoft.Jet.OLEDB.4.0
>
> Is there a way to access the number of records affected when
> executing a query as a method of the connection object? For example,
> using the Execute method of the connection I do the following:
>
> sql = _
> "EXEC spInsertActivityLog " &_
> "@prmStart='2003-07-02 09:00'," &_
> "@prmEnd='2003-07-02 10:00'," &_
> "@prmActivity='Breakfast'"
> conn.Execute sql, ra, &H81
> Response.Write ra & " records were affected."
>
> Using the query as a connection method I do the following:
>
> conn.spInsertActivityLog _
> "2003-07-02 09:00",_
> "2003-07-02 10:00",_
> "Breakfast"
> Response.Write ??? & " records were affected."
>
> The reason I ask is because in this situation, spInsertActivityLog
> only inserts the record if it does not overlap existing data. This is
> one of those rare cases where transactions are not sufficient to
> accomplish what I'm after. I'm perfectly happy to continue using
> method 1, but if possible I'd like to take advantage of method 2
> since it does the parameter data typing for me. Any insight is
> appreciated.
>
> -Chris

[Bob Barrows]

It's on this page near the end:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdaobj01_7.asp[/url]

Bob Barrows

Ken Schaefer wrote:

> I have never seen the ability to specify the "query" as a method of
> the
> connection object. It's not listed here in the docs:
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaobj01_8.asp?frame=true[/url]

>
> Do you have any links on how this works?
>
> Cheers
> Ken
>
> "Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
> news:OmwIpzcQDHA.3796@tk2msftngp13.phx.gbl...

>> Database: Access 2000
>> MDAC Version: 2.7
>> Provider: Microsoft.Jet.OLEDB.4.0
>>
>> Is there a way to access the number of records affected when
>> executing a
>> query as a method of the connection object? For example, using the
>> Execute method of the connection I do the following:
>>
>> sql = _
>> "EXEC spInsertActivityLog " &_
>> "@prmStart='2003-07-02 09:00'," &_
>> "@prmEnd='2003-07-02 10:00'," &_
>> "@prmActivity='Breakfast'"
>> conn.Execute sql, ra, &H81
>> Response.Write ra & " records were affected."
>>
>> Using the query as a connection method I do the following:
>>
>> conn.spInsertActivityLog _
>> "2003-07-02 09:00",_
>> "2003-07-02 10:00",_
>> "Breakfast"
>> Response.Write ??? & " records were affected."
>>
>> The reason I ask is because in this situation, spInsertActivityLog
>> only
>> inserts the record if it does not overlap existing data. This is one
>> of
>> those rare cases where transactions are not sufficient to accomplish
>> what I'm after. I'm perfectly happy to continue using method 1, but
>> if
>> possible I'd like to take advantage of method 2 since it does the
>> parameter data typing for me. Any insight is appreciated.
>>
>> -Chris

[Ken Schaefer]

Cool, never seen that before (well, maybe seen it - I've look at that page
enough times! - never paid any attention I suppose)

Thanks Bob.

Cheers
Ken

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:OR2duXdQDHA.304@tk2msftngp13.phx.gbl...
: It's on this page near the end:
: [url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdaobj01_7.asp[/url]
:
: Bob Barrows
:
: Ken Schaefer wrote:
: > I have never seen the ability to specify the "query" as a method of
: > the
: > connection object. It's not listed here in the docs:
: >
:
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaobj01_8.asp?frame=true[/url]
: >
: > Do you have any links on how this works?
: >
: > Cheers
: > Ken
: >
: > "Chris Hohmann" <hohmannATyahooDOTcom> wrote in message
: > news:OmwIpzcQDHA.3796@tk2msftngp13.phx.gbl...
: >> Database: Access 2000
: >> MDAC Version: 2.7
: >> Provider: Microsoft.Jet.OLEDB.4.0
: >>
: >> Is there a way to access the number of records affected when
: >> executing a
: >> query as a method of the connection object? For example, using the
: >> Execute method of the connection I do the following:
: >>
: >> sql = _
: >> "EXEC spInsertActivityLog " &_
: >> "@prmStart='2003-07-02 09:00'," &_
: >> "@prmEnd='2003-07-02 10:00'," &_
: >> "@prmActivity='Breakfast'"
: >> conn.Execute sql, ra, &H81
: >> Response.Write ra & " records were affected."
: >>
: >> Using the query as a connection method I do the following:
: >>
: >> conn.spInsertActivityLog _
: >> "2003-07-02 09:00",_
: >> "2003-07-02 10:00",_
: >> "Breakfast"
: >> Response.Write ??? & " records were affected."
: >>
: >> The reason I ask is because in this situation, spInsertActivityLog
: >> only
: >> inserts the record if it does not overlap existing data. This is one
: >> of
: >> those rare cases where transactions are not sufficient to accomplish
: >> what I'm after. I'm perfectly happy to continue using method 1, but
: >> if
: >> possible I'd like to take advantage of method 2 since it does the
: >> parameter data typing for me. Any insight is appreciated.
: >>
: >> -Chris
:
:
: