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.
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.
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
'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...