Reference Collection in Access 2003

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.
Who is Participating?
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...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I get runtime errors "

And those would be ??

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also: Why would you remove and add back references? There is no need to "refresh" references on a working application ...
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

michaelrobertfrenchAuthor Commented:
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
michaelrobertfrenchAuthor Commented:
Perhaps this queston was not written well.  Closing Question by author.
>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).
michaelrobertfrenchAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.