Test Whether an Update Query has Changed Records

[RogerCooper]

Is there any easy way to test whether an update query has changed (or will change records)? I running a long series of data correction update queries twice a day, and I would like to see if I can eliminate unneeded ones.

 

[plog]

Make it a SELECT query.

 

[RogerCooper]

I need to change values of the data. Selecting and editing is much slower than an update query.

 

[plog]

I have no idea the point you are making.

Step 1. Run SELECT query that has same criteria as UPDATE query.

Step 2. If records are returned by SELECT query, run UPDATE query.

 

[RogerCooper]

That doubles the run time. Is there a better way?

 

[jdraw]

What exactly is your concern that
a) UPDATE query isn't working? or
b) data values are not changed??

As said previously,
SELECT the records whose values you want to change
UPDATE those records
SELECT the records using the new values

As for run time, what hardware are you using? Is the run time really noticeable?
Put the sequence of queries into a loop process and run.

It's you who is doubting the UPdate, so test your base logic with a few records, make sure it works, then run the loop set up.

It you have additional concerns, then make sure you do a backup before you start --you can always do the process over.

Still not sure what your basic concern is.

 

[Pyro]

I think for the scenario of "will the query change records", you would need a select query first to determine the number of records that might be effected.

To see the number of records that were changed:

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "<your stored query name, or SQL as a string>"

MsgBox db.RecordsAffected

Set db = Nothing

 

[RogerCooper]

I think for the scenario of "will the query change records", you would need a select query first to determine the number of records that might be effected.

To see the number of records that were changed:

Dim db As DAO.Database
Set db = CurrentDb()
db.Execute "<your stored query name, or SQL as a string>"

MsgBox db.RecordsAffected

Set db = Nothing

The RecordsAffected method looks to be what I need. Thank for the help.