Create a data macro

Data macros are a new feature of Access 2010 which enable you to add logic to events that occur in tables, such as adding, updating, or deleting data. They are similar to "triggers" in Microsoft SQL Server. This article shows you how to create and debug data macros.

About data macros

Data macros are managed from the Table tab while viewing a table in Datasheet view, and do not appear under Macros in the Navigation Pane. Among other things, you can use data macros to validate and ensure the accuracy of data in a table. There are two main types of data macros—those that are triggered by table events (also called "event-driven" data macros), and those that run in response to being called by name (also known as "named" data macros).

Top of Page

Create an event-driven data macro

Table events occur whenever you add, update, or delete data within a table. You can program a data macro to run immediately after any of these three events, or immediately before a delete or change event. Use the following procedure to attach a data macro to a table event:

In the Navigation Pane, double-click the table to which you want to add the data macro.
On the Table tab, in the Before Events group or the After Events group, click the event to which you want to add the macro. For example, to create a data macro that runs after you delete a record from the table, click After Delete.

Note: If an event already has a macro associated with it, its icon appears highlighted on the ribbon.

Access opens the Macro Builder. If a macro was previously created for this event, Access displays the existing macro.
Add the actions that you want the macro to perform.
Save and close the macro.

For more information about creating macros, see the article Create a user interface (UI) macro.

Top of Page

Create a named data macro

A named or "standalone" data macro is associated with a specific table, but not with a specific event. You can call a named data macros from any other data macro or standard macro.

In the Navigation Pane, double-click the table to which you want to add the data macro.
On the Table tab, in the Named Macros group, click Named Macro, and then click Create Named Macro.

Access opens the Macro Builder, where you can begin adding actions.

For more information about creating macros, see the article Create a user interface (UI) macro.

About parameters

Parameters enable you to pass values into a named data macro so that they can be used in conditional statements or other calculations. They also enable you to pass object references from standard macros into the data macro.

Note: Parameters aren't available in event-drive data macros.

To add a parameter to a data macro:

At the top of the macro, click Create Parameter.
In the Name box, type a unique name for the parameter. This is the name that you use to refer to the parameter in expressions.
Optionally, type a description for the parameter in the Description box. We recommend that you enter a description because later, when you use the parameter, the description text you type here is displayed in a tooltip. This helps you remember the purpose of the parameter.

To run a named data macro from another macro, you use the RunDataMacro action. The action provides a box for each parameter that you created, so that you can provide the necessary values.

Top of Page

Manage data macros

Data macros do not appear in the Navigation Pane under Macros. You must use the ribbon commands in Table Datasheet view or Table Design view to create, edit, rename, and delete data macros.

Edit an event-driven data macro

In the Navigation Pane, double-click the table that contains the data macro that you want to edit.
On the Table tab, in the Before Events group or the After Events group, click the event for the macro that you want to edit. For example, to edit the data macro that runs after you delete a record from the table, click After Delete.

Note: If an event does not have a macro associated with it, its icon is not highlighted on the menu.

Access opens the Macro Builder, and you can begin editing the macro.

Edit a named data macro

In the Navigation Pane, double-click any table to open it in Datasheet view.
On the Table tab, in the Named Macros group, click Named Macro, and then point to Edit Named Macro.
In the submenu, click the data macro that you want to edit.

Access opens the Macro Builder, and you can begin editing the macro.

Rename a named data macro

In the Navigation Pane, double-click any table to open it in Datasheet view.
On the Table tab, in the Named Macros group, click Named Macro, and then click Rename/Delete Macro.
In the Data Macro Manager dialog box, click Rename next to the data macro that you want to rename.

Access selects the current macro name.
Type the new name or edit the existing name, and then press ENTER.

Delete a data macro

Use this procedure to delete any named or event-driven data macro:

In the Navigation Pane, double-click any table to open it in Datasheet view.
On the Table tab, in the Named Macros group, click Named Macro, and then click Rename/Delete Macro.
In the Data Macro Manager dialog box, click Delete next to the data macro that you want to rename.

Note: You can also delete an event-driven macro by deleting all of its actions.

Top of Page

Debug data macros

Some common macro debugging tools, such as the Single Step command and the MessageBox macro action, are not available for data macros. However, if you are experiencing problems with a data macro, you can use the Application Log table in combination with the OnError, RaiseError and LogEvent macro actions to help find data macro errors.

Display the Application Log table

The Application Log table is a system table (named USysApplicationLog) which by default is not displayed in the Navigation Pane. If an error occurs in a data macro, you might be able to tell what happened by viewing the information in the Application Log table.

Use the following procedure to display the Application Log table:

Click File to open Microsoft Backstage view.
On the Info tab, click View Application Log Table.

Note: If you do not see the View Application Log Table button, this means that an Application Log table has not yet been created for this database. Once an event has been logged, the button will appear on the Info tab.

You can create rows in the Application Log table by using the LogEvent action or the RaiseError action, and you can control how Access reacts to errors by using the OnError action.

Microsoft Office Tutorials

Tuesday, August 21, 2018