By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Number of records changed by an action query

P: 2
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.
Nov 3 '06 #1
Share this Question
Share on Google+
8 Replies


pks00
Expert 100+
P: 280
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
Nov 3 '06 #2

NeoPa
Expert Mod 15k+
P: 31,263
I love learning new things on here.
Thanks pks00 - this should prove uber-useful.
Nov 3 '06 #3

PEB
Expert 100+
P: 1,418
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
Nov 4 '06 #4

P: 2
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 :)
Nov 5 '06 #5

NeoPa
Expert Mod 15k+
P: 31,263
I'm with you on that!
Nov 6 '06 #6

pks00
Expert 100+
P: 280
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..

;)
Nov 10 '06 #7

NeoPa
Expert Mod 15k+
P: 31,263
It is probably possible to use this with begin and end trans, but I'm fairly sure they're not required.
Nov 10 '06 #8

PEB
Expert 100+
P: 1,418
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!
Nov 18 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.