References 2007 vs 2003 (1 Viewer)

[Malcy]

I am having recurring issues where I am working on ACC2007 on my development system and then installing sometimes onto ACC2007 and other times onto ACC2003 (perhaps more relevantly Office 2003 or Office 2007) depending upon what the client has.
Most of the time this is OK but if my VBA reaches out into Outlook, Word or Excel then the ACC2007 version puts a reference to the 12.0 libraries whereas the ACC2003 versions need the 11.0 libraries. As a result you get a MISSING on the 12.0 library and need to manually go in an uncheck the MISSING reference, locate the 11.0 library, check it and all is hunky dory.
Does anyone know some VBA code I could introduce that would check correct references and reset where there is a mismatch? It would save me headaches with so much cross Office platorm work.
Any thoughts would be most appreciated
Thanks

Malcy

 

[Guus2005]

You could reference the dll's dynamically (by BobLarson, slightly modified by me)

Option Compare Database
Option Explicit

'Dynamically add references

Function FixUpRefs()
    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim intX As Integer
    Dim blnBroke As Boolean
    Dim strPath As String

    On Error Resume Next

    'Count the number of references in the database
    intCount = Access.References.Count
  
    'Loop through each reference in the database
    'and determine if the reference is broken.
    'If it is broken, remove the Reference and add it back.
    Debug.Print "----------------- References found -----------------------"
    Debug.Print " reference count = "; intCount

    For intX = intCount To 1 Step -1
      Set loRef = Access.References(intX)
      With loRef
        Debug.Print " reference = "; .FullPath
        blnBroke = .IsBroken
        If blnBroke = True Or Err <> 0 Then
          strPath = .FullPath
          Debug.Print " ***** Err = "; Err; " and Broke = "; blnBroke
          With Access.References
            .Remove loRef
            Debug.Print "path name = "; strPath
            .AddFromFile strPath
          End With
        End If
       End With
    Next
     '''Access.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
    
  Set loRef = Nothing
  
  ' Call a hidden SysCmd to automatically compile/save all modules.
  Call SysCmd(504, 16483)
End Function

Function AddRefs()
    Dim loRef As Access.Reference
    Dim intCount As Integer
    Dim intX As Integer
    Dim blnBroke As Boolean
    Dim strPath As String

    On Error Resume Next
  
    'Loop through each reference in the database
    'Add all references
    Debug.Print "----------------- Add References -----------------------"
    
          With Access.References
            .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll"
            .AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6.dll"
            .AddFromFile "C:\Program Files\Microsoft Office\Office\msacc9.olb"
            .AddFromFile "C:\Program Files\Common Files\System\ado\msado15.dll"
            .AddFromFile "C:\Program Files\Common Files\System\ado\msado25.tlb"
            .AddFromFile "C:\Program Files\Common Files\System\ado\msadox.dll"
            .AddFromFile "C:\WINNT\System32\stdole2.tlb"
            .AddFromFile "C:\WINNT\System32\scrrun.dll"
          End With
      
  ' Call a hidden SysCmd to automatically compile/save all modules.
  Call SysCmd(504, 16483)
End Function

Share & Enjoy!