Dynamically changing a library to match a version of Access

Posted on 2014-12-07
Medium Priority
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 35

Assisted Solution

Norie earned 668 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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 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 40

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 664 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 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