How to auto check the 'Microsoft ActiveX Data Objects x.x Library' from the Tools --> References?

Question

We share our Excel Macro - MS Access project with our client.

They don't know to select the 'Microsoft ActiveX Data Objects x.x Library' from the Tools --> References.

Any code to automatically update MS ADO library settings?

Note: In Office we are using MS 2010. I think the client's office is using Micorsoft XP.

Answer 1

I suggest above to use late binding, but you could do something like this (my code exactly as used in PPT 2010, should be easy enough to adapt to Access but I do not ever use access).

You may need to change the ADODBReference constant for use in XP. Or you could add another constant and a logic check to see what Application.Version and load from the appropriate destination path.

Public Const ADODBReference As String = "C:\Program Files (x86)\Common Files\System\ado\msado15.dll"

Sub PPT_AddRefToADODBLibrary()
'Adds a programmatic reference to ADODB library if one doesn't already exist
'ADODBReference is a public const refers to Microsoft ActiveX Data Objects 6.0 Library

    If Not PPT_RefExists(ADODBReference, "Microsoft ActiveX Data Objects 6.0 Library") Then
        Application.VBE.ActiveVBProject.References.AddFromFile _
            ADODBReference
    Else:
        'Already installed
    End If

End Sub

The sub above calls on this custom function, which first iterates the active References

Function PPT_RefExists(refPath As String, refDescrip As String) As Boolean
'Returns true/false if a specified reference exists, based on LIKE comparison
' to reference.description.
Dim ref As Variant
Dim bExists As Boolean

'Assume the reference doesn't exist
bExists = False

For Each ref In Application.VBE.ActiveVBProject.References
    If ref.Description Like refDescrip Then
        PPT_RefExists = True
        Exit Function
    End If
Next
PPT_RefExists = bExists
End Function

Answer 2

Trying to simply turn it on with a code like this:

Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Common Files\System\ado\msado15.dll"

you may come across three problems: it is already installed, earlier version is installed, the file path is invalid. So my logic is as follows:

1. Code loops through all refs and checks if ref to Microsoft ActiveX Data Objects 6.0 Library is installed.
2. If not installed, then it tries to install it with error handling.
3. If failure will occur, it means that either earlier version i.e. Microsoft ActiveX Data Objects 2.8 is installed (could be checked while looping) or the file path is invalid.

Code:

Sub AddReferenceMicrosoftActiveXDataObjectsLibrary()
    Const MyRefPath As String = "C:\Program Files (x86)\Common Files\System\ado\msado15.dll"
    Dim ref As Variant
    Dim IsInstalled As Boolean: IsInstalled = False

    For Each ref In Application.VBE.ActiveVBProject.References
        Debug.Print ref.FullPath
        If ref.FullPath = MyRefPath Then IsInstalled = True
    Next

    If IsInstalled = False Then
        On Error GoTo err:
        Application.VBE.ActiveVBProject.References.AddFromFile MyRefPath
        On Error GoTo 0
        Debug.Print "Just installed"
        Exit Sub
    Else
        Debug.Print "Already installed"
    End If  
Exit Sub
err:
    MsgBox "Probably earlier version of Microsoft ActiveX Data Objects is already installed or other error occurred"
End Sub

Answer 3

I think late binding is the only way.
I made an Excel-based application for my office, and every time i prepare new version there is about 10% of users I have to visit to add references.
I found out, that since these computers have different Windows versions, for some dll's there is no version which would exist on each computer.
This makes adding references from code more difficult and I do not want to use late binding.
Thats a pity - most of the dll's functionality I use is compatible among all versions.