Referencing references

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

With the above in mind, I want to tackle another piece of the Microsoft Access version control puzzle. The biggest challenge with incorporating version control into Access development is that Access applications consist of a single binary file, while version control works best with many separate text files.  

The best way to reconcile this disconnect is by exporting as much important information as possible from our binary front-end file into a series of text files. In this context, I define "important" as "anything that can lead to a bug."

...missing or broken reference...

A common source of bugs in Access applications are missing external references.  These are code libraries that exist on our development machines, but may not necessarily exist on our users' machines.  To view these references from VBA, we click on the "Tools" menu then choose "References..."

Usually what happens is that we add one of these references to our programs during development.  Once we do that, our program depends on that reference being available on whatever machine it runs on.

This is one of the big downsides of so-called early binding.  A user without the reference installed on their computer will not be able to run the Access program.  This is true even if they won't be using the feature that the reference provides.  If we want to provide such a feature for some users but not others, then we need to use late binding via the CreateObject() and GetObject() functions instead.  But that is a topic for another day.

Priority matters

Another common source of errors has to do with the priority ordering of references, especially if the references include overlapping object types.  In Microsoft Access, the most common examples are the DAO (Data Access Objects) and ADO (ActiveX Data Objects) libraries.  There is a lot of overlap between the two libraries.  For example, both define Recordset objects, but the two libraries implement recordsets in differing ways.

The best way to deal with this ambiguity is to be explicit when declaring variables.  For example, instead of: Dim rs As Recordset, you would use Dim rs As DAO.Recordset.  This is not required, though, even if there are multiple references that provide the same type of object.  VBA resolves ambiguities like that by looking up references in priority order.  As you can see from the screenshot above, the References dialog window provides up and down arrows so that you can set the reference priority explicitly.

Exporting references to a text file

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.

The VBE object has an .ActiveVBProject property.  That property in turn includes a collection of References.  The References collection is 1-based, meaning the first item in the collection has an index of 1.  For example, to show the path to the "Visual Basic For Applications" reference shown in the above screenshot, we can retrieve the .FullPath property:

Debug.Print VBE.ActiveVBProject.References(1).FullPath
C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL

Additionally, the References collection returns references in priority order.  This means that if we change the priority of our references, we can reflect that change when we export the reference list to a text file.

Sample Code

Here is the procedure I use in my version of the decompose.vbs script to export my references to text file:

'ExportPath: folder where the References.txt file should be created
'oApp: an Access.Application object with an open database (via .OpenCurrentDatabase)
Public Sub ExportRefs(ExportPath, oApp)
Dim ref, Refs, objFSO, i
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set Refs = objFSO.CreateTextFile(ExportPath & "\References.txt", True)
	'Refs.WriteLine oApp.VBE.ActiveVBProject.References.Count & " references found"
    Refs.WriteLine Join(Array("Name", "Major", "Minor", "Type", "Builtin", "Broken", "GUID", "Description"), vbTab)
	On Error Resume Next
    For Each ref In oApp.VBE.ActiveVBProject.References
		i = i + 1
		'Refs.WriteLine "Reference " & i
        Refs.WriteLine Join(Array(ref.Name, ref.Major, ref.Minor, ref.Type, ref.Builtin, ref.IsBroken, ref.GUID, ref.Description), vbTab)
		If err.number <> 0 then
			Refs.WriteLine err.Description
			err.Clear
		End If
	Next
    Refs.Close
    Set Refs=Nothing
End Sub

Image by ElasticComputeFarm from Pixabay