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!

Access 2007 .Transfer bug, the fun…….

I just spent 20 hours of my life, debugging one hell of a problem in Access 2007. I thought I would share this with you, incase anyone else maybe tearing their hair out - and the problem is similar.

Here is the setup. Access 2007 Database - Upsized/Migrated to SQL Server (2008 in my case). Everything worked fine before, exporting a Text File, or a Spreadsheet of the data. Now when you click on the button - It crashes the application. The stranger part - it doesn’t happen EVERY TIME!?

No in fact this problem, doesn’t even happen if you use the OutPutTo function instead…….

So after, all the tinkering eliminating the much more obvious offenders, what was the little damn stinker?! A UnionQuery…. A pesky UnionQuery was bombing out the .Transfer. It worked fine when I ran it, Access didn’t complain about it, nor did SQL server.

What I found was a pesky little ORDER BY on one of the UNION members. This trashed the .Transfer for some reason. SO there you have it. Remove your ORDER BY in one of your UNION statements, and life should be grand again!