What is an Access Macro?

Question

The word macro has several meanings in the Microsoft Office suite. What are they - when would I use them? Is there a difference between macros and vba code?

Answer 1

What is an Access Macro?

A macro can be thought of as one or more instructions that are saved in an Access database. The instructions could be as simple as opening or closing a form or report. They could also involve several steps, even performing calculations and/or logical operations in order to identify which actions should be taken!

There are two types of macros:  embedded and stand-alone "macro objects". Embedded macros are always attached to events and will not appear in the Navigation Pane. Macro objects appear in the Navigation Pane. 

Alert to Excel users: Do not confuse macros with Microsoft Office VBA macros. They are NOT the same thing! VBA Macros which can be run in office products such as Excel and Word consist of lines of VBA code. Access macros are NOT VBA code.

The following are a couple practical macro examples along with instructions for creating them:

Embedded Macro created By the Form Design Control Wizard:

1.      Open a test form in design view

2.      Use the Form Design Ribbon to add a button to the form.  (This is typically the fourth icon in the Controls Group on the ribbon).

3.      As long as the wizard option is turned on (it is on by default), a popup will appear asking you what you want the button to do. Take a minute to browse the many examples without clicking the Next button.

4.      Go to the Category "Form Operations" select "Close Form".

5.      Click Next.

6.      You can either type text for your button, or use a picture – your choice. 

7.      Click Next.

8.      Access will name your button but the name will be meaningless: CommandX where X is a number.

For the sake of this test, that silly name is fine, but if this is a database that you will be doing other work on, it is suggested that you give it a better name such as "CloseThisForm". 

9.      Save the form. Re-open it in form view and observe that when you click the button the form closes!

To view the macro created by Access behind the scenes, open the property box of the button. On the Event tab of the property box, the On Click event will read "[Embedded Macro]". The ellipses to the right of that line will open the Access Macro Editor window to view the instructions that the wizard added.

Object Macro created By the User:

In this example, we will create a macro that will open a query in read-only mode. Your database must have at least one select query before we can do this.

1.      On the Create Ribbon, select the icon labeled Macro (it is in the grouping called Macros & Code)

2.      A green plus sign appears – asking you to select your first "macro action", which can be thought of as an instruction. Select Open Query.

3.      New options appear, giving you a dropdown to select the Query Name (do not type it – use the dropdown arrow on the right of the Query Name box!)

4.      For the view, keep the default "Datasheet" – which simply tells the user you want to view the query in the format that one would see if it had been opened from the Navigation Pane.

5.      For Data Mode, change the option to Read Only. This will prevent a user from typing/adding or deleting records from the query datasheet view.

6.      Save the macro (right click the tab in the upper left corner of the macro window for a Save option). Name your macro something like "Testing Query"

7.      Close the macro window.

8.      In the Access Navigation Pane, under the object Macros, your new macro will appear. 

9.      Double click it and voila – the query will run, and you will not be able to make any data changes to it.

Of what practical use is that?

The Navigation Pane can be hidden from users, and macros can consist of a SERIES of steps. Imagine a situation where you need to run a delete query, then an append query, and only after those two steps do you want the user to see a read-only view of a query that shows the impacted records. All this could be done by creating one macro, then adding a command button to a form that runs the macro!

Macros vs. VBA Code

VBA Code is a programming language that works with the Office suite. Code is added to modules in an Access database. Macros do not require coding skills: a user interface hides the code!

Personally, I have to admit that I somewhat detest macros. But a little bit of history: I design Access databases for a living. When I first started out, (learning phase), I did not know much about VBA programming, and macros were an easy way to explore the power of Access. Over time, I quickly learned that a database that relies on macros typically makes it very easy for a user to accidentally enter a "Stop Macro" window that is both confusing and dangerous. When this happens, users are sent to the Macro editing window – where only the developer should be! For professional databases, I realized I needed to learn VBA. Thankfully, Access includes a feature that converts existing macros into VBA! I found myself creating macros, converting the macro to code, and then tweaking the resulting VBA code to handle potential errors and to provide even more power/flexibility than what is possible via a macro. Converting macros to code isn't enough to master VBA – but it certainly helps to provide a starting point!

Today, I will use some of the built-in wizard macros for simple actions such as closing forms. 98% of the time, however, VBA code is now easier for me to write, and it clearly has many powerful advantages.  The learning curve to master VBA is much higher than some care to go, but macros ARE a wonderful tool that can accomplish a myriad of tasks. Thanks to the internet, when stumped on what action to select and/or how to structure a series of macros, help is almost always just a few internet queries away.