ActiveDirectory with VBA Part 5 - Querying LDAP with ADO

So in addition to using the GetObject("LDAP//... syntax one can also query LDAP Active Directory using ActiveX Data Objects (ADO). You will need to use the Microsoft OLE DB Provider for Microsoft Active Directory Service.

I can give some VBA which queries the AD LDS instance created in part 1, part 2 and part 4 of this series. If you run the following code you should get the table of results shown below.

Sub QueryingLDAPWithADO()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=ADSDSOObject" '* ref https://msdn.microsoft.com/en-us/library/aa746471(v=vs.85).aspx
    cn.Open

    Sheet1.Cells.ClearContents
    Sheet1.Range("A1:F1").Font.Italic = False
    Sheet1.Range("A1:F1").Font.Bold = True
    Sheet1.Range("A2:F2").Font.Italic = True


    Dim cmdSQL_Dialect As ADODB.Command
    Set cmdSQL_Dialect = New ADODB.Command
    cmdSQL_Dialect.CommandText = "Select nAME,distinguishedName FROM 'LDAP://localhost:389/o=Microsoft,c=US' WHERE objectClass='*'"

    Set cmdSQL_Dialect.ActiveConnection = cn
    
    Dim rsSQL_Dialect As ADODB.Recordset
    Set rsSQL_Dialect = cmdSQL_Dialect.Execute
    Sheet1.Cells(1, 1).Value = "SQL Dialect query"
    Sheet1.Cells(2, 2).Value = "Name"    '* seems the fields are in reversed order, perhaps alphabetical?
    Sheet1.Cells(2, 1).Value = "DistinguishedName"
    Sheet1.Cells(3, 1).CopyFromRecordset rsSQL_Dialect

    Dim cmdLDAP_Dialect As ADODB.Command
    Set cmdLDAP_Dialect = New ADODB.Command
    cmdLDAP_Dialect.CommandText = ";(objectClass=*);CN; subtree"
    
    Set cmdLDAP_Dialect.ActiveConnection = cn
    
    Dim rsLDAP_Dialect As ADODB.Recordset
    Set rsLDAP_Dialect = cmdLDAP_Dialect.Execute
    
    Sheet1.Cells(1, 4).Value = "LDAP Dialect query"
    Sheet1.Cells(2, 4).Value = "CN"
    Sheet1.Cells(3, 4).CopyFromRecordset rsLDAP_Dialect
End Sub