Using ADO to Read a Database

Up till now, we’ve used XL’s QueryTable object to retrieve records from databases.  This post breaks away from the QueryTable to leverage the power of ADO.  One of the advantages of ADO is that we can create user defined functions to use directly in XL just like any normal XL function.

As before, we will use a VBA function that can be easily snapped into other code.  This function uses an SQL Statement and a Connection String to retrieve data and return it to the calling routine as a simple string.

Because we are using ADO, we need to add the “Microsoft ActiveX  Data Objects x.x Library”  reference (Where the x.x is the lowest version of the library your users are likely to have.  Some of my users still have Office 2003 which works with version 2.6).  To add a reference, load your XL spreadsheet, go to the VBE (Alt-F11), and from the menu take Tools > References.  From the “Available References” list, scroll down until you find the reference we need. Check it then click OK.  That’s all there is to it.  

Here is the code:

Function SQLRead(sSQL As String, sConnect As String, _
                 Optional iTimeOut As Integer, _
                 Optional bDisplayErrors As Boolean, _
                 Optional cn As ADODB.Connection) As String

'   SQLRead:     Returns a database result set as a string

'   Parameters:  sSQL           SQL Select statement
'                sConnect       Connection String
'                iTimeout       Milliseconds to wait for results before error
'                bDisplayErrors Set to False to surpress error displays
'                cn             Connection Object. If sent, sConnect is ignored
'                               Use this to speed up multiple reads

'   Example:     Cell(1,2) =  SQLExec(sSQL, sConnect)

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    On Error GoTo ErrHandler
    SQLRead = Failure    'Assume Something went wrong

    Dim cnSent As Boolean
    Dim rs As ADODB.Recordset
   
    Dim s As String
    Dim lCols As Long
    Dim l As Long
   
'   If connection object not sent, create from connection string
    cnSent = True
    If cn Is Nothing Then
        cnSent = False
        Set cn = New ADODB.Connection
        cn.Properties("Prompt") = adPromptComplete
        cn.Open sConnect, "", ""
    End If
    Set rs = New ADODB.Recordset
   
'   Restrict how long the query can run before failing
    If iTimeOut > 0 Then
        cn.CommandTimeout = iTimeOut
    End If
   
'   Get the data from the database
    rs.Open sSQL, cn
      
'   Put results into a comma delimited string
    lCols = rs.Fields.Count
    If Not rs.EOF Then
        rs.MoveFirst
        s = ""
        If Not rs.EOF Then
            For l = 0 To lCols - 1
                s = s & IIf(l > 0, ",", "") & """" & rs(l) & """"
            Next l
        End If
    End If
   
'   Return results
    SQLRead = s
   
ErrHandler:
   
    If Err.Number <> 0 Or cn.Errors.Count <> 0 Then
        SQLRead = Failure
        If Err.Number <> 0 Then
            MsgBox "SQLRead - Error#" & Err.Number & vbCrLf & Err.Description, _
                vbCritical, "Error", Err.HelpFile, Err.HelpContext
        Else
            Dim errLoop As ADODB.Error
            For Each errLoop In cn.Errors
                MsgBox "SQLRead - Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description, vbCritical, "Error", _
                    errLoop.HelpFile, errLoop.HelpContext
            Next errLoop
        End If
    End If
    On Error Resume Next
    If rs.State > 0 Then rs.Close	'Close the record set
'   Close the connection object if created in this routine
    If Not cnSent Then If cn.State > 0 Then cn.Close
    On Error GoTo 0
   
End Function 

To test our function, let’s create an XL User Defined Function (UDF).  The code for our test is shown below.  After adding the code below (with the reference and the function above), go back to XL and put Customer ID “27″ in cell “A4″.  In cell “B4″, enter this formula: “=CustomerName(“A4″).  Our routine will retrieve the customer’s name and display it in cell “B4″. 

Public Function CustomerName(sID As String) As String

'   CustomerName:  Get Customer Name UDF

'   Parameters:    ID     Customer ID

'   Example:       =CustomerName("A4")

'     Date   Init Modification
'   11/10/09 CWH  Initial Programming

    On Error GoTo ErrHandler

    Dim sSQL As String
    Dim sConnect As String

    sSQL = "SELECT  C.`First Name` " & _
           "FROM    Customers C " & _
           "WHERE   C.ID = " & sID
    sConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
               "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"
'   NOTE: For previous versions of Access use:
'   sConnect = "Driver={Microsoft Access Driver (*.mdb)};" & _
'              "DBQ=C:\Users\chatmaker\Documents\Northwind.mdb;"
    CustomerName = Replace(SQLRead(sSQL, sConnect), """", "")

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "CustomerName- Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

How to Copy Code from this Blog to XL

1. Open your XL spreadsheet containing modGeneral.
2. Get to the VBE (Alt-F11)
3. Open modGeneral in the Code Window
4. From this post, select and copy the code
5. Paste into the Code Window (*see next paragraph)
6. Make any corrections to code that didn’t paste correctly
7. From the VBE menu navigate File > Export File…
8. Save modGeneral and remember where you saved it.

Unfortunately, the code won’t paste 100% properly.  You will have to add carriage returns and perhaps fix a few things until your code looks exactly like what you see here.