databasedev.co.uk - database solutions and downloads for microsoft access

Automate applications with macros in Access 2007

If you have been using Access in the past, the easiest way to automate the tasks would have been by using the macros. On the other hand, programmers might have realized that writing VBA (visual basic for applications) modules is the best way to automate things in any Access database.

Access 2007 makes it very easy to create macros. In previous versions, many macros could not have been created without using VBA code but in Access 2007 new features have been added to eliminate the need of using VBA code.

New Features in MS Access 2007

Now you can embed macros in any event provided by the control, form or report. While the embedded macros won’t be visible in Navigation pane, it becomes part of the control, form or report where it has been created. Also if you create a copy of the control, form or report where the embed macros are present, you'll be able to find them in the copy as well. A new security feature won’t allow a macro to run if it’s not “trusted”. In order to use a database containing “un-trusted” macros you would need to grant trusted status to that database. New macro actions are now available to check for error or view the macro every step of the way. Temporary variables allow can be used each time there is a need for such a value. They can also be part of VBA code.

In Access 2007, a macro can be stand alone –and will be visible in the Navigation Pane –or can be embedded in an object –form , report – or control.

Macro Builder Feature

In order to create a macro by using the Macro Builder feature , go to the Create ribbon and on the Other group, click on Macro.

Figure 1 Click to create a macro

From the drop down list, choose Macro. This will display the Macro Builder. When you first open the Macro Builder, notice that the Action, Arguments and Comment columns are displayed. The Macro Builder can be used to create stand-alone macros.

In order to create a macro –add actions to the macro -, choose an action from the drop down list, enter the arguments and write a short comment if needed.

Figure 2 Adding an action to the macro

Macro groups

If you want a macro object to execute several related macros, you can create a macro group.

In order to create a macro group, click the Create ribbon and on the Other group, click the arrow near Macro and choose Macro (see figure 1 ). Go to the Design tab and in the Show/Hide group, click on Macro Names to display the Macro Name column.

Figure 3 Click to display the Macro Name column

Macro names have to be used when creating macro groups to distinguish individual macros from each other. A macro in a macro group ends when the next macro name is encountered.

Type the name of the first macro in the Macro Name column and the actions you need the first macro to carry out. When you are done with the first macro, move to the next empty line and type the name of the next macro. Then add the actions and so on until you have add all the needed macros and actions.

Figure 4 Two macros, each carrying out two actions

If you run a macro group from the Navigation Pane or by clicking Run (Design Tab, Tools Group), Access 2007 executes only the first macro in the group stopping when it encounters the next macro’s name.

Embedded Macros

The embedded macros are not displayed as macros in the Navigation Pane but they are stored in the event properties of controls, reports or forms. It can make the database more manageable because you do not have to keep track of endless macros. Embedded macros also have the advantages that are copied with the form, control or reports when they are copied, imported or exported.

In the Navigation Pane, right-click the form or report that will contain a macro and then click on Design View.

Figure 5 Click Design View

Click the Property Sheet and then click the control or section that contains the event in which you want to embed the macro. Click the dots (as marked in Figure 6) and choose Macro Builder and then click OK.

Figure 6 Properties Sheet

Now, in the Macro Builder click the drop-down Action list and choose the action that you want. Add as many actions as needed and then Save the Macro.

Figure 7 Add the Actions and then save the macro

The macro will run each time the event property is triggered. Pay attention that in a macro group only the first macro runs.