Having reference problems?

Left and string not working?   Other bizarre problems?   Do you have any references besides the basic three or four?  Are you sure you need them?  Write down the path and name of the extra ones (or put the following code in a module and execute the code), delete from the references list and Compile and Save All.   Keep any necessary references and ensure they are distributed to the target system.  

The following code uses late binding and gives you the full reference name such as you see in the references window.  (Early binding would’ve required the Microsoft Visual Basic for Applications Extensibility reference.  So lets make things simpler for the users.)

Sub ViewMoreReferenceDetails()

Dim refIDE As Object

    For Each refIDE In Access.Application.VBE.ActiveVBProject.References
        If refIDE.IsBroken = True Then
            Debug.Print "Broken, GUID – " & refIDE.Guid
        Else
            Debug.Print refIDE.Description & " – " & refIDE.Name & " – " & _
                refIDE.Major & "." & refIDE.Minor & vbCrLf & _
                "       Location – " & refIDE.FullPath
        End If
    Next refIDE

End Sub

Note that, to my knowledge, you can’t get any information about a broken reference other than the GUID.   As this information for missing reference is included somewhere inside the Access database file I don’t have any access to the information that MS displays for a broken reference.   And it’s not really needed for this kind of trouble shooting.  It’s enough to know there is a broken reference.

To execute this code copy into a VBA module or create a new VBA modle.  Copy and paste this code into the module.   Ensure your cursor is within the subroutine and press F5. Then Ctrl+G will take you into the Debug/Immediate window to view the results.  You should now see a list such as

Visual Basic For Applications  – VBA – 4.0
       Location – C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Microsoft Access 11.0 Object Library  – Access – 9.0
       Location – C:\Program Files\Microsoft Office 2003\OFFICE11\msacc.olb
Microsoft ActiveX Data Objects 2.8 Library  – ADODB – 2.8
       Location – C:\Program Files\Common Files\System\ado\msado15.dll
Microsoft DAO 3.6 Object Library  – DAO – 5.0
       Location – C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

For a very detailed page on reference problems see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Updated 2010-10-22.  Thanks to fellow Access MVPs Tom van Stiphout for pointing this out and to John Spencer for pointing out an easier way of running the code.

4 thoughts on “Having reference problems?”

  1. Looks useful, Tony. Thanks for the code!

    FWIW, when I migrated from xl2003 to 2010 (my company’s IT department removed 2003 completely when they installed 2010) I experienced trouble with Left. It took me a while, but I discovered that the problem was that I was using Convert from the old VBA Analysis Pak. Why the compiler was flagging Left instead of Convert I never understood – the solution was to uncheck the Missing Reference (to the 2003 analysis pak); recompile – which would now fail at the Convert function call; revise to point to the now built-in worksheet function; recompile and save.

    Clif

  2. You could also have a function to drop all references and then set them during the startup. If a reference failed to link, the code would break at that point, then you would know what was missing. JMHO

  3. Art, there are two references you can’t drop. Also you can’t change references in an MDB/ACCDE.

    And I’d be reluction to delete/recreate them in any environment outside a known corporate environment.

    That said it’s probably been a decade or so since I last used any references besides the required two and DAO/ACE/ADO.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>