Count records in update query

[Robert]

How can I count the number of records which have been updated by my UPDATE
query? I tried using DCount but it said you can't use an action query as an
argument.

Robert

 

[Nick Godfrey]

How can I count the number of records which have been updated by my UPDATE
query? I tried using DCount but it said you can't use an action query as an
argument.

Robert

I would suggest that you add a column to your table that records the
time the record was last updated. Then run a query looking for records
updated in the last few seconds (depending on how long the update query
will take to run).
If you try to count the records in the update query before it is run
there is no guarantee that all the records will be updated and therefore
your count may not be correct according to actual updated records.

HTH,

Nick.

 

[Baz]

If you run the query by using the Execute method of a QueryDef or Database
object, the RecordsAffected property will contain the number of records
updated.

 

[Robert]

I thought there might be something returned by .Execute but the help is
sometimes difficult so I didn't try looking it up. Thanks.

 

[Robert]

Baz,

I tried the following:

Dim returnCount As Long, SQLstr As String

SQLstr = "UPDATE tblRentaldetails SET Returndateandtime = '"
SQLstr = SQLstr & Now()
SQLstr = SQLstr & "' WHERE rentalid = "
SQLstr = SQLstr & Me.txtRentalid
SQLstr = SQLstr & " AND return1 = true"
CurrentDb.Execute SQLstr, dbFailonError
returnCount = CurrentDb.RecordsAffected

The query executes but the returncount always comes up zero. See anything
wrong?

Robert

 

[Baz]

Help in most versions after 97 is horrible. It was getting better in 2003
but they've really screwed it up again in 2007. The smart move, if you can
find a copy, is to have the help file from Access 97 handy.

 

[Baz]

Baz,

I tried the following:

Dim returnCount As Long, SQLstr As String

SQLstr = "UPDATE tblRentaldetails SET Returndateandtime = '"
SQLstr = SQLstr & Now()
SQLstr = SQLstr & "' WHERE rentalid = "
SQLstr = SQLstr & Me.txtRentalid
SQLstr = SQLstr & " AND return1 = true"
CurrentDb.Execute SQLstr, dbFailonError
returnCount = CurrentDb.RecordsAffected

The query executes but the returncount always comes up zero. See anything
wrong?

Robert

Yes. CurrentDb is not an object, it is method that returns an object, so
each time you use it you get a new database object. Hence, the database
object which you are checking for
RecordsAffected is not the same database object you used to run the query!

Dim returnCount As Long, SQLstr As String
Dim db As DAO.Database

SQLstr = "UPDATE tblRentaldetails SET Returndateandtime = '"
SQLstr = SQLstr & Now()
SQLstr = SQLstr & "' WHERE rentalid = "
SQLstr = SQLstr & Me.txtRentalid
SQLstr = SQLstr & " AND return1 = true"
Set db = CurrentDb
db.Execute SQLstr, dbFailonError
returnCount = db.RecordsAffected

 

[Robert]

I tried that and I got this error:

Compile error: User-defined type not defined.

on this statement:

Dim db as DAO.Database

Maybe a namespace is missing?

Robert

 

[Baz]

That's right. You haven't got a reference to the DAO library. In the code
window, go to Tools menu, References. Scroll down the list looking for the
Microsoft DAO Object Library, and tick the check box.

 

[Tom Lake]

I tried that and I got this error:

Compile error: User-defined type not defined.

on this statement:

Dim db as DAO.Database

Maybe a namespace is missing?

You have a missing reference. Go in to References
(from a module menu) and look for DAO 3.2 or similar.
Make sure it's checked.

Tom Lake

 

[Robert]

Working. Thanks.

That's right. You haven't got a reference to the DAO library. In the
code window, go to Tools menu, References. Scroll down the list looking
for the Microsoft DAO Object Library, and tick the check box.

 

[Robert]

It's working. thnx

You have a missing reference. Go in to References
(from a module menu) and look for DAO 3.2 or similar.
Make sure it's checked.

Tom Lake