Macros are basically Microsoft Access’ way of telling the application what it needs to do. It’s essentially a small program (or a piece of script) that can tell the application a whole range of things it can do and how to go about doing them. They can sometimes be useful when performing certain tasks during the initial creation of applications.
Unfortunately, in Microsoft Access, when creating certain functions, you have no other option but to use macros. This is generally the only times you should be using them, especially when it comes to making serious applications on a larger scale. The specific times you'll want to be using macros should include while you are creating menus, reassigning auto keys, adding custom actions to a toolbar and the Dynamic Data Exchange with other applications.
The verdict regarding the use of Microsoft Access macros seems to be split. While many people try to avoid using macros unless it is absolutely necessary for the certain task that needs to be completed, others try to build there entire application using the things. The latter of the two can sometimes prove to be more trouble than it’s worth, as many people have come to realize. The main reason given by the people who don't favor working with macros is because of the serious limitations regarding what can and cannot be done with them.
Not only are they a bad choice when it comes to creating large-scale applications, but error handling and user defined functions will not work with a Microsoft Access macro. You also cannot name variables inside a macro, which means you will have no way to pass parameters in specific parts of your application if the need should happen to arise.
There is also no way to process records in a table separately when using macros and it just happens to be one of the harder codes to debug – as many people can attest to. It’s also not possible to do any kind of replication or to create database objects at runtime through the use of Microsoft Access macros. To top it all off, processing transactions cannot be done from within a macro.
On the other hand, there are a couple reasons why using macros might end up benefiting you. If the application you are creating needed to be built yesterday (and in you need it ASAP), then a few carefully selected macros could end up coming in awful handy. Though your application might be more prone to crashing, it’s still far better to have the macro-based application than no application at all.
The other reason for which you might find macros useful is the ability for newer versions of Microsoft Access to convert the code used in the macro to Visual Basic for Applications (VBA) code. After you’ve done this, you no longer have a macro, and you will be able to edit the code to fit your needs with less drawbacks and a greater flexibility. This option is utilized by many, because it offers a fast way of creating the basic code for your application, but still gives the added security of the application functioning better in the end.
The bottom line is this – if you can get away without having to use a Microsoft Access macro, then I’d suggest going that route. If you decide though, that you need to use a macro, either because the function you need cannot be accessed in any other way or you don’t have the time to code the application all yourself at the moment, then it’s better than nothing, and sometimes your only option. Just try and remember not to build your entire application through macros, or you might be sorry.