DataWright Information Services

Consulting and Resources for Excel and Access

Why do some macros not run in Access 2007?

Access 2007 has a number of new features that enhance macros.
This gives you more flexibility and allows you to create an Access
application that will work even if VBA has been disabled. However,
not all macro commands are created equal and, in the interests of
security, a number of them will only run if the database is granted
trusted status. The list is shown below.

Macro Command Macro Command Macro Command
CopyDatabaseFile OpenView SetValue
CopyObject PrintOut SetWarnings
DeleteObject Quit ShowToolbar
Echo Rename TransferDatabase
OpenDataAccessPage RunApp TransferSharePointList
OpenDiagram RunSavedImportExport TransferSpreadsheet
OpenFunction RunSQL TransferSQLDatabase
OpenModule Save TransferText
OpenStoredProcedure SendKeys

How do I know which commands are not trusted?

If you open a macro for editing and see yellow warning
icons in the left margin of any macro action, that action is
on the “naughty” list.

a macro using unsafe actions

How can I restrict myself to “safe” actions?

On the macro’s Design tab is a toggle button labelled
Show All Actions (see right). When pressed, all actions are
available; when released, only the safe actions can be
show all macro actions

If I need to run “unsafe” actions, what are my options?

Use the Trust Center to define the database folder as a trusted

Microsoft has written a useful article about trusting databases
and using the Trust Centre. You can see it

. Basically, when you open the database you should get a
warning message. By clicking the Options, you can enable the content
for that session. To permanently remove the message you can move the
database to a trusted location, or you can define the database’s
folder as a trusted location.

Convert the macro to VBA

This may sound weird but sometimes it is your only option.
Recently I came across a database containing a single macro that had
stopped working. The message bar was not coming up so we couldn’t
trust that session. The file was being accessed on Terminal Services
and we were unable to trust the database’s location. Converting the
macro to code, and attaching that code to a button on a form, forced
the warning message to appear when the database launched. The users
have to trust each session, but at least the code runs.

To convert a macro to a module in Access 2007, do the following:

In Access 2007 click the Home button, then Save As, and
Save Object As
In earlier versions of Access, select File > Save As

select the Save As option to convert a macro
In the dialog, pick Module as the option in the
drop-down list

choose Module from the options
By default, Access will offer to include comments and
error handling. Click OK

Access offers to include comments and error handling