Here’s an snippet from my popular Microsoft Access database eBook on How To Use Microsoft Access Macros & Automate Your Application 

In this section I’m going to list the advantages and disadvantages of using Access macros that is an exercise developers need to carry out to determine how they will programme their application going forward and avoid the dreaded result of having a ‘pear shaped’ and inflexible database.

Benefits of using Access Macros

Here’s the list:

1. Easier to write! You do not have to have a university degree in computer programming to understand and utilise macros. The command reference is intuitive and easy to apply. It just requires some investment of your time.

2. Disabled Mode – From version 2007 by default, any database opened that contained VBA code would not run as part of the security changes made to Microsoft Office applications and prevent unwanted macro virus threats that the VBA code could contain some malicious routines which some programmers seem to get a kick out of! Macros used within templates in Access are safe and run in normal mode.

3. Access Services – With the introduction of SharePoint server where you can now publish your Access database on the web in a secured environment, VBA code isnot a web compatible procedural language and therefore will not run. Macros on the other hand are safe and will run via a web server.

4. Embedded Macros – As of Access 2007, you can now attach a macro inside a form or report as part of the host object and not have a dedicated separate macro object sitting in the navigation pane (or database window for earlier versions). This means when you copy a control like a command button which has an embedded macro the procedure copies across too, as it is part of the properties to the control.

5. Command Bars/Ribbon Bars – Macros have the ability to be attached to customised command buttons on a toolbar/menu bar (pre Access 2007) and ribbon bars (from Access 2007) which in turn can call VBA procedures should you need to.

6. Reserved Macro Names – There are two reserved macro names that automate the start-up and keyboard shortcut controls in your application without the need to code in VBA. A polished application will have reassigned keyboard shortcuts using the ‘AutoKeys’ macro.

7. Variables are not reset – With VBA, public variables lose their values when a procedure ends or there is an error thrown. Macros can keep the values in place when an error occurs and have an advantage when handling errors in code.

Microsoft Access Databases: Pros & Cons of Access Macros

Disadvantages of using Access Macros

1. Performance – VBA code is generally faster and more efficient that macros and is more noticeable for larger or longer procedures compared to a small piece of code where it is negligible. High end applications will use structured VBA more than macros.

2. Flexibility & Functionality – Macros allow you to do many things but VBA has the power to reach beyond and communicate with other applications and use features that macros simply cannot do, like play a sound, talk to web service or handle non Microsoft software.

3. Managing Code Procedures – Macros can be split into smaller units and now in Access 2010 you have a sub-macro tool; this can still be a restriction in handling control flows and other modular based calling procedures. Using external references like ADO is simply not possible and will be a deciding factor when planning your coded procedures.

4. Build Custom Functions – In VBA, you can build additional Access functions to sit alongside the standard functions and use them in queries, forms and reports like with any other function.

Spend the time analysing which set of programming tools you are going to apply. It may even be that a combination of the two will give you a fine balance and create a good working practice for end users. It could be a two tier structure whereby macros are used for basic processing that end users could manipulate and VBA is sealed in the background and critical workflows that no user is allowed to gain access to.

You can also convert existing macros into VBA code with the built in tool available but in some cases, will need to be checked and edited.

Want to know more about Microsoft Access database macros? my eBook is a good place to start and backed with he usual 30 day ‘no questions asked’ returns policy. Check out How To Use Microsoft Access Macros & Automate Your Application 

Tags: access database training, access programming, automating your database with macros, microsoft access macros, pros and cons of access