Setting Up a New MS Access Development Environment

Five settings and two addins that vastly improve your quality of life as a Microsoft Access developer.

Setting Up a New MS Access Development Environment

There are a few settings I configure and add-ins that I install whenever I'm setting up a new VBA development environment:

  • Enable Option Explicit
  • Turn off automatic syntax checking
  • Set up Dark Mode in VBA
  • Configure the Quick Access Toolbar
  • Add my dev folder to Access's Trusted Folders
  • Install MZ-Tools
  • Install vbWatchdog

Enable Option Explicit

Including Option Explicit at the top of every code module is an easy way to turn many common runtime errors into compile errors.  That's a good thing because compile errors are cheaper and easier to fix than runtime errors.

By default, you have to remember to add the Option Explicit line to every new module that you create. A much better alternative is to let VBA do that for you by enabling the Require Variable Declaration option.

VBA IDE > Tools > Options > [√] Require Variable Declaration

Turn Off Auto Syntax Check

The automatic syntax check is a super-annoying "feature" that displays a message box every time you write invalid syntax.

It's annoying because most of the time the syntax errors that it highlights are instances where you're simply not finished writing a line of code.  For example, you start writing an If ... Then statement but can't remember the name of the variable you need to compare in the condition.  As soon as you move the cursor off the line with the incomplete statement, VBA hits you with a message box warning you that you've written invalid syntax:

Yes, I realize that the keyword If all by itself is invalid syntax.  I don't need a message box to remind me.  This is especially annoying since the invalid syntax can be visually identified by the red font.  Turning off this "feature" is always one of the first things I do:

VBA IDE > Tools > Options > [_] Auto Syntax Check

VBA Dark Mode

There's no literal "Dark Mode" setting, but you can get the same effect by modifying the Code Colors in the Editor Format tab of the VBA IDE Options dialog:

Tools > Options > Editor Format > Code Colors

Dark Mode in VBA
VBA may not have a preset “Dark Mode” like other modern development environments, but it’s not hard to create one yourself.

Configure the Quick Access Toolbar (QAT)

I dock a handful of my most-frequently accessed ribbon commands on the Quick Access Toolbar:

Customizing the Quick Access Toolbar in Microsoft Access
Get back some of that pre-2007 Office toolbar goodness with the Quick Access Toolbar (QAT). Vote for your favorite QAT commands.

Add Primary Dev Folder to Trusted Folders

Microsoft has gotten very aggressive with its VBA blocking behavior in recent years:

Timeline for VBA Macros Blocked by Default in Microsoft Office
Enterprise customers are next in line to have macros blocked by default in Office files from the web. The policy addresses a real problem, but does it actually help?

The policy is understandable, though I expect it may lead to unintended consequences that actually reduce security (e.g., if users work around the issue by adding their Downloads folder as a trusted location).  

The policy exists mainly to help protect non-technical users.  I assume if you are reading this blog, you don't fall into that category.  

File > Options > Trust Center > [Trust Center Settings...] > Trusted Locations > [Add new location...]

If you'd prefer to add machine-wide trusted locations, you can do that by editing the HKLM registry hive:

Adding a Machine-Wide Trusted Location in MS Access
Let me show you how to add a Trusted Location for every user on a device even if that device doesn’t have a full copy of Microsoft Access.

Install MZ-Tools

If you spend any amount of time writing VBA code, you need to use MZ-Tools.  It's a commercial product, but it's worth every penny.  The advanced find dialog is worth the price of admission all by itself:

Install vbWatchdog

I've written at length in the past about vbWatchdog.  I would never want to return to the dark days before vbWatchdog when I was adding more than half a dozen lines of error-handling boilerplate to almost every procedure I wrote.

Error Handling Evolution
How you handle errors says a lot about you as a programmer. Most people evolve with experience in how they handle errors. From the most naïve to the most advanced, here is what that evolution looks like.

Reader Submissions

What application-level settings do you change when configuring a new Access development environment?  Let me know in the comments below.

NOTE: I intentionally omitted database-level settings–such as turning off Name Autocorrect–from this list.  I plan to cover those settings in a separate article.

Image by Rudy and Peter Skitterians from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0