Solved

Dynamically changing a library to match a version of Access

Posted on 2014-12-07
7
146 Views
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?
0
Comment
Question by:jwandmrsquared
[X]
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
7 Comments
 

Author Comment

by:jwandmrsquared
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


    Next

    If (foundWord = False) Then
        References.AddFromFile ("c:\Program Files\VBRM\MSWORD.OLB")
    End If
0
 

Author Comment

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

Assisted Solution

by:Norie
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.
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 85

Accepted Solution

by:
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.
0
 
LVL 38

Expert Comment

by:PatHartman
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.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 166 total points
ID: 40487160
@jwandmrsquared

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.
0
 

Author Closing Comment

by:jwandmrsquared
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!
0

Featured Post

Industry Leaders: 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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

719 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