none
MISSING VBA REFERENCES FOR OFFICE15 RRS feed

  • Question

  • Hi everyone, I have been working on this problem since Monday of this week when some of our computers were updated from Office14 to Office15.  Here is what Access References are missing. 

    I know this is due to the computers with Office15 are now missing the Office14 references.  I have been working to create VBA code that will allow both Office14 and Office15 users to access the database that tracks over 1000 students.  I have visited many different Internet sites and tried the suggested code but they are all dying of the same problem.  Since Excel for Office15 is not installed on the computer trying to open the database, the user gets a run-time error of 48, error in loading DLL.  I have tried to trap the error and then remove the check from the reference to the missing DLL and then what to re-add it. 

    Here is some of the code I have tried;

    
    Sub Report_Broken_References()
    
    Dim strRefsName As String
    Dim strRefsDescription As String
    Dim intRefBroken As Integer
    
    Dim intIndex As Integer
    
    For intIndex = 1 To Application.VBE.ActiveVBProject.References.COUNT
    
        ' Get the Current Name of the Reference based on the index
        strRefsName = Application.VBE.ActiveVBProject.References(intIndex).Name
        
        ' Get the Description of Current Reference
        strRefsDescription = Application.VBE.ActiveVBProject.References(intIndex).Description
        
        'Returns True or False based on if missing or not.
        If Application.VBE.ActiveVBProject.References(intIndex).IsBroken = True Then
            intRefBroken = True
            MsgBox "Reference Broken Name = " & strRefsName & " Description = " & strRefsDescription, vbOKOnly + vbMsgBoxSetForeground
        End If
    
    Next intIndex
    
    End Sub
    
    This line is throwing the error; strRefName = Application.VBE.ActiveVBProject.Reference(intIndex).Name
    

    Public Function RemoveRefs() Dim RefPath As String, X As Lon

    g Const RefName = "Excel" With Application.References For X = 1 To .COUNT If .Item(X).Name Like RefName Then .Remove .Item(X) End If Next '.AddFromFile (YourNewRefPath) End With End Function

    Again the line that throws the error is; If .Item(X).Name Like RefName Then

    If anyone can point me in the right direction, I want to thank you now for your time and effort.  I have read about late binding the reference.  If that is the only solution to having different versions of Access open the same 2010 database then so be it.  The database is split front-end and back-end.  Because this database is used to track over 1000 students location and grades within the schoolhouse, and having over 100 users possible but only 10 computers out of 100 upgraded.  Those 10 users are not allowed to open the database because they immediately corrupt the database for the 90 other users. 

    Again, thank you for your time for responding to my question.

    Friday, September 30, 2016 8:30 PM

Answers

  • Hi,

    Newer versions of Access should be able to run databases created in older versions. So, if you limit your design changes or development using Access 2010, then Access 2013 users should not have any problems opening and running your database application.

    Late-binding is another solution to eliminate the dependency on specific Office versions, but it's only useful if you're automating other Office products (or any other application program which allows for late binding).

    So, you either adapt late-binding or only use Access 2010 when making any design changes during development.

    Hope it helps...

    • Marked as answer by coxml32 Sunday, October 2, 2016 10:45 PM
    Friday, September 30, 2016 8:35 PM
  • The issue here is that it would seem someone ran the on the newer version of Access and then someone else with the older version then tried to use the same copy.  What happens is the newer version automatically "upgrades" the references and then it no longer works for the older version anymore.

    The entire issue is then one of users sharing the db and what version you are using for development.

    You as the developer should always be using the lowest common denominator between all the versions that your users use.  So in your case, it would appear you should be doing all your development using Access 2010.

    Secondly, each user needs to have their own copy of the db front-end and never share a common copy.

    Lastly, regarding late-binding, for Excel, Word, Outlook I urge you to look into it.  It doesn't require much in the way of changes to your code and eliminates this entire issue (for those references at least!).  Here's a little primer for Excel:

    VBA – Automating Excel
    VBA – Automating Excel – Part 2

    Once you implement Late-Binding, you can remove the related reference library(ies).  As always, ensure your db compiles without errors.


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


    • Edited by Daniel Pineault (MVP)MVP Saturday, October 1, 2016 1:19 PM
    • Proposed as answer by Paul23 Sunday, October 2, 2016 11:33 AM
    • Marked as answer by coxml32 Sunday, October 2, 2016 10:43 PM
    Saturday, October 1, 2016 1:18 PM
  • As crazy as it may sound, RefName is not available if the reference is broken. Makes the Reference object a lot less helpful. You can show users the Guid property, but it is not as helpful.

    If you REALLY want to show the name, you must have a table with Guids and Names. You can refresh this table just before you release a new version (at that time there is no broken ref and the RefName *is* available).

    I find it hard to believe that your app needs all those references. Are you sure? Uncheck some of them and recompile.

    Other than that, the developer ALWAYS should develop in the lowest version of Access that the users may have. Few softwares are forward compatible, but many are backward compatible.


    -Tom. Microsoft Access MVP

    • Marked as answer by coxml32 Sunday, October 2, 2016 10:43 PM
    Saturday, October 1, 2016 2:20 PM
  • To Daniel, Tom and DBguy,

    I have finished the late binding updates to my database.  I did one reference at a time and have my reference list down to 4.  There were a few minor fixes; i.e. PtrSafe, that needed to be added but everything seems to compile fine. 

    Next step is to put it on line and see if both Office 2013 and Office 2010 users have any issues.  Thank you again for the help.

    During this process, I ran across a statement that DAO is no longer used since 1998.  I should be using ADO.  Boy, this looks confusing and I will have to try to build a sample database from scratch before trying to upgrade my existing database. 

    Any good sites where I can find out more info about ADO programming other than MS. 

    Thank you again.

    Martin

    • Marked as answer by coxml32 Tuesday, October 4, 2016 6:28 PM
    Tuesday, October 4, 2016 6:28 PM

