Leveraging Data Macro in Microsoft Access 2010

Finally, Microsoft Access developers have a version of table triggers at
their disposal. Follow along as Danny Lesandrini walks through an example of
implementing a Data Macro in Access 2010.

During beta testing, I was most excited about the new Data Macros (table
triggers) in Access 2010. At the time I had trouble getting them to work but I
attributed that to my lack of understanding. Now that the public release
version of Access 2010 is available, I’m looking more closely at Data Macros
and while I’m still excited, I’m only a little less confused about how to
implement them.

I had wanted to write a comprehensive article about how to build Data Macros
but no longer think I’m qualified to do so. Instead, I’ll settle with
demonstrating how I replicated a SQL Server table trigger I currently use into
Access 2010 and explain some of the tricks along the way.

The Sample Trigger

At the company where I work, we are trying to consolidate and integrate all
the different SQL Server databases we have. For example there’s an HR database
for requesting Sick and Vacation leave time and a Business Labor Accounting
(BLA) database where time spent on projects is logged in a Timesheet fashion.
When it was decided that Sick and Vacation hours should be included in the BLA
time sheet, we were faced with a double data entry dilemma.

What if a SQL Server trigger was put on the HR_tblEmployeeLeave table that
automatically entered a record in the BLA_tblTimesheet table when a Sick or
Vacation leave was approved by HR? While the logic wasn’t trivial, it was
doable and soon we had a system that synchronized the two tables. This is
something one couldn’t have begun to imagine programming in Access 2007 (let
alone earlier versions) but the Access 2010 Data Macro makes it possible. The
end result is available for review in the download
but first consider the steps required.

1) We begin by creating two tables: HR_tblTimesheet and BLA_tblEmployeeLeave
2) Next create a Data Macro for the AfterUpdate event of the table
3) By necessity, this is followed by figuring out how to debug the macro.
4) Finally, watch with pleasure as the macro does its job!

Build the Macro

To add a Data Macro, open the table in design mode and look for the Create
Data Macros button on the Design menu. It will give a drop-down list of
available macro events. For this task, I selected the After Update macro.

After Update Macro

The user interface for building macro steps is very intuitive and the
If-Else-End If logic will be very familiar to VBA developers. Intellisense is
there to help you and the familiar Access "Builder" tool provides
assistance as well. I quickly worked my way through an IF block that says,
"If an HR Approval Date is added, create a clone of this Leave record in
the Time sheet table." (See the screen shot farther down below.)

Problem was, it didn’t work and it was really frustrating. There are
apparently little quirky rules about building macros that one needs to know.
So, I pulled out my copy of the Access
2010 Programmer’s Reference
and read chapter 4 about macros. It helped me
understand two things. First, the Alias property was very important (as will be
shown later) and second, you can debug the triggers by looking into the new
Application Log table, which is available from the File menu as shown in the
screen shot below.

App Log Debug

The log was telling me, among other things, that it couldn’t find the table
I was inserting into. As it turns out, this target table maps, not to the
actual table name, but to the alias name you give it. Once I took care of that
I ran into another issue with some syntax in my IF condition, which was also
explained in the Application Log, and next thing I know, the trigger is working.

Below is the macro I built. The top section identifies both the action
(Create a Record In) and the target table (my BLA_tblTimesheet). The Alias was
causing my problems so I simply called it NewRecord. This, as it turns out,
will become the target table name of the SetField action used in the next
steps.

In this case, I want to trigger the macro only once, only the FIRST time the
HRApprovalDate is populated so I check its [Old] value to verify it’s empty and
then its current value to see it’s not null. I had tried to use SQL Server
syntax of [FIELD] IS NULL but that was causing the other error described in the
application log. Once I implemented the Access Nz() function against the field,
it could evaluate correctly and proceed into the section that executes the
SetField actions.

As you see, I am setting the field values for the alias [NewRecord], not for
the table [BLA_tblTimesheet]. I’m able to map these fields to existing field
values in the current table or simply add text or other "values" as the
task requires. This is where the Intellisense feature really pays off.

Record Status_01

Once the Data Macro and Table are saved, I opened the HR_tblEmployeeLeave
table in datasheet view to add a record, leaving the HRApprovalDate empty. The
record saved and with no execution of our trigger, as one would expect.

Record Status_02

Next I added an HRApprovalDate. The screen shot below shows both tables
prior to moving the focus from the field. At this point, the new date value has
not been saved and the Data Macro has not yet fired. The record has not yet
been updated.

Record Status_03

However, once you tab out of the field, the macro does execute and since the
condition is met, the result is the creation of a new row in HR_tblTimesheet
with the approved leave data. I was disappointed that the table didn’t refresh
itself but after closing and reopening HR_tblTimesheet I saw the trigger had
done it’s job. Mission accomplished!

Data Macro Menu

Next Steps for Data Macros

The next steps for data macros are infinite. I mean, what do you want to
accomplish? This same bit of logic above could be adapted to update audit
tables that record every change made to your critical data tables. Calculations
could be performed and appropriate actions taken. An email could be sent out
under some situations where someone, perhaps, needs to be informed that
something in the table has changed.

As I said at the outset, I’m new to Data Macros so I don’t even know all the
potential uses of it yet, but I do know that it’s a feature that is LONG
overdue. I recall discussions in the newsgroups in the 90’s where people were
asking for "table triggers", which is effectively what Data Macros
are. Well, now we have them.

»


See All Articles by Columnist

Danny Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles