Version Control

Access Version Control: My Philosophy
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.
High Wire Walking With No Net
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.
An Homage to Mercurial
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.
Top 10 Reasons to Use Version Control With Access
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.
The Starting Point
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.
From Gibberish to Clarity
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.
Database Properties for Thee
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.
Wherefore art thou, database properties?
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.
Tables to Text: Do it for the DVCS!
Anything that can lead to a bug in your software belongs in version control. That includes local tables with design-time data.
Referencing references
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.
Tracking Table Structure Changes
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.
Exporting Queries for Version Control
SaveAsText will export queries to text files, but calling those files “human-readable” is a stretch.
Putting It All Together
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.
Scraps of Wood and Code
Resisting the packrat mentality.

Error Handling

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.
Throwing Errors in VBA
Introducing a frictionless alternative to Err.Raise.
Graduate Level Error Handling
Once again, I borrowed an idea from Python. To smuggle this concept into VBA, I turned to a rarely-used language feature...

Testing

Python-inspired Doc Tests in VBA
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.
VBA Doc Tests: Advanced Features
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.

The Basics

It Takes Two
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.
One if ByVal, and Two if ByRef
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?

Intermediate

All about interfaces:

Interfaces, Conceptually
Interfaces can be a difficult concept to grasp conceptually. To help make sense of the concept, consider these real world examples.
Interfaces, Practically
A real world example using Active Directory lookups.
Backward-Compatible Refactoring
How can you completely change the way you interact with a class without breaking backward compatibility? Interfaces.
Cookie Cutter Forms
How do you reuse a form with external dependencies in multiple projects? Interfaces.
Dependency Injection (sort of)
For a few common dependencies, we can cheat by using a singleton class. Within that class, we’ll type those dependencies as interfaces.
Cleaner IntelliSense
How to clean up your object’s auto-complete dropdown list when it starts to look like that of the MacBook Wheel.

Hidden Features

Expressions vs. Code
When is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.
A Rounding We Will Go
Two kinds of rounding, the VBA language spec vs. the Office VBA implementation, and a drop-in replacement for VBA.Round().
Requery a Form in Place
Form.Recordset.Requery. That’s it. That’s the whole article.
VBA Alchemy: Turning Methods Into Properties
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.
Blast From the Past
Why use keyboard shortcuts from Access 2003 when developing in 2020? Because starting with Access 2007, the new shortcuts suck.
VBA’s Case Changing “Feature”
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.

Tools

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

Autohotkey

Debugging VBA with no Break Key
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.
A Mind-Reading Script
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.
Expand Your Access Palette
Use AutoHotkey to bring consistency to your Access color schemes and save time when designing forms.
Size Matters
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.

SQL Server

Peeking Under the Hood
Don’t let these gotchas trip you up when using SQL Server as a backend for Microsoft Access.
All Hail Ola!
If you’re managing SQL Server and you’re not using Ola Hallengren’s scripts, then you’re doing it wrong.
Cloning a SQL Server Database
The fastest, simplest, and cheapest way to clone a SQL Server database for debugging or testing. Even works with SQL Server Express!
Tuning Access Query Performance
The four best tools/techniques for tuning Access query performance: JetShowPlan, Process Monitor, TraceSQLMode, and SQL Profiler.

Conventions

VBA Squeeze Box
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.
Enum Type Naming Convention
The combination of “IntelliSense overload” and “global identifier case changes” convinced me I needed a different approach.

Commentary

Sympathy for the devil?
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.
Reverence for Backwards Compatibility
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.
Past is Prologue
A personal reflection on my software development journey. There’s more to creating great software than knowing how to write code.
Building Your Library
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.
Weekend Warriors
Wherein I compare keyboard warriors to actual warriors.
Lightweight Forms? Just Don’t
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.
The Curse of Reliable Software
How does one avoid the reliability paradox? One option is to intentionally write unreliable, buggy software. There’s a better option.
Two Ways to Burn
Burning cardboard is a lot like a large data migration. You can do it all at once or slowly over time.
How Not to Propose
Features don’t sell software.
Defensive Programming
Don’t build digital Maginot Lines. Program your defenses in depth.
The Golden Rule of Data Migrations
The fastest and most reliable way to run a migration is to repeat it many times. I call this the “Repetition Paradox.”
Cruft Sale!
Everything must go!!! Dead code, obsolete reports, and bunches of binary backups! No offer too big or too small!
Web Apps - Bane of my Existence
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.

Signal vs. Noise

Signal vs. Noise
My approach to software development in four words: Less noise. More signal.
When Good Comments Go Bad
Bad actors carry out disinformation campaigns to poison our discourse. But when we write code, sometimes we’re the bad actors.

Career Advice

Journey to Access: Part 1
Many of the best Microsoft Access applications are written by people with no formal background in software development. Your origin story starts here.
Journey to Access: Part 2
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.
Journey to Access: Part 3
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.

Image by Arek Socha from Pixabay