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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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 36

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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