Number of rows updated by each query within macro

[secohen]

I need to know how many rows are being updated by each query within a macro. My macro contains about 50 queries.

Is it possible to see the details for each query individually - without running each query individually?

[twinnyfo]

secohen,

One thing I know for certain is that action queries cannot be used to create a recordset (and thus perform a recordcount) like normal queries can.

One possible way to do what you are trying to do is to convert your macro to VBA (and convert all your queries to SELECT Queries) and create recordsets for each iteration and perform a recordcount, followed by either executing the Action version of the query, or performing updates via the open recordset.

Since you are talking about 50 queries, this solution sounds like a lot of work (and it would be). But, I don't know how one would perform such functions within the Macro Environment (hence, I am also a strong proponent for moving away from macros and toward VBA).

[zmbd]

Twinney,
secohen mentions "updated" so I suspect that secohen is using an update action query.

secohen, using macros and the standard method you should be receiving a pop-up for each update-query ran that states the number of records that will be effected by the query. If this is not happening then we need to see:

+ The version of Access you are running
+ The SQL of just one of the 50 the action queries being called, please format the sql using the [CODE/] format button in the post toolbar.
+ The macro you are running also formatted using the [CODE/] button. In ACC2003 and older you will need to type the commands in by hand. In ACC2007 and newer you can open the macro in design view, <ctrl><A>,<ctrl><C> then come back to this thread <ctrl><V> select the pasted text and click on [CODE/] formating button.

[twinnyfo]

Z,

I understood his post as asking how to find out the number of records affected. Obviously, if the warnings are set to False, he won't see the prompt to continue with the action query. If the warnings are one, he will see--whether he runs these queries individually or as mart of a macro that runs them all.

My post was regarding finding out how many records are affected "without running the query individually."

Obviously, without seeing each of the 50 queries (individually) this is nearly impossible--even knowing the names of these queries doesn't help much, because OP is specifically asking about the number of rows updated.

So, unless we take apart all 50 action queries and make them Select Queries, I know of no other method. However, I am eager to learn how to do such a thing with an action query, because I think there would be value in having such information available if I needed it. If you know a method, I will certainly monitor this thread for a solution.

[zmbd]

+ there are ways to do this.
+ the execute method does not require warnings to be turned off.
+ both use the records effected method
+ we just need to see the mechanics OP is using