Topics-v1
- Version Control
- Error Handling
- Testing
- The Basics
- Intermediate
- Hidden Features
- Tools
- SQL Server
- Conventions
- Commentary
- Career Advice
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
Exporting Queries for Version Control
SaveAsText will export queries to text files, but calling those files “human-readable” is a stretch.
Mike Wolfe
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.
Mike Wolfe
Scraps of Wood and Code
Resisting the packrat mentality.
Mike Wolfe
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.
Mike Wolfe
Throwing Errors in VBA
Introducing a frictionless alternative to Err.Raise.
Mike Wolfe
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...
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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?
Mike Wolfe
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.
Mike Wolfe
Interfaces, Practically
A real world example using Active Directory lookups.
Mike Wolfe
Backward-Compatible Refactoring
How can you completely change the way you interact with a class without breaking backward compatibility? Interfaces.
Mike Wolfe
Cookie Cutter Forms
How do you reuse a form with external dependencies in multiple projects? Interfaces.
Mike Wolfe
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.
Mike Wolfe
Cleaner IntelliSense
How to clean up your object’s auto-complete dropdown list when it starts to look like that of the MacBook Wheel.
Mike Wolfe
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.
Mike Wolfe
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().
Mike Wolfe
Requery a Form in Place
Form.Recordset.Requery. That’s it. That’s the whole article.
Mike Wolfe
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.
Mike Wolfe
Blast From the Past
Why use keyboard shortcuts from Access 2003 when developing in 2020? Because starting with Access 2007, the new shortcuts suck.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
Expand Your Access Palette
Use AutoHotkey to bring consistency to your Access color schemes and save time when designing forms.
Mike Wolfe
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.
Mike Wolfe
SQL Server
Peeking Under the Hood
Don’t let these gotchas trip you up when using SQL Server as a backend for Microsoft Access.
Mike Wolfe
All Hail Ola!
If you’re managing SQL Server and you’re not using Ola Hallengren’s scripts, then you’re doing it wrong.
Mike Wolfe
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!
Mike Wolfe
Tuning Access Query Performance
The four best tools/techniques for tuning Access query performance: JetShowPlan, Process Monitor, TraceSQLMode, and SQL Profiler.
Mike Wolfe
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.
Mike Wolfe
Enum Type Naming Convention
The combination of “IntelliSense overload” and “global identifier case changes” convinced me I needed a different approach.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
Past is Prologue
A personal reflection on my software development journey. There’s more to creating great software than knowing how to write code.
Mike Wolfe
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.
Mike Wolfe
Weekend Warriors
Wherein I compare keyboard warriors to actual warriors.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
How Not to Propose
Features don’t sell software.
Mike Wolfe
Defensive Programming
Don’t build digital Maginot Lines. Program your defenses in depth.
Mike Wolfe
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.”
Mike Wolfe
Cruft Sale!
Everything must go!!! Dead code, obsolete reports, and bunches of binary backups! No offer too big or too small!
Mike Wolfe
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.
Mike Wolfe
Signal vs. Noise
Signal vs. Noise
My approach to software development in four words: Less noise. More signal.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
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.
Mike Wolfe
Image by Arek Socha from Pixabay
