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.
Related Articles
* Book Review: Access 2010 Programmer's Reference
* Top 10 Productivity Tips for Microsoft Access 2010
* Getting Up To Speed With Microsoft Access 2010
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.
Full article...
