Dynamically changing a library to match a version of Access

Posted on 2014-12-07
Last Modified: 2014-12-08
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?
Question by:jwandmrsquared

Author Comment

ID: 40486088
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

Author Comment

ID: 40486090
I should also mention that I called the above module from an OnLoad event property when my Main Menu loads.
LVL 33

Assisted Solution

Norie earned 167 total points
ID: 40486093
Instead of changing the reference libraries you could change the code to use late binding to the reference isn't required.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 40486491
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.
LVL 35

Expert Comment

ID: 40487157
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.
LVL 26

Assisted Solution

Nick67 earned 166 total points
ID: 40487160

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.

Author Closing Comment

ID: 40487312
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!

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to use VBA to update a record in a table in MS Access? 12 36
Access sql to sql server express 10 35
SQL Group on First occurrence 9 25
Dcount help 2 16
In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question