Automatically Update References

**dandoescode** · 06-27-2012, 06:11 AM

I've been tasked with writing a script that will run every time I open a database, it will check to see if there are any missing libraries, and if there are, find them. Is this possible? I don't really even know where to start. Any hints, links or code would be extremely helpful.

**dandoescode** · 06-27-2012, 06:56 AM

I've thought some more on the problem and I think I have a better plan.

The code needs too:

1) Check and see what version of Access is being run.
2) Check against the databases references against a master list of necessary ones (I already have this)
3) Add the missing references
4) Check and make sure that none of the references are broken.
5) Fix any broken references.

**RuralGuy** · 06-27-2012, 07:18 AM

This link may be of some help: http://www.accessmvp.com/djsteele/Ac...nceErrors.html

**dandoescode** · 06-27-2012, 08:45 AM

Indeed it was!

I'm currently attempting to check the db's references, against my master list. I know that the filenames are correct but my piece of code returns false.. any ideas

Code:

If StrComp(chkRef.FullPath = "C:\Program Files (x86)\Microsoft Office\Office14\MSACC.OLB", vbTextCompare) = 0 Then
match = True

**dandoescode** · 06-27-2012, 08:46 AM

I'm wondering if maybe vba doesn't interpret chkRef.FullPath as a string or something

**RuralGuy** · 06-27-2012, 09:52 AM

I'm not familiar with chkRef.

**dandoescode** · 06-27-2012, 10:00 AM

Ooops sorry, variable name its a reference.

**RuralGuy** · 06-27-2012, 10:14 AM

Is it a collection so that chkRef.FullPath makes sense?

**dandoescode** · 06-27-2012, 10:18 AM

hmm, not sure what you mean.

Code:

Dim chkRef As Reference
For Each chkRef In References
MsgBox (chkRef.FullPath)
Next chkRef

This piece of code returns the path of each reference file, for instance
the first returned is the Visual Basic for Apps Library
which gives

C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL

**RuralGuy** · 06-27-2012, 10:31 AM

I didn't realize you could do that and YES that is a collection. It sounds like it is time to do some single stepping of the code.

**dandoescode** · 06-27-2012, 10:56 AM

Okay, have confirmed that this issue is that chkRef is a Reference file, so it cant be compared as a string, know of any way to convert it?

**RuralGuy** · 06-27-2012, 11:07 AM

I would go back to the link I supplied and see if there is another way.

**dandoescode** · 06-28-2012, 10:09 AM

Well it seems that stupidity and determination have come together for the win this time. The code checks the users version of access, and then adds the required references.

Code:

Option Compare Database

Private Sub Command2_Click()

'Variable Declarations

Dim version As Integer
Dim chkRef As Reference
Dim strRef As String
Dim match As Boolean
match = False

'Check version, seperate on case by case basis with if statements
version = Application.version




'Deal with 2010
If version = 14 Then
        
        
        'Look for VBA Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "VBA" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBE7.DLL")
        MsgBox (" Added reference C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBE7.DLL")
        End If
        match = False
        
        
        
        'Look for Access Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Access" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\MSACC.OLB")
        MsgBox (" Added reference C:\Program Files (x86)\Microsoft Office\Office14\MSACC.OLB ")
        End If
        match = False
        
        
        'Look for OLE Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "stdole" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Windows\SysWOW64\stdole2.tlb")
        MsgBox (" Added reference C:\Windows\SysWOW64\stdole2.tlb ")
        End If
        match = False
        
        
        'Look for DAO Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "DAO" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL")
        MsgBox (" Added reference C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\ACEDAO.DLL")
        End If
        match = False
        
        'Look for Office Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Office" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL")
        MsgBox (" Added reference C:\Program Files (x86)\Common Files\ Microsoft Shared\OFFICE14\MSO.DLL")
        End If
        match = False
        
        
        
        'Look for Word Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Word" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB")
        MsgBox (" Added reference C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB")
        End If
        match = False
        
        
        'Look for Excel Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Excel" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE")
        MsgBox (" Added reference C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE")
        End If
        match = False
        
        
        'Look for OutLook Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Outlook" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Microsoft Office\Office14\MSOUTL.OLB")
        MsgBox (" Added reference C:\Program Files (x86)\Microsoft Office\Office14\MSOUTL.OLB")
        End If
        match = False
        
        
        'Look for ADODB Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "ADODB" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files (x86)\Common Files\System\ado\msado15.dll")
        MsgBox (" Added reference C:\Program Files (x86)\Common Files\System\ado\msado15.dll")
        End If
        match = False
        
        
        'Look for Scripting Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Scripting" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Windows\SysWow64\scrrun.dll")
        MsgBox (" Added reference C:\Windows\SysWow64\scrrun.dll")
        End If
        match = False
          
        
        'Look for Common Controls Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "MSComctlLib" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Windows\SysWow64\MSCOMCTL.OCX")
        MsgBox (" Added reference C:\Windows\SysWow64\MSCOMCTL.OCX")
        End If
        match = False
        
