Beyond Excel: VBA and Database Manipulation

Nov 7 2009   10:35PM GMT

Using ADO to Read a Database

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: