none
Access Runtime Issue RRS feed

  • Question

  • Hi everybody, I'm facing a critical problem with Access Runtime.

    I developed a VBA application on Access 2010 some time ago, and it runs on machines without a Microsoft Office installation but only with Access Runtime. I made some changes with Access 2016 and I'm facing this big problem when I try to execute the application on Access Runtime. It gives a non specified Visual Basic syntax error at the start.

    I luckly discovered the cause of this issue by removing the following two libraries: "Microsoft Excel 16.0 Object Library" and "Microsoft Access 16.0 Object Library". But I need them to make my application working.

    Has anyone faced this issue before? Can someone please give me an help?

    Thanks in advance.

    Tuesday, February 14, 2017 12:01 PM

Answers

  • If you must support users running the 2010 Runtime, then you should do your development work on a machine running Office 2010. Also, if end users are running the 64-bit runtime, then your development work must be done using the 64-bit version. You cannot mix-and-match the two.

    What you're describing is a classic case of invalid references. In order for your application to run successfully, the target machine must have the same (or higher) versions of all references you include in your code. As noted previously, Access can usually upgrade references to higher versions, but it cannot downgrade them - so if your application requires Excel 2016, the end user must have AT LEAST Excel 2016 installed on the target machine. A target machine with Office 2010 would cause your application to fail during the initial load, since your application is expecting to find the 2016 version, and the machine would have the 2010 version.

    The code is already separated into a routine that uses the Excel library's objects.

    Moving code to a different module/class is not going to resolve this, since it's the reference that is causing your issue (and not the code). You can use Late Binding to avoid that reference issue, but your code still may fail if you don't take steps to ensure it works (and the target machine would still require Excel to be installed).

    On the machine with the runtime I have not installed any version of Excel. Can the issue be releated to this fact?

    If you're using Excel, the target machine must have Excel installed. There's no way around that, short of moving to a different platform (like .NET) and using a 3rd party library to emulate Excel. The only way to deploy Excel is for the end user to purchase the program and install it. There are no redistributale components for any of the Office products except Access (where you can deploy the Runtime).

    With Access development - or development on any platform, for that matter - you must be sure to work in an environment that is the "lowest common denominator" of the target environments you intend to support. If you have users running 2007, 2010, and 2013 then you MUST do your dev work on a machine with 2007 installed ONLY, and in most cases deploying to the machines with newer versions would work fine (you'd need to test thoroughly, of course). You must also ensure those target machines have all required references, and have all required Windows resources (like the correct .NET framework, if you're working in .NET). 



    -- Scott McDaniel, Microsoft Access MVP

    Monday, May 8, 2017 11:21 AM