All replies

  • Hi,

    Newer versions of Access should be able to run databases created in older versions. So, if you limit your design changes or development using Access 2010, then Access 2013 users should not have any problems opening and running your database application.

    Late-binding is another solution to eliminate the dependency on specific Office versions, but it's only useful if you're automating other Office products (or any other application program which allows for late binding).

    So, you either adapt late-binding or only use Access 2010 when making any design changes during development.

    Hope it helps...

    • Marked as answer by coxml32 Sunday, October 2, 2016 10:45 PM
    Friday, September 30, 2016 8:35 PM
  • The issue here is that it would seem someone ran the on the newer version of Access and then someone else with the older version then tried to use the same copy.  What happens is the newer version automatically "upgrades" the references and then it no longer works for the older version anymore.

    The entire issue is then one of users sharing the db and what version you are using for development.

    You as the developer should always be using the lowest common denominator between all the versions that your users use.  So in your case, it would appear you should be doing all your development using Access 2010.

    Secondly, each user needs to have their own copy of the db front-end and never share a common copy.

    Lastly, regarding late-binding, for Excel, Word, Outlook I urge you to look into it.  It doesn't require much in the way of changes to your code and eliminates this entire issue (for those references at least!).  Here's a little primer for Excel:

    VBA – Automating Excel
    VBA – Automating Excel – Part 2

    Once you implement Late-Binding, you can remove the related reference library(ies).  As always, ensure your db compiles without errors.


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


    • Edited by Daniel Pineault (MVP)MVP Saturday, October 1, 2016 1:19 PM
    • Proposed as answer by Paul23 Sunday, October 2, 2016 11:33 AM
    • Marked as answer by coxml32 Sunday, October 2, 2016 10:43 PM
    Saturday, October 1, 2016 1:18 PM
  • As crazy as it may sound, RefName is not available if the reference is broken. Makes the Reference object a lot less helpful. You can show users the Guid property, but it is not as helpful.

    If you REALLY want to show the name, you must have a table with Guids and Names. You can refresh this table just before you release a new version (at that time there is no broken ref and the RefName *is* available).

    I find it hard to believe that your app needs all those references. Are you sure? Uncheck some of them and recompile.

    Other than that, the developer ALWAYS should develop in the lowest version of Access that the users may have. Few softwares are forward compatible, but many are backward compatible.


    -Tom. Microsoft Access MVP

    • Marked as answer by coxml32 Sunday, October 2, 2016 10:43 PM
    Saturday, October 1, 2016 2:20 PM
  • Tom,

    Thank you for the quick response.  Yes I do need all these references due to automation with other Office products.

    I am working right now to understand late binding and working through the compile errors.  I am currently working on just the Excel reference.  

    Boy I wish I would have understood this part of coding when I started this database using Office 2003.  This does explain why I have issues when the Marine Corps pushes out updates.

    Thank you again Bob.  Have a great day.

    Sunday, October 2, 2016 10:43 PM
  • Hi DBguy,

    Thank you for the quick response.

    I am working through my code now to fix all the early binding issues I have to make into late binding.

    Thank you again.

    • Marked as answer by coxml32 Sunday, October 2, 2016 10:46 PM
    • Unmarked as answer by coxml32 Sunday, October 2, 2016 10:46 PM
    Sunday, October 2, 2016 10:45 PM
  • Thank you for the quick response.  Yes I do need all these references due to automation with other Office products.

    I am working right now to understand late binding and working through the compile errors.  I am currently working on just the Excel reference.  

    Boy I wish I would have understood this part of coding when I started this database using Office 2003.  This does explain why I have issues when the Marine Corps pushes out updates.

    Thank you again Daniel.  Have a great day.

    • Marked as answer by coxml32 Sunday, October 2, 2016 10:46 PM
    • Unmarked as answer by coxml32 Sunday, October 2, 2016 10:46 PM
    Sunday, October 2, 2016 10:46 PM
  • My pleasure.

    Typically the conversion is pretty straightforward.  If you have any trouble post your procedure here and I, or someone else here, will help you out.


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

    Sunday, October 2, 2016 11:41 PM
  • Hi DBguy,

    Thank you for the quick response.

    I am working through my code now to fix all the early binding issues I have to make into late binding.

    Thank you again.

    Hi,

    As Daniel said, don't hesitate to let us know if you run into any problems. Good luck.

    Monday, October 3, 2016 12:09 AM
  • To Daniel, Tom and DBguy,

    I have finished the late binding updates to my database.  I did one reference at a time and have my reference list down to 4.  There were a few minor fixes; i.e. PtrSafe, that needed to be added but everything seems to compile fine. 

    Next step is to put it on line and see if both Office 2013 and Office 2010 users have any issues.  Thank you again for the help.

    During this process, I ran across a statement that DAO is no longer used since 1998.  I should be using ADO.  Boy, this looks confusing and I will have to try to build a sample database from scratch before trying to upgrade my existing database. 

    Any good sites where I can find out more info about ADO programming other than MS. 

    Thank you again.

    Martin

    • Marked as answer by coxml32 Tuesday, October 4, 2016 6:28 PM
    Tuesday, October 4, 2016 6:28 PM
  • Hi Martin,

    Not sure where you read about DAO as being "dead" since 1998. When Access 2007 came out, MS included DAO within the database engine, so a separate reference library is no longer needed. In other words, we still use DAO today.

    Cheers!

    Tuesday, October 4, 2016 6:53 PM