Return No of Rows Affected

[gray]

Hi All

Access 2002/2007
WinXpPro SP2

Does anyone know how to return the number of rows affected when doing an update?... In particular, ! want to detect whether or not an update to a boolean field actually represents a change (and to do so as part of the Update rather than re-query it) i.e..

If my SQL tries to:
Set the value to True when the Column was False, rows_affected = 1
Set the value to True but Column was already True, rows_affected = 0

and vice-versa?

I create SQL in VB and then execute it in an SQL Transaction using an ADODB Connection. I tried the code @@Rowcount method below but I got a -2147217900 error - Missing Operator in Query Expression @@Rowcount ??

Dim ADODBconn As ADODB.Connection
Dim rsRead As New ADODB.Recordset
 
'Set Up Connection and begin SQL Transaction
Set ADODBconn = CurrentProject.AccessConnection
ADODBconn.BeginTrans
 
'Build Update SQL
SQLLine = "UPDATE Addrs SET  Record=True, Date_Record_Last_Updated='21/02/2011 14:07:00', Record_Last_Updated_By_Unique_No=7 
WHERE Unique_No = 15"
 
'Execute the SQL
ADODBconn.Execute SQLLine, dbFailOnError
 
'Build SQL to check the update actually took place
SQLLine = "SELECT @@RowCount AS Rows_Changed FROM Addrs WHERE Record_Last_Updated_By_Unique_No=7"
 
'Execute 'Check' SQL
rsRead.Open SQLLine, ADODBconn, adOpenStatic, adLockReadOnly

I've also tried this syntax:

ADODBconn.Execute SQLLine, dbFailOnError
Rslt_Count = ADODBconn.RecordsAffected

Where Rslt_Count is Dim'd as a Long...

but I get ERROR 3001... Arguments are of the wrong type, are out of acceptable range or are in conflict with one another..?

Thanks

 

[jdraw]

Unless I'm missing your point, a typical approach would be to run a Select query
using the very same criteria you plan to use in the Update query.
You can count the records returned, or modify the Select to include a Count(*) with the same criteria.
Then change the SELECT to an Update query.

Whether a field is Boolean or text shouldn't matter. If you are changing the current value to a new value, an Update occurs.

The other approach would be to use vba and loop through a recordset
using a .Edit...... .Update
You could include a MyCounter = MyCounter +1 type of count mechanism within the loop, then do a Msgbox to show the Number of Updates.

Or, do a count before the update, and a count after the update and report the difference.

Just my $ .02

Subsequent note:
NOTE: I just did some googling and found this that might be helpful to you.

http://www.granite.ab.ca/access/currentdbrecordsaffected.htm

Good luck.

 

[gray]

Hi Jdraw

Thanks for the reply.

Actually, I've always used the loop method exactly as you suggested and it works a dream.

In this particular case, I was worried that (on a busy multi-user environment) there was always the possibility of data changing between the Select and the Update queries - and i'm implementing some record locking on financial data so I didn't want to risk that happening (even at a million-to-one chance ).

I looked at the link you found and although it didn't quite provide an answer, it did get me thinking... And I can now return a RowsAffected value... Syntax in Access is utterly bewildering at times but this is how I did it....

1. First Dim a Long e.g.:-
Dim MyVar As Long

2. Build the sql string e.g. :-
SQLLine = "Update blah blah"

3. Then specify the long var in the Execute:
ADODBconn.Execute SQLLine, MyVar, dbFailOnError
MsgBox MyVar

However, although I no longer get an error... the MyVar "lies"! I tried various tests on my boolean field where I set a mix of True and False values in the raw tables...
I then ran my SQL without criteria.. just to set the Boolean for the entire table...(i.e. Update Table MyTable Set MyBoolean=True)
MyVar is always the same number irrespective of the mix of True or False actually in the table?

 

[gray]

Hi All

I thought I'd spotted my deliberate error for a minute in so far as I am always updating the time and date on the record... so it obviously always returns '1' RecordsAffected!

I've re-tested using just one column (as below) but still RecordsAffected always = 1 even when I am setting a True column to True??...

Anyone know where I'm going wromg please??

Thanks

Dim ADODBconn As ADODB.Connection
Dim rsRead As New ADODB.Recordset
Dim Rslt_Count As Long
 
'Set Up Connection and begin SQL Transaction
Set ADODBconn = CurrentProject.AccessConnection
ADODBconn.BeginTrans
 
'Build Update SQL
SQLLine = "UPDATE Addrs SET  Record=True WHERE Unique_No=15"
 
'Execute SQL
ADODBconn.Execute SQLLine, [B]Rslt_Count[/B], dbFailOnError
 
MsgBox [B]Rslt_Count[/B]
ADODBconn.CommitTrans
ADODBconn.Close

 

[kaisersose1995]

Hi All

I thought I'd spotted my deliberate error for a minute in so far as I am always updating the time and date on the record... so it obviously always returns '1' RecordsAffected!

I've re-tested using just one column (as below) but still RecordsAffected always = 1 even when I am setting a True column to True??...

Anyone know where I'm going wromg please??

Not sure if you eventually solved your issue, but thought I'd post anyway....may help someone else . I believe the following will solve your issue

Dim l_sql as string
Dim Rslt_Count As Long
dim db as database

Set db = CurrentDb
 
'Build Update SQL
l_sql= "UPDATE Addrs SET Record=True WHERE Unique_No=15;"
 
'Execute SQL
db.Execute l_sql, dbFailOnError
[B]Rslt_Count[/B] = db.RecordsAffected
 
MsgBox [B]Rslt_Count[/B]

 

[gray]

Hi kaisersose1995

Thanks for the reply. Unfortunately, this still doesn't quite return what I was hoping. In the example,

If unique_no 15 has record=true and the SQL is run to set record=true, the db.RecordsAffected still returns a 1.

What I was hoping to do was detect if a 'real' change had been made to the record.

Thanks

 

[spikepl]

I think you got into cul-de-sac here. The "records affected" is only a counter for how many records were found complying with your criteria in WHERE, and not a check on what in fact happened with the data.

If you wish to count the records whose values actually changed, then put that into the criteria:

WHERE Unique_No=15 and Record<>True

 

[gray]

Aha... I see... thanks for clearing up my misunderstanding... as they say "assumption is the mother of all ..... [unprintable]"

 

[spikepl]

and as to your underlying concerns - I am not sure about how transactions act in Access, i.e. whether they do (or can be made to) lock all the tables involved while the transaction is being executed.

You could run an experiment, starting and then halting the transaction midway and from elsewhere trying to change one of the tables (and records!) involved

Update: or run two instances of your code, letting one halt midway, and see what happens

 

[gray]

Hi

I've done a fair bit with these transactions now... they were 'interesting' to set up ... the order in which they were built and populated for example...

I don't think they do lock the tables in [what I would call] the traditional way or even in the Access way for that matter... I have built my own record-locking by using flags in the records which are set unset by code. Since I have custom Edit and Delete buttons I can interrogate the locks before allowing an action on any given record... I can also lock records associated with the selected record in the same way....

As you could probably guess, the down-side is that then manual management of those locks is needed... I do this via an admin interface... in case the PC crashes with records still locked for example.

thanks again