Today, I thought I’d explore AutoExec macros a little today.
What is an AutoExec Macro Exactly?
The short answer is it is a macro that is automatically runs when the database is opened, assuming the database is Trusted or had its content enabled!
What Purpose Is an AutoExec Macro?
They are great when you want to run some code prior to allowing the user to interact with the actual database. They can be used to:
- Check linked tables and re-link them automatically
- Enforce security restrictions
- Setup folder/file structures required by the database
- Perform automated backups
- Open/run database objects (tables, queries, forms, reports, macros, …)
- Import data
- …
What is a Display Form?
A Display Form is a database option you set that specifies which form is opened by default when the database opens.
It is set by going
File -> Options -> Current Database -> Display Form
What Runs First, The Display Form or AutoExec Macro?
The answer here may surprise you.
If you setup some basic logging, TempVars, … on both a Display Form and AutoExec macro you will see that the Display Form’s code executes first, which was contrary to what I expected to happen. That said, the Display Form doesn’t actually get rendered until after the AutoExec macro has run. It’s as if the AutoExec macro is called somewhere in the Display Form’s initialization process.
Why Do We Need An AutoExec Macro When We Have a Display Form?
This is an excellent question.
I’ve never been a huge proponent of the Display Form database option because it is ‘hidden’ and I don’t like things to be hidden and I don’t like wasting my time clicking through menus and dialogs. You need to go through multiple menus to see it/control it whereas the AutoExec macro is right there in the Navigation Pane.
I also believe general functionalities and initialization procedures don’t have their place in a Display Form and truly should be called in the AutoExec macro. Before loading all sorts of libraries, object classes, … I want the startup to be as minimalistic as possible, so I try to use the AutoExec which then has all it’s code in a single module, thus loading the least possible until the startup process is complete, then move on to loading database content.
Which One Should Be Used Then?
Since the Display Form is actually triggered first, you can’t rely on the AutoExec to perform tasks like relinking, security, … prior as they actually will occur after. This is one reason why when using the AutoExec it is best to do the entire job, including launching the default application form as its last command. This guarantees the sequence of events and sets everything in one location for maintenance.
On the other hand, the nice thing with the Display Form is that it will display even if the code isn’t trusted. It obviously won’t run its code, but it will display the form. This is why a Display Form can be used to display startup message to the user even if active content is disabled. This is a great way to display a message about setting up a Trusted Location for instance.
Personally, I like to use the AutoExec to do everything: check for missing VBA references, table re-linking, security checks, backup, … and lastly launch the menu or default form, but that is just my personal preference.
To me thought, the important thing here is not to use both as you could end up with things not happening in the order you’d expect. Pick one, and solely use that startup approach.
A Side Note
I was helping someone on UtterAccess who was using the Display Form property and wanted to hide the form as soon as the database opened. A bit of a weird case, but basically they used the Display Form to initialize things and then open another ‘main’ form. Because of the sequence of events, it wasn’t possible, not easily at least. You can’t use Me.Visible = False, not even in the secondary form! You can’t use an AutoExec macro either as it fires before the forms are actually rendered!
Although a bit of an extreme case, this illustrates again to me, why I would simply do everything in an AutoExec macro and, if need be, open the form in hidden mode using plain simple VBA!
Well this article has me on my heels. Over the years I thought the accepted “best practice” was to avoid AutoExec Macros – not that I had done any research on my own but because no self-respecting Access Dev uses [spit]Macros[/spit] and since AutoExec IS a [spit]Macros[/spit], there it is.
But when Access Heavy Weights such as you and others (another site I visit every morning mentioned this very subject recently) tout its usefulness, I have some rethinking to do.
As always, good stuff! Thanks and please keep them coming…
I only use 2 macros in my databases, AutoExec & AutoKeys, that’s pretty much it. I avoid them like the plague.
The other option is to use a Display Form and set all your startup code behind that. It’s a choice at the end of the day.
As I read the line “the nice thing with the Display Form is that it will display even if the code isn’t trusted” a world of headaches I deal with began to evaporate before my very eyes… until I read the next line “It obviously won’t run its code”.
Arrgh!
For many clients the “trusted location” problem together with the “relinking backend” is always a disaster. At least initially. And especially on network folders. This would have been such a sweet workaround. Too bad!
Still, interesting to know that the Display Form runs first. Makes no sense to me, but Microsoft I guess.
PS: I also only use the “autoexec” macros.
PPS: I do occasionally use data macros. They have the one benefit, that they execute whether or not VBA is enabled.
The only genuine purpose for Display Form, IMNSHO, is to display a form that has a screenshot of the warning bar when a database isn’t Trusted/Enabled with instruction to get it functional. It is a default warning and automatically closes if code is properly enabled.
As for helping clients with Trusted Locations and all of that, I typically setup my clients with a shortcut to a VBScript. The VBScript copies the master copy of the db to their local PC, sets up the necessary registry settings for the FE Trusted Location and then launches the db. So the user does absolutely nothing more than double-click on a shortcut!