Number of records changed by an action query

[thefj]

Hey all,

As part of an access application, I am importing data from an XML file into a table, then using an update query to process the imported data into various other tables in the database where it belongs. I have the queries running fine, but I cannot for the life of me find out how to, from a VB code perspective, get the number of records that will be updated (or were updated) by a query.

I'm trying to make something similar to the default Access warning message box that says "You are about to update XX record(s)", except a custom message that more closely reflects what the user is actually trying to do when the query is executed.

The closest I've got it using an SQL Count() function in a SELECT query based on the update query I have just run, but it seems a bit of a long way of going about it.

Any help appreciated.

[pks00]

Run an action query using currentdb.execute then find out number of records modified

eg

Dim db As DAO.Database


Set db = CurrentDb
db.Execute "UPDATE mytable SET myfield=22"

msgbox db.RecordsAffected


If it complains about dao, u need to add it as a reference, in vba window go to tools/references then check microsoft dao 3.6 object library

[NeoPa]

I love learning new things on here.
Thanks pks00 - this should prove uber-useful.

[PEB]

Yeah,

You're a great girl pks00 :)

Never used a staff like this one...

msgbox db.RecordsAffected

Is needed to use Begintrans and CommitTrans methods with this methode?

My God you're pleinty with surprises..

;)

Run an action query using currentdb.execute then find out number of records modified

eg

Dim db As DAO.Database


Set db = CurrentDb
db.Execute "UPDATE mytable SET myfield=22"

msgbox db.RecordsAffected


If it complains about dao, u need to add it as a reference, in vba window go to tools/references then check microsoft dao 3.6 object library

[thefj]

You have no idea how much hair you have prevented me losing! I can't believe such a simple and useful reporting method is so difficult to find.

Thanks :)

[NeoPa]

I'm with you on that!

[pks00]

Howdo, Peb

and thanks all:)

To tell you the truth, I have no idea whether it works with BeginTrans/CommitTrans. Ive never used these methods with DAO before#
Sorry

Yeah,

You're a great girl pks00 :)

Never used a staff like this one...

msgbox db.RecordsAffected

Is needed to use Begintrans and CommitTrans methods with this methode?

My God you're pleinty with surprises..

;)

[NeoPa]

It is probably possible to use this with begin and end trans, but I'm fairly sure they're not required.

[PEB]

For me it's curious where Access keeps the information for the affected records if this isn't in the buffer where it keeps info for the transactions...

It's thing that should be checked!