Dynamically changing a library to match a version of Access

I am preparing to deploy a tool to users who have access versions ranging from 2007 to 2013.  My primary development is in 2010.  I've seen some code snippets that check for libraries and change the library, but none have worked for me.  Does anyone have a snippet that will inventory the libraries used for the version in 2010 and change them for 2007 and 2013?
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Agree 100% with imnorie, Late Binding is the fix for things like this.

You could also change to using the "lowest common denominator" method, which means you do your development work with a reference to the minimum version you'll be supporting. So if you require users to have AT LEAST Word 2007, you make your reference to Word 2007 and continue with your work. When you deploy to end users, Access is intelligent enough to "upsize" references to newer versions. Note it does not downsize, so you must be sure you're using the earliest supported version of the library.

First and foremost: In order to make the reference, the program (like Word) will have to be installed on the machine. You cannot know if it is, so attempts like this are mostly worthless. Also, even if the program is installed, you don't know where it's installed, so you cannot be sure your code will work. You cannot "distribute" Word, so you can't include it in your installation media.

Second: In order to remake references, you have to run VBA code. If you have missing or invalid references, you cannot be assured your VBA code will run - in most cases, you'll get all sorts of odd errors and such, and your code will simply fail.

Third: Even if you CAN get the code to run, and you CAN locate the correct reference for the library, you cannot effectively do this AFTER Access has loaded. In order to be sure your reference is properly verified and loaded, you'd have to run your code, then close and reopen the database (and hope the reference "sticks").

And finally:

One of the first caveats of professional programming is do NOT alter the user's environment to match your own. Instead, alter your development methods to insure that your program will behave and operate correctly in those environments. This would include making sure that the necessary elements for your program to operate are installed on the end user machine, or to use coding (like Late Binding) that will work with multiple versions. In most cases you'd use an installer, and that installer would verify if the correct versions and such of needed programs are installed. If not, you'd alert the user, and abort the installation.

Or you'd change your program to use those items which are installed on the machine, and redeploy.

Either way, the caveat remains: You don't alter the end users environment to suit your needs, you alter your program to fit the confines of the end user environments. This often means you'd have some sort of minimum requirements - i.e. "User must have Office 2007 or greater installed", or something of that nature - and you'd check for that before installing your program.
jwandmrsquaredAuthor Commented:
Here is the latest attempt to just fix the WORD reference, but have others that need to be fixed as well.

Public Sub CheckReference()

    Dim chkRef As Reference ' A reference.

    Dim foundWord As Boolean

    foundWord = False

    ' Check through the selected references in the References dialog box.
    For Each chkRef In References

        ' If the reference is broken, send the name to the Immediate Window.
        If chkRef.IsBroken Then
           Debug.Print chkRef.Name
        End If

        If InStr(UCase(chkRef.FullPath), UCase("MSWORD9.olb")) <> 0 Then
            foundWord = True
        End If

        If InStr(UCase(chkRef.FullPath), UCase("MSWORD.olb")) <> 0 Then
            References.Remove chkRef
        End If


    If (foundWord = False) Then
        References.AddFromFile ("c:\Program Files\VBRM\MSWORD.OLB")
    End If
jwandmrsquaredAuthor Commented:
I should also mention that I called the above module from an OnLoad event property when my Main Menu loads.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

NorieConnect With a Mentor VBA ExpertCommented:
Instead of changing the reference libraries you could change the code to use late binding to the reference isn't required.
Excellent advice Scott.  Way too many professional developers forget that simple maxim.

I almost drove myself crazy (OK, I know it's only a short putt) trying to get an installation that would work in multiple environments for a product that was sold to the public.  In the end, I required that they have a minimum of Office 2007 installed (we recently changed our minimum to O2010) since the app interfaced with Outlook, Word, and Excel.  I do my final testing on an O2007 PC (I'm developing with A2013 currently) and have SageKey build the installation package in that version of Access.

I also toyed with distributing the Access runtime and still do but give the users an option if they have a full version of Access installed so I include two installation options.  The product isn't "shrink-wrapped" so there are a limited number of installations and we do have some influence over the environment.  Our policy is that you use the runtime at your own risk and the customer MUST have at least one installed full copy of Access in case we need to log in and do any work on their system remotely.
Nick67Connect With a Mentor Commented:

Isn't that code a bite-in-the-ass?
It sure looks like you could see your references and enumerate them and fix them.
It REALLY looks possible.

You can code it, you can compile it, it all LOOKS so sweet.
And then it DOESN'T, CANNOT, and will NEVER work.
Very sad.

What's the point of .IsBroken, then
Your guess is as good as mine.

Late Binding works, but is a pain because you losing Intellisense support.
...alter your development methods to insure that your program will behave and operate correctly in those environments.

The BEST way to do that is to use the earliest version of the program to develop in.

Our environment includes A2003, A2010 and A2013.  All development is done on A2003 (it has the best help anyway! and I find the most productive environment for working on pre-existing objects created in A2003 and earlier.)  All references silent UPGRADE when the front end is accessed by clients.  No coding required.

***NOTE*** objects created by A2007+ wizards rapidly become corrupted if altered by A2003.
jwandmrsquaredAuthor Commented:
First, Nick, thanks for confirming that I am not, indeed, crazy or incompetent.  I am not a professional developer, but more of a hack with skills :)

I think I will take the advice of having the final version be published from the lowest common denominator  of 2007 to avoid many of the issues.  The audience for what I've done will be widespread, but small enough to be controllable, and is NOT the general public (I personally do not like dealing with the general public) but b2b users.

Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.