From Gibberish to Clarity

In an earlier post, I discussed the starting point for version control with Microsoft Access. The tl;dr version is that you need to export text files of the objects in your database and store those in version control, not the database file itself.  That other article linked to this StackOverflow answer as a starting point for a script to perform such a conversion.

Database Properties

There are several properties that you can change in a database file itself.  This includes the properties in the following screenshot.  To get to this dialog, you go to File -> Options -> Current Database:

Comparing binary files

Using the script from the link above, this information would be saved in the stub.accdb file, but we would not be able to compare it between two different revisions.  At least, not in a meaningful way.  Here's what happened when I forced the comparison using kdiff3 between two different versions of the stub file associated with one of my programs:

What did I actually change in the stub file between those two versions?  I have no idea.  And that's my point.  It's certainly possible that I changed one of the settings in the above Options dialog box.  But there's no way to tell that from the kdiff3 comparison.

Comparing text files

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:

'db        : an open database object
'ExportPath: folder where the DbProperties.txt file will be created
Public Sub ExportProps(db, ExportPath)
Dim i, Props, objFSO, Prop
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set Props = objFSO.CreateTextFile(ExportPath & "\DbProperties.txt", True)
    Props.WriteLine Join(Array("Index", "Name", "Value"), vbTab)
    On Error Resume Next
    For i = 0 To db.Properties.Count - 1
        Set Prop = db.Properties(i)
        Props.WriteLine Join(Array(i, Prop.Name, Prop.Value), vbTab)
    Next
    On Error Goto 0
    Props.Close
    Set Props=Nothing
End Sub

The code above creates a tab-separated header line.  It then iterates through the Properties collection, attempting to export each property's index value, name, and value to a tab-separated line.  Here's a sample output from one of my applications.  This is what it looks like in Visual Studio Code, using the excellent Rainbow CSV extension:

Now, when we change one of the Database Properties--such as the program title--the change is easy to see in the TortoiseHg diff window:

A lot of these properties won't change very often.  And some of them change all the time.  I mean, does anyone really care if the Nav Pane was open the last time they exported their db to source but now it's closed?  If those things annoy you, you can easily exclude them by adding a Select...Case statement to your properties loop, like so:

Select Case Prop.Name
Case "NavPane Closed", "NavPane Width" 'etc.
    'these are inconsequential properties; we'll just ignore them
Case Else
    Props.WriteLine Join(Array(i, Prop.Name, Prop.Value), vbTab)
End Select

And there you have it.  From gibberish to clarity in just a few lines of code.  

It really starts to get cool when you realize that you can add your own custom properties to a database file.  In fact, astute readers may have already been wondering what that hg_csv property was all about in the VS Code screenshot earlier.  Well, that's a story for another day...

Image by ju Irun from Pixabay