Share via

Macro improvements in Access 2007

  • Article

Access 2007 and Macros
One feature that might be surprising to many experienced developers is the re-invigoration of macros in Access 2007. We have done a bunch of work in this area that we think benefits end users and developers in some scenarios. If you are running Access 2007 you will notice macros are used extensively in our templates and the button wizard and switchboard manager creates embedded macros.

History of Macros
Many people think macros were a predecessor to Visual Basic, but it turns out that they were introduced at the same time. However, they have different goals. A large majority of knowledge workers are intimidated and frightened by free-flowing code, but they are more comfortable with using a structured grid such as the macro designer. We found that there are many scenarios where users are very successful "programming" using macros. Macros target a broader audience of Access users that don't have the skills to drop into VBA and write code. Yet, these users are very successful picking an action and setting a couple parameters. This is why many applications built by knowledge workers are loaded with all sorts of macros.

Unfortunately, the Access team has not focused on knowledge workers for a long time. We have traditionally been more developer focused, so our macros have not received much attention. One of our goals for Access 12 has been making the product easier to use by a broader audience (while still delivering features relevant to developers). We think there are many knowledge workers that are tracking information in Excel that could be more appropriately stored in a database. As you can tell from previous posts, Access 2007 has more new knowledge worker features than previous releases. I also think there are some nice enhancements for developers building bigger applications.

There are a number of things we did to improve the macro experience.

Embedded Macros
Just like you can save a SQL Statement in the Row Source or Record Source property, you can now save a macro in any event property. These are called Embedded Macros. Similar to SQL Statements, you can use Save As to create a standalone macro that can be reused. One nice thing about Embedded Macros is that you can copy/paste and "Open Form" button and just change the form name that it opens, and you are done.

When you think about templates and renaming objects--name fix-up becomes really important for users that want to start with one of our templates and modify it to more closely reflect their business. In this case, it is far easier for Access to perform name fix-up operations on macros than try to fix-up code. Think of the scenario of renaming the Contacts table to Employees in the Issues template. Many customers will want to do that—It would be really hard for us to do all the right things if all the logic was expressed in code.

Macro Sandbox
The biggest thing that we added to the macro architecture was the concept of a "sandbox". Certain actions can run even when a database is not trusted. You can think of these macros like expression or formulas in Excel. VBA can never be trusted, so it never runs in an un-trusted database, but you can now use macros to build simple applications that are fully-functional without having to trust the solution. This was our goal for templates. They will leverage these macro that are allowed even when the instantiated template is not trusted.

The Show All Actions button switches the contents of the drop-down list in the Actions column between all actions and only the actions that are allowed in databases that have been trusted. The yellow warning sign in the record selector indicates the action won’t execute unless the database is trusted.


(Click image to enlarge)

Variables and TempVars
We also introduced temporary variable that are accessible via VBA and macros. TempVars are designed to store variant data that is transient in nature. Think of them as global variables that work across the whole product—anywhere there is VBA or an expression. They are exposed as an enumerable collection off the Application object. There are two properties Name and Value and two methods Remove and RemoveAll.

There are four new macro actions associated with TempVars:
(1) TempVars![Name]returns the variant value that was stored.
(2) SetTempVars(Name, Expression) allows macros to set a TempVar to a given value.
(3) RemoveTempVars(Name) removes the TempVar from the store.
(4) RemoveAllTempVars empties the TempVars store.

Here is an example of TempVars in use in a macro:

 
(Click image to enlarge)

Error Handling
One of the key weaknesses of macros in the past is the inability to handle errors. Well, that is not longer true. We added an OnError action that is similar to the On Error statement in VBA. OnError has three different settings:

(1) 'Next', which is equivalent to "On Error GoTo Next" in VBA, and allows you to keep on running the macro while ignoring AND suppressing all errors.
(2) 'Fail', which is similar to “On Error GoTo 0” in VBA, will revert error handling to the default behavior of having macros fail completely on hitting any errors executing actions.
(3) 'Macro Name' is directly related to the 'Macro name' parameter. This allows you specify a label on the macro to where the macro will jump if any errors are found. This is very similar to the "On Error GoTo " in VBA. You can easily use this to put up your own error messages:

OnError (Macro Name, "Error")
OpenForm (Form, "Form1", …)

Error: MsgBox ("Error: Custom message", …)

Button Wizard
One frequent complain that we hear about Access 2003 is the lousy code the button wizard generates.
For example, if you create a “Record Operations > Print Record” button, this is the VBA it generates:

Private Sub cmdPrintRecord_Click()
On Error GoTo Err_cmdPrintRecord_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Exit_cmdPrintRecord_Click:
Exit Sub

Err_cmdPrintRecord_Click:
MsgBox Err.Description
Resume Exit_cmdPrintRecord_Click

End Sub

How do you like the DoMenuItem action? Yeah, I have memorized what the 8 means…

Here is the equivalent embedded macro:

RunCommand (SelectRecord)
RunCommand (PrintSelection)

All the commands in the button wizard will now generate safe macros that execute property in un-trusted databases. We even updated the icons in the wizard!