All replies

  • Hi Maurizio -

    (A) What version(s) of the Access Runtime are experiencing the problem?

    (B) Will it startup when the Excel 16.0 reference is dropped but the Access 16.0 kept? My experience is that Access will successfully adjust it's reference, and any Office Object reference, but not backward version changes for Excel, Word. I have not been able to get around the condition but can mitigate by:
    1) Separate the Excel Code into it's own modules, classes, etc. Keep the Excel forms out of any forms, reports.

    2) Do not execute any of the Excel code as a part of opening the form / report. This doesn't initiate compilation. The moment it does, you get the error message.

    3) You might be able to find code to check the version of excel on the machine using the registry (not excel). Chances are high that if the alue of Access is not 16, there will be a problem that you can then let the user know of; you can also set conditions to executing Excel code.

    Final Suggestion> The only way I've ever managed to stop this completely is to go back to a machine with Access / Excel 2010 on it, open the apps there, manually fix the references, save it and that becomes my distribution copy.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Tuesday, February 14, 2017 1:12 PM
  • sounds like your development has advanced to the 2016 edition but your runtime is back in 2010

    as a sanity test I would install the 2016 runtime into a pc and see how that runs with your new design

    Tuesday, February 14, 2017 2:32 PM
  • Hi, sorry for the late response, I shelved this issue for a while.

    The Access Runtime version is the 64bits Access 2016 Runtime. The application starts correctly if I remove only Excel 16.0 library, so the issue it's not releated to Access 16.0 library.

    1) The code is already separated into a routine that uses the Excel library's objects.

    2) The error does not happens when I execute the code that has the Excel library's objects but when I open the application! (even if I use that library only by clicking on specific buttons of the main form)

    3) On the machine with the runtime I have not installed any version of Excel. Can the issue be releated to this fact?

    Monday, May 8, 2017 8:23 AM
  • If you must support users running the 2010 Runtime, then you should do your development work on a machine running Office 2010. Also, if end users are running the 64-bit runtime, then your development work must be done using the 64-bit version. You cannot mix-and-match the two.

    What you're describing is a classic case of invalid references. In order for your application to run successfully, the target machine must have the same (or higher) versions of all references you include in your code. As noted previously, Access can usually upgrade references to higher versions, but it cannot downgrade them - so if your application requires Excel 2016, the end user must have AT LEAST Excel 2016 installed on the target machine. A target machine with Office 2010 would cause your application to fail during the initial load, since your application is expecting to find the 2016 version, and the machine would have the 2010 version.

    The code is already separated into a routine that uses the Excel library's objects.

    Moving code to a different module/class is not going to resolve this, since it's the reference that is causing your issue (and not the code). You can use Late Binding to avoid that reference issue, but your code still may fail if you don't take steps to ensure it works (and the target machine would still require Excel to be installed).

    On the machine with the runtime I have not installed any version of Excel. Can the issue be releated to this fact?

    If you're using Excel, the target machine must have Excel installed. There's no way around that, short of moving to a different platform (like .NET) and using a 3rd party library to emulate Excel. The only way to deploy Excel is for the end user to purchase the program and install it. There are no redistributale components for any of the Office products except Access (where you can deploy the Runtime).

    With Access development - or development on any platform, for that matter - you must be sure to work in an environment that is the "lowest common denominator" of the target environments you intend to support. If you have users running 2007, 2010, and 2013 then you MUST do your dev work on a machine with 2007 installed ONLY, and in most cases deploying to the machines with newer versions would work fine (you'd need to test thoroughly, of course). You must also ensure those target machines have all required references, and have all required Windows resources (like the correct .NET framework, if you're working in .NET). 



    -- Scott McDaniel, Microsoft Access MVP

    Monday, May 8, 2017 11:21 AM
  • You always have to do development using the oldest version of Access that will be used to run your database. So, since you still have users running 2010, you need to do all your development in 2010. If you switch to 2016, then all your users need to upgrade.  Access is backwards compatible, but no software can be forwards compatible, ie 2010 has no clue about 2016 format...

    Then add to that bitness issues 32 bit vs 64 bit and you get a real mess to deal with.  You can learn more about compatibility issue by looking over https://www.devhut.net/2017/04/13/access-x32-vs-x64-compatibility/

    As always, you should try to use Late Binding as much as possible.  You can learn more by checking out https://www.devhut.net/2016/11/08/vba-early-binding-and-late-binding/


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Monday, May 8, 2017 3:28 PM
  • yes, users without Excel will case code to break in your application - even code that has nothing to do with the Excel.

    If you have references set and those objects are not available on the target machine then you do have a broken reference. And yes such a setup will break “general” code and functions in your application EVEN if you not running or trying to run the actual code that works with Excel.

    So the above tends to be a challenge + issue. If a reference is missing, then it tends (will most often) break general code. Again, EVEN code that has nothing to do with the external word or Excel application.

    I also suggest that unless you have VERY robust error handling in your code that you ALWAYS distribute a compiled accDE as compared to an accDB. The reason for this is that a compiled accDE can continue to run correctly EVEN if the application encounters an error. If you use an accDB and an error occurs the runtime will shut down (full version does not). So while a compiled accDE is VERY resilient to errors (all local and even global VBA variables NEVER get re-set – and this includes when errors occur). And as noted the application will keep running when you distribute an accDE.


     With an accDB, ANY SINGLE un-handled errors not only re-set and blow out all VBA variables (and thus cause issues with your application), you also find that the runtime will after displaying the error THEN shutdown. So runtime accDB will shut down with one little error, but an accDE NEVER shutdown to un-handled errors – the result is a FAR more reliable application and one that works well even if you have limited error handling code.

    Now without question one might have word, or outlook or Excel bits and parts in your application that makes use of these external applications. However, if such applications like excel or word are being used by your application and you set a reference? If these target applications are not installed, then you have a broken reference.

    The solution is quite simple in the above. You have to convert your code from what is called “early” binding (you have reference to those external applications) to “late” binding in which you ALWAYS remove refinances to those external programs.

    In most cases the change from early to late binding is rather minor and simple. If your goal is to have your application run without “always” having those target applications then you MUST use late binding else your VBA code will experience errors and break – this EVEN includes code in your application that does not even use the external program(s) and has ZERO to do with the external application. (read this paragraph carefully again if you must to grasp this issue).

    Of course the other reason why your references can break is that the path name and location of 2016 programs is different then 2010. While in the past Access could “guess” and would automatic change the references for you, the other issue is office 2016 is now OFTEN what is called CTR (click to run). So CTR version of Access have a slightly different location and install path then the full “MSI” version.


    Converting your code to late binding – how?

    I recommended when you are actually working on the code to use early binding, and then right before you compile to a accDE to prepare for distribution you change that code to late binding and test it a few times to ensure it works.

    Typical early binding code of word (but same for Excel) will look like:

        Dim myWord     As New Word.Application ' early bind

    Code follows to use MyWord object such as:

       myWord.Documents.Open ("c:\MyWordDocs\test.docx")


    To change above to late binding, you use this code:

       'Dim myWord     As New Word.Application ' early bind
       Dim myWord     As Object               ' late bind
       Set myWord = CreateObject("Word.Application")
      
       myWord.Documents.Open ("c:\MyWordDocs\test.docx")

    You then REMOVE the reference to word in the VBA->tools->references.

    Once you removed the reference to word, you will find that your word (or excel or whatever) code will continue to run and MOST important is that your VBA compiles.

    At this point you then:

    Test if your code compiles (VBA editor->debug->compile

    After you both tested your code, ensue it compiles, you THEN compile the application to the accDE and this is then used on the machines with runtime.

    Note that you don’t necessary have to use a compiled accDE, but as noted the advantages as per above are significant.

    When you have to make “extensive” modification to the actual code that uses word, or say excel from your application, then your comment out the above “dim” statements and un-comment out the original dim statement with early binding, set a reference to word, and then now develop + make changes to your word code. When done developing you flip the code back to late binding (it only takes a few seconds of your time to do this). Note that for “general” code changes or very “minor” changes to your VBA code that uses the external application you likely don’t even have to change back to early binding. However the “main” advantage of using early binding is that:

    You get inteli-sense in the VBA editor (that is the drop down and suggestions of the word/excel properties and methods as you type).

    You get compile errors when you choose/use a method not suggested by the VBA editor.

    When you flip the application to late binding then you lose BOTH of the above two main features of early binding.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Monday, May 8, 2017 4:10 PM
  • Thanks everybody for the answers.

    After days of troubles I found that my customers actually had a version of Office installed but was the one without Access. I was testing on a virtual machine WITHOUT Office installed. As @Scott Daniel MVP said, I need to have at least Excel installed on the machine in order to run the application.

    Thanks also tu @Albert D. Kallal for the exhaustive explanation on early and late binding, it will be very useful for me!

    Tuesday, May 9, 2017 7:38 AM