derek:b3studios

Dynamic References in an Access Application.

So you have an Access database application. Along with this application, your company uses multiple versions of Office, and you use Excel, Word, Outlook - or any other Reference - where versioning might be a problem.

So how do you get your app to work flawlessly on Bob’s computer with Office 2000 and Mary’s computer with Office 2007? A little trick called VBE. Visual Basic Extensions! We are going to loop through our projects references, when we encounter either a reference we know is Word, Excel or Outlook - we’re going to remove it, and re-add the latest version. We don’t need to worry if the reference is broken or not, since removing and re-adding costs very little.

We identify references by using a GUID. VBA has a GUID for each reference. Even if you are using Excel 2003 or Excel 2007 - the GUID remains the same. What changes is the Major and Minor versions when loading them via VBA code. However, by specifying the versions as 0, 0 we get to force VBA to load the LATEST version. Thus making our code dynamically load whichever version is installed on the target machine.

For now, I’m only going to worry about three GUIDs. Word, Outlook and Excel. I’ll write later on how to retrieve the GUID ids, but if your clever the following code is enough for you to work from. With our new found knowledge of what we’re doing, how do we do it? Here are two functions that will help you put the mystery together.

To remove references we may have to these three COM objects.
Function RemoveOfficeReferences()
Dim chkRef As Reference
For Each chkRef In Application.References
Select Case chkRef.Guid
Case "{00062FFF-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
Case "{00020813-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
Case "{00020905-0000-0000-C000-000000000046}"
Application.References.Remove chkRef
End Select
Next
End Function


To add references we need for these three COM objects.

Function AddOfficeReferences()
On Error GoTo errhandler
'this bit of code will add the references the first part is the
'Function is .AddFromGuid(Guid, Major, Minor)
'Leave the Major and Minor @0 to retrieve latest version.

End Function

These are just samples, there are much more eloquent ways to do the above, but this gives you an idea! Now go out, and develop without fear of version issues!