The Starting Point

I'm a big believer in version control for Microsoft Access.  But how do you actually do it?  

Text files: GOOD

In a typical programming language--like C#, Java, or Python--you write your source code in individual text files.  This is ideal for Git and other distributed version control systems (DVCS).  The way a DVCS works is that it stores a series of differences between revisions.  It does not store a full copy of every file with each commit.

This works great for text files.  If you have a 500-line code module and you change one line, the DVCS stores only that information it needs to apply the change to that line.  

Binary files: BAD

Binary files are a different story.  By default, a DVCS treats binary files just like text files.  But, unlike text files, a small change to a binary file could require the DVCS to store almost the entire file.  The classic example is an image.  Let's say I make a single change to the image, such as replacing the background color.  That's a single action, but it would create an entirely different combination of bits in the saved file.  This bloats the repository.  Too many changes like this and the repository will be huge.

Bloating is a problem, but in and of itself it's not a huge deal.  After all, disk space is cheap these days.  The far bigger problem is when you try to compare two versions of a binary file.  You would have to restore each file, open them in an image editor, and compare them side-by-side.  Even then, you could look at two images and never find the difference.

Scalable vector graphics (SVG) is a text-based vector image format.  In the above example, if we had changed the background color of an SVG file, we could compare the differences very easily.  It would look something like this:

 <layer>
-    <color:red />
+    <color:blue />
 </layer>

^{I'm sure this is not actual valid SVG markup, but you get the idea.}

How does this apply to Access?

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

The post includes two vbscript files.  The first, decompose.vbs, works like this:

1. Copy the Access file and append _stub to the new filename
2. Use Application.SaveAsText to save all the reports, forms, modules, and macros
3. Delete all the reports, forms, modules, and macros from the _stub file

The idea is to convert as much of the Access project to text files as possible and then leave everything else in a stripped down version of the binary Access file.  By doing this, you can then use the compose.vbs script to run everything in reverse and restore the original Access file, even from past revisions.

Where do we go from here?

As I said, the above scripts were the mere starting point of my Access version control journey.  More than ten years later, I've expanded on the original concept in a number of ways.  Stay tuned for how you, too, can take that idea to the next level. Here's how to take that idea to the next level:

• Export database properties to text files
• Export local "design-time" table data to CSVs
• Export external references to text files
• Export linked table locations and table structures to text files
• Exporting queries for version control

Image by Paul Brennan from Pixabay

UPDATE [2021-07-21]: Changed the SaveAsText code sample to properly show that it is a method of the Access Application object and not the DoCmd object. (h/t Huy Truong)