Topics

These rules are intended as a guiding philosophy. If I’m getting closer to meeting this vision, I know I’m moving in the right direction.

In those early days, our method of “source control” was making regular copies of the front-end database and appending a date to the filename. This was....less than ideal. The problem is best illustrated with a quick story.

I believe without a doubt--even knowing what I know now--that I made the right decision at the time. If I could go back to 2009 and do it over again, I would still go with Mercurial. If I were starting from scratch in 2020...that’s a different story.

Using version control is the most impactful change you can make to improve the quality of your Microsoft Access applications. Here is a quick list of just some of the benefits you gain using version control.

Before we can use a DVCS with our Access application, we need to convert the binary Access file into a series of text files. The best place to start (and where my journey began) is with this StackOverflow answer.

So, how can we compare those database properties? By iterating through the database’s .Properties collection, of course. Here is some sample code from my modified decompose.vbs script.

The DAO Database object has a Properties collection. You can read through the list of properties to extract saved database options. You can also add your own properties to the object.

In my previous post, I talked about adding custom properties to the database object. I covered how to do it, but did not go into any detail about why you might want to do it. Let’s remedy that now.

Anything that can lead to a bug in your software belongs in version control. That includes local tables with design-time data.

While we can use the VBA user interface to display the references, we can’t rely on that approach if we want to automate exporting our Access binary file to text files. Luckily, there is a solution.

Another potential source of bugs is changes to the structures of our tables. This includes both local tables and linked tables that reside in our back-end.

SaveAsText will export queries to text files, but calling those files “human-readable” is a stretch.

I expanded on the original decompose.vbs script. The code below is the culmination of more than 50 individual changes and tweaks over 10+ years.

Resisting the packrat mentality.

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.

Introducing a frictionless alternative to Err.Raise.

Once again, I borrowed an idea from Python. To smuggle this concept into VBA, I turned to a rarely-used language feature...

Doc tests are not a replacement for unit or integration testing. But they do provide the best return on investment (ROI) of any type of test, mostly because the effort to write them is near zero.

Let’s take our doc tests to the next level. I’ll show you how to use my custom DocTests function to test classes, private functions, and error conditions.

Microsoft Access is both a rapid application development (RAD) environment and a file-based database system. It is actually quite capable in each area. But they are two fundamentally different functions.

Arguments in VBA are passed by reference by default. What does that mean? And what are the differences between passing by reference and by value?

Interfaces can be a difficult concept to grasp conceptually. To help make sense of the concept, consider these real world examples.

A real world example using Active Directory lookups.

How can you completely change the way you interact with a class without breaking backward compatibility? Interfaces.

How do you reuse a form with external dependencies in multiple projects? Interfaces.

For a few common dependencies, we can cheat by using a singleton class. Within that class, we’ll type those dependencies as interfaces.

How to clean up your object’s auto-complete dropdown list when it starts to look like that of the MacBook Wheel.

When is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.

Two kinds of rounding, the VBA language spec vs. the Office VBA implementation, and a drop-in replacement for VBA.Round().

Form.Recordset.Requery. That’s it. That’s the whole article.

One can check the status of screen painting in Excel, but not in Access. This turns out to be an important shortcoming. Let’s remedy it.

Why use keyboard shortcuts from Access 2003 when developing in 2020? Because starting with Access 2007, the new shortcuts suck.

VBA likes to change the casing of our code (upper/lower/mixed). It may seem random, but it’s not. Read all the details here.

Here is a list of my favorite utilities, add-ins, OCX controls, and applications that I use when developing Microsoft Access applications.

Ever use a keyboard without a Pause/Break key? It’s no big deal for most people, but if you develop in VBA it’s a huge deal.

What if you could highlight any text, press a hotkey, and have Windows do what you want almost every time? You’d have my top productivity hack.

Use AutoHotkey to bring consistency to your Access color schemes and save time when designing forms.

Testing your program for small screens? You could change your own monitor’s resolution, but that gets annoying real fast. Here’s a better way.

Don’t let these gotchas trip you up when using SQL Server as a backend for Microsoft Access.

If you’re managing SQL Server and you’re not using Ola Hallengren’s scripts, then you’re doing it wrong.

The fastest, simplest, and cheapest way to clone a SQL Server database for debugging or testing. Even works with SQL Server Express!

The four best tools/techniques for tuning Access query performance: JetShowPlan, Process Monitor, TraceSQLMode, and SQL Profiler.

I discussed previously the differences between passing by value and passing by reference. I now want to discuss how and when I use each in my own code.

The combination of “IntelliSense overload” and “global identifier case changes” convinced me I needed a different approach.

Microsoft Access is not the devil. You can use it to write great software. You can use it to write crappy software. I’m here to help you write more of the former and less of the latter.

A feature that was heavily used by a very small percentage of power users has been maintained over the course of five subsequent upgrades (and counting). Now that is showing reverence to backwards compatibility.

A personal reflection on my software development journey. There’s more to creating great software than knowing how to write code.

Thirteen years ago, I chose to maintain a folder of text files rather than a single Access database library. I’ve never once regretted that decision.

Wherein I compare keyboard warriors to actual warriors.

Lightweight forms make your code harder to read and debug, but if your users have monitors with high enough refresh rates, they’ll appreciate the effort.

How does one avoid the reliability paradox? One option is to intentionally write unreliable, buggy software. There’s a better option.

Burning cardboard is a lot like a large data migration. You can do it all at once or slowly over time.

Features don’t sell software.

Don’t build digital Maginot Lines. Program your defenses in depth.

The fastest and most reliable way to run a migration is to repeat it many times. I call this the “Repetition Paradox.”

Everything must go!!! Dead code, obsolete reports, and bunches of binary backups! No offer too big or too small!

Whatever modicum of credibility I may have had with “real programmers” I am about to douse in gasoline and light on fire with this article.

My approach to software development in four words: Less noise. More signal.

Bad actors carry out disinformation campaigns to poison our discourse. But when we write code, sometimes we’re the bad actors.

Many of the best Microsoft Access applications are written by people with no formal background in software development. Your origin story starts here.

The weirdos who enjoy spending lots of time to avoid doing mundane tasks that only take a little time have their own special name: programmers. Welcome to our very weird club.

A morass of Word documents and Excel workbooks--the invasive plant species of the Windows world--overgrowing your file system like so much digital kudzu.