MS Access Data Bases: Creating and Using Macros

An analogy to a database macro would be a menu item in a restaurant. When you order a steak dinner, you order one thing, but naturally expect what goes with it – the steak, the baked potato, the salad, etc. A macro in a database “orders up” a single or a group of steps that can be run in one go. If, like in a steak dinner where you order your steak cooked medium rare, you want the macro to pause so that you choose certain options, you can also program the macro to do that.

Macros essentially "bundle up" a single command or a group of steps you need in accessing, maintaining or getting the most from your database with fewer steps and less work. The example macro we will learn about in this article is one called "AutoExec." This macro is executed each time you open your database. The macro could, for example, open a form and launch the "find" dialog box.

As in the planning that must go into creating your database, you'll need to do the same before you can create and execute a macro. A macro is also somewhat like a robot that goes somewhere (usually to another database object, like a form, query or report) and does something (like opens the object and lets you get on with your work). You could do the same work without creating a macro, but the macro saves the tedium and makes you more efficient by performing exactly the same steps each time. So planning is important, because, like robots, macros do exactly as you order them. They need to be tested thoroughly, since the conditions of "launching the robot" must be precisely set or the macro will either fail or freeze.

Once you have everything ready and have planned ahead, you can create a macro by clicking on the “Create” tab in the MS Access database window, which displays the macro icon on the far right. (See screenshots below.) Clicking on the macro icon opens a dialog box where you order up your steps in the left “Action” column. You choose from an array of actions from the drop-down menu. The middle “Arguments” column are details for a particular action. For example, if the Action is “OpenQuery” the arguments would include, naturally, the name of the query, the view you want it to open in, etc.

Let’s do a basic, easy macro: Say you want your database to open with a specific form displayed. You want the form to be opened where you can edit or add a new one. So under “Action” you select “OpenForm”; in the Action Arguments section you select the form from the drop-down list. (The form is there because you created it previously.) Then you select “Edit” from the Data Mode and “Normal” from the Window Mode.

The final step is to name and save the macro. This one we will name “AutoExec.” This will be the default macro that your database opens with every time.

Command buttons on database forms are handy automation tools in MS Access databases. Adding a button activates the Command Button Wizard that has an array of commands (one-step macros, really). Often the command button wizard will not have the specialized command (or group of steps) you need. Here is where your macro comes in: You open the “Miscellaneous” category and choose “Run Macro.” Any macro that you have saved can then be attached to a button on your form (or your switchboard – see my article on Access switchboards.)

See the Microsoft Office Online article on automating applications with macros in Access.

Go to Microsoft Access Macro Articles and Examples for a wealth of examples and information on macros.

Also, for more user guides, be sure to check out the collection of Microsoft Access tips and tricks available here on Bright Hub's Windows Channel.