Most of us know that you can build an Access macro by selecting actions, and defining arguments for those actions.
This in itself is no big deal, but when you have many actions to add, it can be quite tedious.
Here's what I mean:
To run a query in an Access macro, you first select the Open Query action.
Next, you have to define the arguments for the Open Query action, telling Access which query to run.
Now you repeat that process for the next 50 queries. See what I mean?
For large data crunching exercises, it's not uncommon to have 25 – 50 queries that have to be run in consecutive order.
An Access macro can help automate the running of these queries, but building a macro that incorporates all the needed queries is a chore.
So how do you avoid the painful process of adding one query action at a time? Drag it.
Most people don't realize that you can simply drag a query to the macro grid and Access will automatically start create Open Query action complete with all the arguments.
All you have to do is drag all your queries in order to the macro grid. If you build as many Access macros as I do, this is a life saver.
Mike, what are your thoughts on the new macro modifications in Access 2010? Also, in your work, about what percentage of solutions are satisfied with macros vs procedures?
Thx
Colin: I think the new macro functions in Access 2010 are a great step forward. I can't talk about them too much here, but in the end, they will make programming Access for the web easier than it has been in the past.
About half of the databases I work with are used as work-horses to consolidate and normalize disparate data sources. In these databases, I find macros to be more than sufficient to automate any ETL process. I reserve procedures for functional applications that do more than just process data.
Mike, thanks for the response. I like that you can add macros to table events, giving you the functionality of triggers in other DBMSes.
That's really cool – thanks for sharing this one! It's brilliant!
That's awesome! It's a huge time-saver!!
I, too, reserve procedures for "macros' that would include more specialized processes (like having Access automatically open an Excel file for refreshing after exporting a bunch of data).
I hope this question is not off topic. I have been writing Excel Macros for about six months. Recently I needed Excel to interact with Access. Is it better to have Excel pull data from Access using MS Query or have Access push data to Excel?
C Rieckenberg: It really depends on the complexity of the situation. If you're using Excel as the host environment, and all you need is a bulk read-only data pull, then I would use MS Query.
If you're using Access as the host and you need to send data to Excel, then I would use an Access macro or VBA.
Love this tip, thank you!