3 tools I would not want to work without in Microsoft Access

by Philipp Stiefel, originally published 2015-07-20

last updated January 2020-12-13

In this article I would like to introduce you to the three most important tools I use when working with Microsoft Access. This is a personal selection of course, but if you consider yourself to be a professional Microsoft Access developer, I recommend you take a look at them at least. I’m pretty confident, they could benefit your work with Access as well.

Counting down from 3rd place …

At first thought a find and replace tool might not be a very important thing to have, as there is the default find and replace feature integrated in Access already. But if you ever try out a “real” Find and Replace tool you will understand why I reconsidered that.

Originally, this section of text was about Rick Fisher’s Find and Replace, which still is a wonderful tool. Unfortunately, Rick does not respond to any inquiries anymore and thus it has become impossible to buy licenses for his tool.

Because I and many other people wanted to buy a Find and Replace tool and there was none I liked, I teamed up with a freelancer and we built a replacement product: AccessDevTools – Find and Replace.

This tool can find (and replace of course!) text in all the properties of all the Access objects in your whole database. This is an extremely useful tool. The most significant use case for me is, if I want to know in which expressions I referenced some function of mine I need to change now, to enhance or modify in a way that might be incompatible to the previous signature (parameter types and ordering) of that method. Without search and replace, that would be doable in VBA code with a compile and having the compiler complaining about non-matching call to that function. But if you use the said function in Expression in your Forms, Reports, and Queries you are totally out of luck. You would need to open each and every single object and check all the properties for a reference to that function. What a pain!

Find and Replace just searches all those properties for you. It is not only able to show a dialog for each match but can also search your whole database silently and show a sort- and filterable list of results.

There are many more uses cases for a tool like this. Imagine your client wants you to replace all occurrences of a certain term in all the visible text of an application. - Think label and form captions, constant text in textbox controlsources, VBA code, and so on.

Or imagine you took over the maintenance of an existing application and need to quickly asses which objects are referenced and affected by certain other objects before changing part of that application. Finding stuff quickly matters!

I think if you encounter any of the above situations just once and are able to resolve it quickly with a proper tool it is worth the price of $50 already.

Update 2020-12-13: I published an infomercial video on the 1.1 update of our Find and Replace for Microsoft Access tool. This is an opportunity to see some of the cool new features in action without even downloading the free trial version.

The second most important thing for me to have is not one specific tool, but the general concept of source code control (aka version control).

Many developers try to version their work by making a backup copy every now and then. While this is a sensible thing to do, it does not help much in certain scenarios. Even if you include not only application versions released to the customer, but each significant version in the development process, as you absolutely should, it is still very difficult to get a detailed overview of all the changes you made to a specific object during a development cycle.

Imagine, after several months of intensive development, you notice a new bug in the application that is really hard to track and you can’t make sense of. It probably would be helpful to find out all the changes you made to the related module(s) over that time and why exactly you made them. How would you do that with your bunch of save Access files you backed up during that time? – Well, yes, depending on the tools you have at your disposal and the comments you added to the code (you did, didn’t you?) this is possible in one way or the other.

But wouldn’t it be so much easier if you just select the module in your Access application, select the “History” feature of your source code control solution, filter the date range and see a list of all the changes you checked in, including the check in comments you were encouraged to make?

Then you could easily select any version in that list and get diff to the last version of that file know to be bug free, on the click of a button?

This makes work as a developer so much easier, I cannot understand why someone would not want to have this available.

This is just one example of the advantages of integrated source code control. There are so many more advantages of source control I would like to mention, but that would go beyond the scope of this article.

As this article is about tools to use in Access, here come the tools:

Microsoft used to supply an add-in for Access to integrate version control. Their source code control integration, based on the MSSCCI specification, is able to connect to several source code control systems. The feature set covers all important operations you would want to have integrated in the IDE and was working reasonably well. It has some quirks, which are annoying, but for me the benefits are outweighing the flaws by far.

This tool is freely available to download for Microsoft Access 2003 to Access 2010.

• Add-In for Access 2003
• Add-In for Access 2003
• Add-In for Access 2010

You might have noticed that I wrote “Microsoft used to…” and that Microsoft Access 2013 and Access 2016 are missing from the links above. – That is for a reason. Microsoft discontinued its add-in for source code control integration.

Microsoft’s decision motivated me to develop my own solution for version control integration into Access. I released my product, Ivercy, as a commercial product in September 2015. It mimics almost all the features Microsoft has in its add-in, but I did not stop there. While Microsoft just made the basic functionality for version control available in Access, I went further and added cool features that support the specific situation of Access development much better.

Ivercy not only handles the changes of files exported from Access for version control, but also monitors the changes to your Access objects, while they are still in Access with its local change tracking feature.

The topic of version control is too complex to be properly covered within this article. I will certainly write more about that in the future.

The number one tool I use for my Access development work is an add-in for the VBA-IDE. It is MZ-Tools for VBA by Carlos J. Quintero . This add-in supports you with useful additional features while you are writing code. It has lots of cool features. Here I’m going to focus on those I use most frequently myself.

“Procedure callers” lets you mark any procedure or function in your VBA code and, click the context menu item of that name and then searches your applications source code for call to this function. This is a bit like find part of find and replace, but it is integrated so much tighter into the VBA editor and really distinguishes between just the name of the method (like in a comment) and a real call of the procedure. – Only the latter is found.

There is a find and replace feature for the code as well. Unlike Rick Fisher’s Find and Replace tool mentioned above, this only searches VBA code and not the Properties of the Forms and Reports. Still the close integration into the VBA editor makes me use this much more often when working on code.

Another feature of MZ-Tools I use frequently, is the feature to insert code templates with just a keyboard shortcut. Whenever I write a new event handler procedure I hit my keyboard shortcut to insert an error handler, and there it is in an instant. This leaves no excuse for omitting error handlers in your code any more.

Other frequently used code templates of mine include a simple comment header that adds my name and the current date to a comment. - Quite useful if you are working in a team of developers. - And some boiler plate code to call a SQL Server stored procedure or to open and loop through a Recordset.

MZ-Tools has assistant-dialogs to create any type of VBA procedure. I use this feature mostly for property procedures which can also be generated from public variables. A rather tiny, but still incredible important feature for me, is to clear the immediate pane with just a single click.

For increased productivity, you can assign keyboard shortcuts to all the actions of MZ-Tools as well. So don’t even need to use the mouse to use it for your most common action.

MZ-Tools has so many features I use on a daily basis, they are just too many to mention here. If you want to do add only one single tool to improve your development work in Access, you absolutely have to check out MZ-Tools.

And finally this tool is not only available for Access but for all Office Applications that support VBA development.

This article, including the screenshots, was originally written about the free version 3.0 of MZ-Tools. In autumn 2015 Carlos released a completely rewritten version 8.0 of MZ-Tools for VBA. The new version has several major improvements, like Office-64-Bit support and a setup that does not require admin permissions. But this comes at a prices. Literally. MZ-Tools is not free anymore, but I assure you it is worth every single penny!

I hope you could find some useful information in this article. If you have feedback or would like to recommend a tool, just send me an email.

Share this article: