Test Whether an Update Query has Changed Records (1 Viewer)

RogerCooper · Nov 5, 2015

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 · Nov 5, 2015

Make it a SELECT query.

RogerCooper · Nov 5, 2015

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

plog · Nov 5, 2015

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 · Nov 9, 2015

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

jdraw · Nov 9, 2015

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 · Nov 9, 2015

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:

Code:

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 · Nov 9, 2015

Pyro said:
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:

Code:

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.

Test Whether an Update Query has Changed Records (1 Viewer)

RogerCooper

Registered User.

plog

Banishment Pending

RogerCooper

Registered User.

plog

Banishment Pending

RogerCooper

Registered User.

jdraw

Super Moderator

Pyro

Too busy to comment

RogerCooper

Registered User.

Similar threads

Users who are viewing this thread