Update another table using Data Macro in MS Access 2010

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])


Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SQL, Database and tagged , , , , . Bookmark the permalink.

One Response to Update another table using Data Macro in MS Access 2010

  1. Ali says:

    Great post. I was looking for this all over the internet but no one had this right. Thanks for posting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s