Executing SQL from Excel and Data Abstraction

Abstracting an Excel data source.

In almost of all of the downloadable examples on the ramblings web site, I use a set of data abstraction classes to separate workbook data from its workbook location, and as a result, these classes now have a fair amount of capability built in such as Excel-jSon-Excel conversion, getting data from Google Docs and many other functions.

Most of you will already know about how to get data into Excel from an SQL database, whether through ODBC, MS Query, ADODB, the Excel toolbar and many other methods.

Getting data into excel using SQL can be kind of complicated, with all the confusion around connection strings and recordsets and so on, so I thought I could combine the ideas of creating a simple library class to connect to various sql data sources of the type implemented in the rest-Excel library, and the Excel regular expression library along with the data abstraction classes, so that they could be used to create really simple VBA functions.

As with previous posts such as  Rest-Excel in one line of code and excel-Json in one line of code , I set myself the target of being able to execute SQL on a variety of data sources, populate Excel worksheets, and do various conversions such as to jSon, all in just one line of VBA code. You can download the resultant classes and examples and read in more detail on the ramblings web site, but here are some examples using the cADO library class.

Some examples

Sub testADOCopyCurrent()
    Dim ado As New cADO
    ado.init().execute
End Sub

Sub testADOCopySpecificFilterSpecifyColsAdd()
    Dim ado As New cADO
    ado.init(Range("testAdo!c1"), , False).execute _
        ("tweetsentiments", "value", "where value=1").kill
End Sub

Copy a worksheet, using SQL filtering, convert it to jSon, and serialize the jSon nicely formatted as per jSonLint.

Sub testADOCopySpecificFilterSpecifyColsjSon()
    Dim ado As New cADO
    Debug.Print ado.init(Range("testAdo!a1")).execute _
        ("tweetsentiments", , "where value=1").dSet.jObject.Serialize(True)
End Sub

Get data from an access database using sql, and populate a worksheet

Sub testADOaccess()
    Dim ado As cADO
    Set ado = New cADO
        ado.init(Range("testadoaccess!a1"), _
            "D:googlechartsqlTest.accdb").execute _
            "dogOwners", "firstname,lastname", "where dogowner=true"
    
End Sub

Sub testADOclosed()
    Dim ado As cADO
    Set ado = New cADO
    Debug.Print _
        ado.init(Range("testadoclosed!a1"), _
            "D:googlechartroadmapgenerator.xlsm").execute( _
            "inputdata").dSet.jObject.Serialize(True)
    
End Sub