Tracking Schema Changes

I have four guiding principles when it comes to deciding what to include or not include in version control for my Access applications.  Here is number two on that list:

2. Anything that can lead to a bug in our software belongs in version control.

One of the most overlooked areas in version control is the backend database structure.  Changes to that backend structure can absolutely lead to bugs in our software.  That's why I insist on tracking database schema changes in my version control repositories.

There are two ways to do this. They are not mutually exclusive.  I use both.

Tracking the change scripts

If you have multiple data environments, you should have a reliable way to apply consistent changes to those data environments.  If you are writing Access applications professionally, every project should have at least two different data environments: production and development.  If multiple clients or customers use the same software, the number of data environments you need to support can grow quite large.

The best way to apply schema changes to multiple data environments is to script those changes.  That could mean literal .sql script files, custom VBA functions, or some other easily repeatible process.  The key is the process should be entirely text-based with as little GUI interaction as possible.  

If you need to rely on a series of mouse clicks to check and uncheck boxes, toggle field properties, etc., then you are setting yourself up for failure.  Forgetting to mark a new field as required in one data environment but not another can be the source of lots of hairy future bugs.  What's worse, if you don't have easy access to the inconsistent data environment,  you might not even consider that a field that is required in every other environment could be the source of the error in this one specific environment where it happens to be optional.

Text-based schema change scripts provide two important benefits:

• Guaranteed consistency across data environments
• Easy change management tracking in version control systems

Since you will be creating these schema change scripts as you are developing new features, these scripts are likely to live nearby their associated application changes within the version control system.  In fact, for small changes it might make sense to include the schema change script in the same revision as the associated code changes.

Tracking the current structure

Part of my expanded decompose.vbs script is a section of VBScript that exports the data structure and location of every linked table.

The positive with this approach is that it's guaranteed to reflect reality.  The drawback is that there may be multiple realities.  

The "reality" that this approach reflects is the current data environment.  A given application can have multiple distinct data environments, and hence, multiple "realities":

• Development
• Testing
• Production (Client A)
• Production (Client B)
• Production (Client C)

For consistency, it's probably simplest to track the "Development" data environment.  The problem with tracking just this one environment, though, is you can no longer guarantee that the state of your version control matches any specific production environment.  Thus, if a bug report comes in from Client B, you can't rely on your version control system's assumption about that data environment's current state.

There's no simple solution for this disconnect.  I suppose you could track the data structure changes in every environment within your version control system.  This brute force approach might make sense if it's practical.  By practical, I mean that the following two conditions are met: 1) you have easy access (both now and during future development) to the environments you intend to include in your repository; and 2) extracting the data structure is a quick enough process that it doesn't become an excuse for putting off version control commits.

Image by 2211438 from Pixabay