Part II: Programming Microsoft Access
552
were equivalent in their capabilities, macros have always been considered inferior to VBA for han-
dling an application’s logic.For example, up until Access 2007, the only options for opening a form when a button is clicked
were to use VBA code (DoCmd.OpenForm “FormName”) or to use a macro that ran the
OpenForm macro action.The problems with traditional Access macros were considerable:l (^) Macros existed as separate database objects, so keeping track of the macros in effect
on a particular form was often difficult. Because there was no direct connection
between a form (or a report, for that matter) and a macro, it was easy to break the macro
by deleting or renaming it. VBA code encapsulated within the form’s code module never
had this problem.
l There was no way to trap or handle errors in macros. In versions of Access prior to
2007, macros would simply stop running and display an error dialog box if something
unexpected happened. These interruptions were unwelcome by users, particularly because
there was, most often, nothing a user could do to correct the problem or prevent it from
happening again. VBA code has always featured strong error handling and could often
provide a default value or instruct the user what to do in the event of a problem.
l Macros were unable to work with code. There was no way for a macro to loop through
a recordset, for example, to sum field values or detect out-of-range data. VBA code is well-
suited for data-management tasks and includes all the looping constructs necessary to iter-
ate over recordsets.
Microsoft dramatically improved macros in Access 2007 by adding error handling and allowing
temporary variables during a macro’s execution. These two new features went a long way toward
making macros more acceptable to serious Access developers and allowed macros to be used in sit-
uations where macros were considered unacceptable in earlier Access versions.
Access 2007 also introduced the concept of embedded macros. An embedded macro is (typically) a
small macro attached directly to a control event on a form or report. The macro runs every time
the event is triggered, such as when a user clicks on a button or when a report’s detail section is
formatted. Embedded macros eliminate the objection that macros were always external to the form
or report they serviced.
Access 2010 continues the improvement and expansion of Access macro capabilities. In Chapter
30, I cover some of the details of using the new Access editor, as well as looping and trapping
errors in macros. This chapter specifically covers data macros, which add intelligence directly to
Access tables.
Data macros are intended to make it easier to ensure consistent data handling throughout an appli-
cation. Because data macros are applied at the table level, the exact same action happens each time
the table’s data is updated. Although the subset of actions available to data macros is considerably
smaller than user interface macros, when carefully crafted and implemented, data macros are a
powerful addition to Access applications.