Macros and Developers
One thing I would be sure to emphasize is the fact that macros behind form controls keep their contents even via copy/paste, whereas VBA events are lost.

As a developer, if you write VBA code to do the majority of the interesting work, consider creating a simple embedded RunCode macro on the buttons that call the VBA code as opposed to VBA events directly behind the buttons. The embedded macro acts as the bridge between the UI and the VBA code. That gives the full functionality of the UI designer with copy/paste as well as the robustness of the VBA programming model.

The new Northwind sample was developed this way where many of the simple open form with a filter functionality is stored in embedded macros. When we needed to do the heavy lifting we used VBA. Turns out there is far less code and the code that is there is more interesting.

Comments

  • Anonymous
    February 14, 2007
    Hi Clint Macros and Developers? I don't expect many of the existing developers will start heavily use macros instead of VBA code, except if they want to close the applilcation if it isn't trusted during startup. But not for the mentioned reason. ;-) If I have a button that I want to copy and reuse, such like an OpenForm button I once write a public VBA function OpenAForm(FormName As String) and instead of writing code behind all of the OpenForm Buttons I do it by writing =OpenAForm("MyForm") in the On Click property. This will be copied, too and will be even easier to maintain than using a macro instead as I don't have to open the macro editor to change the form name. Don't forget, your argument "untrusted Access application" doesn't count for developers, they will close their applications in most cases if the user doesn't trust them. Would you like to work for somebody that doesn't trust you? On the other side: I expect to see a lot of new coming PowerUsers (not developers ;-) ) using macros in the future in the mentioned way. Introducing embeded macros to prevent the macro mass in the scripts database container was an excellent move - congrats on this - and as we now finally can catch errors (how long did we ask and wait for this feature? 14 years?) in macros and even share variables between macros, expressions and even VBA code I feel macros will get a better reputation than it did in the past and am sure we will see a macro hype for A2007. It's on the right way now, so why should end users start to learn VBA, macros you don't have to learn, you just click these together. Henry, Phuket, Thailand

  • Anonymous
    February 14, 2007
    Hello Clint, Why Access 2007 uses so much RAM memory regarding XP/2003 versions? Bye:  Ken Ellis

  • Anonymous
    February 15, 2007
    The comment has been removed

  • Anonymous
    February 15, 2007
    The comment has been removed

  • Anonymous
    February 15, 2007
    Good comments Henry. Ken--I'm not seeing a big jump in the RAM working set from Access 2003 and Access 2007. I booted Access 2003 with Northwind and task manager indicated it was using 14,112 K. A similar app with Vista and Access 2007 actually had a smaller working set of 10,804 K. I don't think there will be any change to the security model going from regular Access applications to runtime applications. This is consistent with all Office applications. Trusted locations is the way to ensure you app will run with full trust. David--nice correction. I will update the post to say Visual Basic.

  • Anonymous
    February 15, 2007
    Clint: "Ken--I'm not seeing a big jump in the RAM working set from Access 2003 and Access 2007. I booted Access 2003 with Northwind and task manager indicated it was using 14,112 K. A similar app with Vista and Access 2007 actually had a smaller working set of 10,804 K" me: ok, I'm sorry:  I have tested the matter hastily Clint: "I don't think there will be any change to the security model going from regular Access applications to runtime applications. This is consistent with all Office applications. Trusted locations is the way to ensure you app will run with full trust" Clint ... the protection warnings are very frustrating for all developers: it isn't only my opinion For example, from: http://www.sagekey.com/access_2003.aspx "Key Features Digital Certificate not required -  Installations built using our tools will not display the Macro Security Warning Message "The file may not be safe if it contains code that was intended to harm your computer" when run without a digital signature" the Office security model (in developer perspective) creates more problems than those that resolve. Bye

  • Anonymous
    February 20, 2007
    Regarding SageKey's disabling of the security warning - I have the product, and I believe they modify those "pseudo-secret" registry settings that set your security level, thereby eliminating the warnings. Obviously, there are pluses (and according to the hyper-paranoid,  minuses) to this approach.  The risk is actually extremely, extremely small for users running Access databases from known sources.  Further, my experience is that nearly all users will opt to run code from unknown sources even when confronted with a security dialog.  So I personally don't think that the security dialog helps anyone.  It is probably only important from a MS liability POV. As long as the code can't "auto-run" automatically from email or a website - that is all I care about.  Once the user has double-clicked on an Access database, he has already consented to run code, for all practical purposes, and a security dialog is just an annoyance.   For example, why don't I get a security dialog when I run Notepad?   What about a .NET application? Why should I trust the MS developers, yet not trust the people that send me Access databases?  Obviously, when I download a database from a web site, I implicitly trust the database.  Otherwise, I would not have downloaded it!  This there is no need for a security warning - ever! Clint, do you disagree with this???  If so, then why?  

  • Anonymous
    February 21, 2007
    The comment has been removed

  • Anonymous
    April 21, 2007
    A reader recently asked: Is there any way on Access 2007 to keep wizards and Switchboard Manager coding

  • Anonymous
    April 21, 2007
    Clint Covington gaat op zijn blog in op een vraag over VBA code in Access 2007: A reader recently asked:...