'Deal with 2007
ElseIf version = 12 Then





'Deal with 2003
ElseIf version = 11 Then

'Look for VBA Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "VBA" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files\Common Files\Microsoft Shared\vba\vba6\vbe6.dll")
        MsgBox (" Added reference C:\Program Files\Common Files\Microsoft Shared\vba\vba6\vbe6.dll")
        End If
        match = False
        
        
        
        'Look for Access Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Access" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files\Microsoft Office\Office11\msacc.olb")
        MsgBox (" Added reference C:\Program Files\Microsoft Office\Office11\msacc.olb ")
        End If
        match = False
        
        
        'Look for OLE Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "stdole" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Windows\system32\stdole2.tlb")
        MsgBox (" Added reference C:\Windows\system32\stdole2.tlb ")
        End If
        match = False
        
        
        'Look for DAO Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "DAO" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\program files\common files\microsoft shared\dao\dao360.dll")
        MsgBox (" Added reference C:\program files\common files\microsoft shared\dao\dao360.dll")
        End If
        match = False
        
        'Look for Office Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Office" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files\Common Files\Microsoft Shared\Office12\MSO.DLL")
        MsgBox (" Added reference C:\Program Files\Common Files\Microsoft Shared\Office12\MSO.DLL")
        End If
        match = False
        
        
        
        'Look for Word Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Word" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("E:\Program Files\Microsoft Office\Office11\MSWORD.OLB")
        MsgBox (" Added reference C:\Program Files\Microsoft Office\Office11\MSWORD.OLB")
        End If
        match = False
        
        
        'Look for Excel Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Excel" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("E:\Program Files\Microsoft Office\Office11\EXCEL.EXE")
        MsgBox (" Added reference C:\Program Files\Microsoft Office\Office11\EXCEL.EXE")
        End If
        match = False
        
        
        'Look for OutLook Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "Outlook" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("E:\Program Files\Microsoft Office\Office11\msoutl.olb")
        MsgBox (" Added reference C:\Program Files\Microsoft Office\Office11\msoutl.olb")
        End If
        match = False
        
        
        'Look for ADODB Reference, if found do nothing, if its missing add it
        For Each chkRef In References
        
        strRef = chkRef.Name
        If strRef = "ADODB" Then
        match = True
        End If
        Next chkRef
        
        If match = False Then
        References.AddFromFile ("C:\Program Files\Common Files\System\ado\msado15.dll")
        MsgBox (" Added reference C:\Program Files\Common Files\System\ado\msado15.dll")
        End If
        match = False
        
        


'Give the user an error message if version not handled
Else

MsgBox (" Access Version not recognized, update failed.")


End If


End Sub

**dandoescode** · 06-28-2012, 10:14 AM

I still need to add the code for 2007, but the 2003, 2010 stuff appears to be working well. My main issue now is trying to figure out if in the local db's this will be pushed out to the program files will always be located in the C drive. I hope so.

Automatically Update References

Thread Tools

Automatically Update References

Similar Threads

Query to automatically update report

Set Access to automatically update

Automatically update field

How to update fields automatically

Automatically update datasheet

Tags for this Thread

Posting Permissions