Leveraging Data Macro in Microsoft Access 2010
October 4, 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
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.
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.
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.
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.
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.
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!
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.