Zahid's blog on Data Engineering and BI

Recently, I had to find a way to write a trigger in MS Access 2007. My aim was to update a field in one table (let’s say Project Table) based on Insert/Update/Delete operation on another table (Let’s say Investors table). Quickly, I realized that MS Access 2007 does not offer much when it comes to triggers, but Access 2010 does. It provides a feature called Data Macro. So, what I did was to write a Data Macro which updates the Project table. The Macro looks like below:

Macro Name: UpdateProjectAmount:

So, it takes the Project Id, AmountPledged, AmountReceived as parameters. Then, inside the procedure, it adds those amounts to the ProjectAmountRaised_Pledged and ProjectAmountRaised_Received field respectively for the corresponding ProjectId.

It’s pretty simple from now on. All I had to do was to create After Insert, After Update and After Delete triggers for the Investor table and call the above macro accordingly with the right values supplied as paramaters. Below is the descriptions of those triggers. Look at the use of Keyword “Old” in the Macros.

1. After Insert:

This macro gets the AmountPledged and AmountReceived from    each new record and send them as Parameters to the Macro called UpdateProjectAmount. The newly inserted ProjectId is also sent as a parameter.

RunDataMacro(Investors.UpdateProjectAmount,[ProjectId],[AmountPledged],[AmountReceived])

2. After Delete:

This macro gets the AmountPledged and AmountReceived from each deleted record and send them as Parameters to the Macro called UpdateProjectAmount. The newly deleted ProjectId is also sent as a parameter.

RunDataMacro(Investors.UpdateProjectAmount,[Old].[ProjectId],-[Old].[AmountPledged],[Old].[AmountReceived])

3. After Update:

This macro gets the updated AmountPledged and AmountReceived from the updated record and send them as Parameters to the Macro called UpdateProjectAmount. The newly updated ProjectId is also sent as a parameter.

RunDataMacro(Investors.UpdateProjectAmount,[ProjectId],[AmountPledged]-[Old].[AmountPledged],[AmountReceived]-[Old].[AmountReceived])