Reference Collection in Access 2003

Posted on 2007-07-26
Last Modified: 2013-12-05
Access 2003 .adp/SQL Server 2000.
This application has a routine that loops the reference collection and removes them and then adds them back.
All is good.
My problem is this:  I get runtime errors because between the time the reference is removed and then added back there are other code or class modules that are missing the needed reference.

Anyone know a fix.
Question by:michaelrobertfrench
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    "I get runtime errors "

    And those would be ??

    LVL 84
    Also: Why would you remove and add back references? There is no need to "refresh" references on a working application ...
    LVL 61

    Accepted Solution

    Are you doing this to make your database work with other versions of Access?  You can also accomplish this with Late Binding, and not have reference problems due to mis-matched versions...

    Author Comment

    Typically, one might set references manually.  In this network reference is lost.  The work around has been to set them programmatically and during the time they are programmatically deselected and then reselected this program occurs.

    Here is a Snippit:

    RemoveAddReference(False) is called and before
    RemoveAddReference(True) is called there is vba code that has no object references and a runtime error is generated.

    Private Function RemoveAddReference(pFunction As Boolean)
        'This function is called twice
        'Once with false parameter to remove all non built in references
        'Once with true parameter to add back the references that are needed for this application to work
        'This is a work around because after installing Office 2003 this application looses some references during the night
        'The reason is unknown
        'So the function is placed in the startup procedure and is called with both parameters for a clean reestablishment of refererences
        'The reference issue for this application can be more troublesome since the utilization of Citrix is not standardized
        ' Each user will have their own copy of this application - the actual path and version and release date for .dll and .ocx is not verified by IT
        Dim i As Integer
        'Dim ColOfReferences As New Collection
        Dim Ref As Access.Reference
        Dim WindowsDirectory As String
        Dim OfficeDirectory As String
        If IdentifyOperatingSystem = "Windows XP" Then
            WindowsDirectory = "Windows"
        End If
        If IdentifyOperatingSystem = "Windows 2000" Then
            WindowsDirectory = "WINNT"
        End If
        If IdentifyAccessVersion = "11.0" Then
            OfficeDirectory = "Office11"
        End If
        If IdentifyAccessVersion = "10.0" Then
            OfficeDirectory = "Office10"
        End If
    '    ColOfReferences.Add "C:\" & WindowsDirectory & "\system32\RICHTX32.OCX"
    '    ColOfReferences.Add "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
    '    ColOfReferences.Add "C:\" & WindowsDirectory & "\system32\stdole2.tlb"
    '    ColOfReferences.Add "C:\Program Files\Common Files\System\ado\msado15.dll"
    '    ColOfReferences.Add "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"
    '    ColOfReferences.Add "C:\" & WindowsDirectory & "\system32\MSCOMCTL.OCX"
    '    ColOfReferences.Add "C:\Program Files\Common Files\Microsoft Shared\" & OfficeDirectory & "\MSO.DLL"
    ColReference.Add "C:\WINDOWS\system32\Richtx32.ocx"
    ColReference.Add "C:\Program Files\Microsoft Office\OFFICE11\MSWORD.OLB"
    ColReference.Add "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
    ColReference.Add "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqldmo.dll"
    ColReference.Add "C:\WINDOWS\system32\scrrun.dll"
    ColReference.Add "C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL"
    ColReference.Add "C:\WINDOWS\system32\shdocvw.dll"
    ColReference.Add "C:\WINDOWS\system32\MSHTML.TLB"
    ColReference.Add "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE"
    ColReference.Add "C:\Program Files\Common Files\System\ado\msadox.dll"
    ColReference.Add "C:\Program Files\Common Files\System\ado\msado15.dll"
    ColReference.Add "C:\Program Files\Adobe\Acrobat 7.0\Reader\AcroRd32.dll"
    ColReference.Add "C:\WINDOWS\system32\msscript.ocx"
    ColReference.Add "C:\WINDOWS\system32\stdole2.tlb"

                    Select Case pFunction
                            Case False
                                    Dim iRefCount As Integer
                                    iRefCount = Access.References.Count
                                        For i = iRefCount To 1 Step -1
                                            Set Ref = Access.References(i)
                                                If Ref.BuiltIn = False Then
                                                    With Ref
                                                        With Access.References
                                                                    Debug.Print "ColReference.Add " & """" & Ref.FullPath & """"
                                                             .Remove Ref
                                                        End With
                                                    End With
                                                End If
                                            Next i
                            Case True
                                            For i = 1 To ColOfReferences.Count
                                                With Access.References
                                                   .AddFromFile ColReference.ITEM(i)
                                                End With
                                            Next i
                    End Select
    End Function

    Author Comment

    Perhaps this queston was not written well.  Closing Question by author.
    LVL 61

    Expert Comment

    >Perhaps this queston was not written well.  Closing Question by author.

    Did you wind up using late binding?  If my response did not help out, you can request that the question be deleted/points refunded by posting a question to that effect in Community Support (from your comment, I assumed that was your intent).

    Author Comment

    Perhaps my question was not written well.  But image removing all the library references and then compiling.  If you have modules with code they will generate an exception - alluding to reference missing or whatever.  And that is what is happening since the snippit above removes the references and adds them back according to the predefined collection.  This is because the nodes that use this app do not have any real standards enforced.  I put an all encompassing error handler in to prevent this - another alternative is to store the modules as text in a database and they are introduced into the modules colleciton AFTER the references are set by code. These are viable work arounds but I am thinking - NOT VERY ELEGANT.
    Thanks for you help!

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now