troubleshooting Question

Removing Broken References with VBA

Avatar of Chris
ChrisFlag for United States of America asked on
Microsoft AccessProgrammingMicrosoft Applications
16 Comments1 Solution8000 ViewsLast Modified:
Hello,

I have built a large database in Access 2010, but some of my users are using Access 2007. Access has no problem upgrading references (ie, MS Outlook) to newer versions, but does not nicely downgrade references.  The solution I am attempting to implement is to use an AutoExec function to remove the broken references when the database opens, and then immediately re-add the reference by its GUID.

A simple attempt to remove broken references is:

Dim ref As Variant

With Application.VBE.ActiveVBProject
    For Each ref In .References
        If ref.IsBroken Then
            .References.Remove ref
        End If
    Next
End With

This process for removing the references seems acceptable, as it is documented in this book
http://books.google.com/books?id=8dtQUUBCeTYC&lpg=PA948&ots=3PnVi96AcP&dq=vba%20remove%20broken%20references&pg=PA948#v=onepage&q=vba%20remove%20broken%20references&f=false

However, the ref.IsBroken line fails with "Error in loading DLL".

Even Microsoft uses this kind of code to detect broken references:
http://msdn.microsoft.com/en-us/library/aa196149(v=office.11).aspx

When I run the code in the above article, I get the same "Error in loading DLL" on the ref.IsBroken line.  If I try to Resume Next and remove the broken reference (which is the ultimate goal), I get the same error.

The question is, how do I remove the broken references using VBA when the database is opened?

I have heard a lot of talk of using late binding to resolve reference errors, but this would require significant changes to a very large code base. In addition, I don't want to lose Intellisense, and I'd rather not muddy up my code with conditional compilation.

Similar (failed) attempts at resolving this issue:
https://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Q_22620425.html
https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26881966.html

This thread describes my problem and attempted solutions well:
http://www.access-programmers.co.uk/forums/showthread.php?t=121623
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 16 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros