Beyond Excel: VBA and Database Manipulation

Nov 10 2009   5:58PM GMT

Say Goodbye to QueryTables

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

We started with the QueryTable object in order to ease into this subject.  It’s time to say farewell to QueryTable and fully embrace ADO.  I’m using ADO, not because it’s the best, but because it is compatible with everything my customers have and (I’m guessing) with 98% of all current XL installs, and because the coding for ADO is nearly identical to the older DAO and other newer database access methods available to VBA.  So as technology progresses, our code needn’t change drastically.

We are replacing the QueryTable object with a call to another function that snaps into our code.  The function looks very much like the SQLRead function from the last post.  Here it is:

Function SQLLoad(sSQL As String, _
                 sConnect As String, _
                 sRange As String, _
                 Optional sSheet As String, _
                 Optional sName As String, _
                 Optional iTimeOut As Integer, _
                 Optional bAppend As Boolean) As Boolean
'   SQLLoad:     Loads an XL Range with data from a database table/file
'   Parameters:  sSQL        SQL Select statement
'                sConnect    Connection String
'                sRange      upper left cell address (ex. "A5") to put data
'                sSheet      worksheet to receive data
'                sName       name to give the retuned data
'                iTimeout    milliseconds to wait for a result before quitting
'                bAppend     Set to true to append data to a previous result
'   Example:     bResult =  SQLLoad("Select * From QUSRSYS/QAEZDISK", _
'                                   "DRIVER={iSeries Access ODBC Driver};" & _
'                                   "SYSTEM=;NAM=1;", _
'                                   "A4","Data","Data",300,False )
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    SQLLoad = Failure           'Assume Something went wrong
    Dim cn As ADODB.Connection  'Connection Object
    Dim rs As ADODB.Recordset   'Recordset Object
    Dim lRows As Long           'Number of Rows in Range
    Dim lCols As Long           'Number of Columns in Range
    Dim l As Long               'Generic long variable
    Settings "Save"             'Save XL Settings
    Settings "Disable"          'Disable screen updates, etc.
 '  Print the start time and SQL string in VBA's immediate window
    Debug.Print "Start:", Time, sSQL
 '  Clear spreadsheet if this is not appending to existing data
    If Not bAppend Then
        If sSheet > " " Then
            If ClearAll(sSheet) Then GoTo ErrHandler
        End If
    End If
'   Create Connection and Recordset Objects
    Set cn = New ADODB.Connection
    cn.Properties("Prompt") = adPromptComplete
    cn.Open sConnect, "", ""
    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
    With Range(sRange)
        lCols = rs.Fields.Count
'       Add column headings (if not appending)
        If Not bAppend Then
            For l = 0 To lCols - 1
                .Cells(1, l + 1) = rs(l).Name
                .Cells(1, l + 1).Font.Bold = True
            Next l
            l = 2
'       Position to end of data to append to
            lRows = Range(sName).Rows.Count - 1
        End If
'       Read Recordset and copy to XL
        While Not rs.EOF
            lRows = lRows + .Cells(lRows + 2, 1).CopyFromRecordset(rs, 10000)
            If Not rs.EOF Then lRows = lRows - 1
'       Resize columns to fit data
'       Create a name for the data
        If sName > " " Then Names.Add sName, Range(.Cells(1, 1), _
            .Cells(lRows + 1, lCols))
    End With
 '  Set column headings to repeat at top of page on printouts
    ActiveSheet.PageSetup.PrintTitleRows = "$" & Range(sRange).Row & _
                                          ":$" & Range(sRange).Row
 '  Print the end time in VBA's immediate window
    Debug.Print "End:", Time
    SQLLoad = Success
    If Err.Number <> 0 Or cn.Errors.Count <> 0 Then
        SQLLoad = Failure
        If Err.Number <> 0 Then
            MsgBox "SQLLoad - Error#" & Err.Number & vbCrLf & Err.Description, _
                vbCritical, "Error", Err.HelpFile, Err.HelpContext
            Dim errLoop As ADODB.Error
            For Each errLoop In cn.Errors
                MsgBox "SQLLoad - Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description, vbCritical, "Error", errLoop.HelpFile, _
            Next errLoop
        End If
    End If
    On Error Resume Next
    If rs.State > 0 Then rs.Close   'Close Recordset
    If cn.State > 0 Then cn.Close   'Close Connection
    Settings "Restore"              'Restore XL Settings
    On Error GoTo 0
End Function 


Here is the modified MACRO1() with the QueryTable replaced.  Note that it looks similar, and cleaner.

Sub Macro1()
    Dim s As String
    Dim sSQL As String
    Dim sConnect As String
    s = Trim( _
             InputBox("Enter State Code:" & vbCr & vbCr & _
                 "'%' is a wildcard.  By itself it will retrieve all states. " & _
                 "'N%' will retrieve all states beginning with 'N'" & vbCr & _
                 "'%Y' will retrieve all states ending in 'Y'", _
                 "State Code Prompt", "%") _
    If s > "" Then
        sConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                   "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"
        sSQL = "SELECT O.`Order ID`, O.`Customer ID`, " & vbCr & _
               "       O.`Order Date`, C.`First Name`, " & vbCr  & _
               "       O.`Ship State/Province`, D.Quantity, " & vbCr & _
               "       P.`Product Name` " & vbCr & _
               "FROM   Customers C, Orders O, " & vbCr & _
               "       `Order Details` D, Products P " & vbCr & _
               "WHERE  O.`Customer ID` = C.ID " & vbCr & _
               "  AND  O.`Order ID`    = D.`Order ID` " & vbCr & _
               "  AND  D.`Product ID`  = P.ID " & vbCr & _
               "  AND  C.`State/Province` LIKE '" & s & "'"
        SQLLoad sSQL, sConnect, "A4", "Data", "Data"
    End If
End Sub

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.

14  Comments 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.
  • webluca
    Hi,this seems a good approach, but I can't understand why you want to say goodbye to querytables. Is this method faster than querytables?
    60 pointsBadges:
  • webluca
    Is this method faster than QueryTables? Or there is a other reason to use this method instead of querytables?
    60 pointsBadges:
  • Craig Hatmaker
    Thanks for posing the question. I should put this explanation in my post. From my research, QueryTables are quite fast. They are wonderful if you don't know VBA. If you know VBA then ADO provides advantages over QueryTables: [1] ADO doesn't require DSNs. QueryTables do (there is a way around this, but it's convoluted). DSNs are just one more thing to install and maintain on the target PC - so avoiding DSNs makes things simpler. [2] It is possible with ADO to query two different databases simultaneously - for example a SQL Server database AND an MS Access database. [3] The big advantage of QueryTables is their graphical MS Query front end; however, MS Query is limited as to what it can present through its GUI. [3a]Many moderately complex SQL statements cannot be built in MS Query w/o using the SQL dialog. [3b]MS Query often strips custom column headings and ignores re-arranging columns. [4] ADO works with MS Word and other Office apps. So if we're not going to us MS Query, then from a VBA perspective, ADO is simpler and affords us better control.
    1,860 pointsBadges:
  • AnjaliCDW
    How would one do formatting when using the ADO?  My problem is that I have a tab delimited text file and I'm trying to read it in using the ADO and I don't know how to specify this without creating a text driver.  This is why I resorted to a query table. 
    0 pointsBadges:
  • Craig Hatmaker
    "How would one do formatting when using the ADO?"  Formatting is usually applied after data is loaded.  In a sense, the same applies to QueryTables.  We really can't format the results until we have a QueryTable to begin with.  To pre-format an ADO result we  could create an empty "Table" (aka "List" in XL2003) and format it.  Then, when ADO loads the empty Table, the formatting would be automatically extended to all rows.Regarding the driver: ADO can use any driver used by QueryTables.  This posts says good-bye to QueryTables, not because they're bad, but because moving on to updating databases requires ADO. 
    1,860 pointsBadges:
  • Kalehpanir
    When you first copy the code to Word, then from Word to VBE then it will copy without mixups.
    0 pointsBadges:
  • vbanovice
    Hi, First I'd like to say that I'm really impressed with your blog and have learned so much from your code examples since stumbling upon this site a few days ago.  However, I seem to have run into an issue in the code for the "SQLLoad" Function.
    VBE is throwing a "Compile Error: Sub or Function not defined" on the following line:

    If ClearAll(sSheet) Then GoTo ErrHandler

    I went back through your posts to make sure I somehow didn't skip the section where ClearAll() was defined but I'm not finding it.  Is it something I'm missing? Could you help point me in the right direction?
    0 pointsBadges:
  • Craig Hatmaker
    Below is ClearALL(). A lot has changed since 2009. You may find this approach simpler: Function ClearAll(sSheet As String) As Boolean ' ClearAll: Clear entire worksheet ' Parameters: sSheet - the name of the worksheet to be cleared ' Example: bResult = ClearAll ' Date Init Modification ' 01/01/01 CWH Initial Programming On Error GoTo ErrHandler ClearAll = Failure 'Assume Something went wrong Application.StatusBar = "Clearing Sheet" Sheets(sSheet).Cells.ClearContents Sheets(sSheet).Cells.ClearFormats Do While Sheets(sSheet).Names.Count > 0 Sheets(sSheet).Names(1).Delete Loop Sheets(sSheet).Range("A1").Select Application.StatusBar = "" ClearAll = Success 'Normal end, no errors ErrHandler: If Err.Number 0 Then MsgBox _ "ClearAll - Error#" & Err.Number & vbCrLf & _ Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext On Error GoTo 0 End Function
    1,860 pointsBadges:
  • zarehgasparian

    1 - How is it possible to query data from two different databases in AD)?
    2 - Can you query data from web by ADO
    0 pointsBadges:
  • Craig Hatmaker

    1) is it possible to query data from two different databases.

    a) If we are talking about 2 different tables in the same database, use a join

    b) If we are talking about 2 different tables in two different SQL Server databases, we can qualify each table with the database name and use a join.

    c) If we are talking about 2 different tables in two different databases by 2 different providers, say Oracle and SQL Server, then we have to try the ISAM method,  See:

    1,860 pointsBadges:
  • Craig Hatmaker

    2) Can we query data from web by ADO.

    a) Not with HTTP.  If we establish a VPN connection, we can. 

    b) We can use XL's Get External Data, From Web to load data from web sources.

    c) We can use the Browser Object and DOM to pull data from web pages.  See

    1,860 pointsBadges:
  • olddirtybaird
    Hi, I too am a lover of ADO, but have run into a situation and was curious if you had experience in getting around it.

    My issue: When multiple users try to send SQL read-only queries to an Excel file at the same time, it errors and states another user has it open. This seems silly to me, but from my experience it appears only 1 user at a time can query an Excel file via ADO. Any thoughts or advice?

    Background: I've got a report that dumps a csv file every Sunday with the latest information, and I have multiple users using other files/applications to query against. I love ADO, but it's failing me here. Querytables seem to be the only thing allowing multiple-user queries at the same time.

    10 pointsBadges:
  • Craig Hatmaker
    Hi Stephen, I have not run into that problem. Perhaps your connection string is opening for update with "READONLY=FALSE". See:
    1,860 pointsBadges:
  • olddirtybaird
    Hey Craig, thanks for much for the quick reply! Sadly, I tried that and could not get it to work when another user is querying the same csv file at the same time. It pops up a Access error stating that another user has it open. Again, this is silly to me considering readonly access, but based on what I've Googled, it seems it's a flaw in ADO logic with csv(text) files.
    10 pointsBadges:

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:

Share this item with your network: