VBA/Excel - querying Apache Drill

Question

I trying to built a macro for querying the Apache Drill. I cannot get it working.

I tried two ways.

First using the QueryTables.Add. It keeps adding the results so that is not updating the results but only shifts the old results to the right and inserts new set in A1.

Q: how to make the query delete old results and place new ones in the same place?

Second try using the ADODB.Recordset. The connection doesn't work

Error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Q: can ADODB.Connection, ADODB.Recordset be used for querying data from Appachi Drill?

First attempt

Sub S3Download()

Dim sConn As String
Dim oQt As QueryTable
Dim sSql As String

sConn = "ODBC;DSN=MapR Drill;"
sSql = "SQL statement"
Set oQt = Sheets("Data").QueryTables.Add(Connection:=sConn,_
         Destination:=Sheets("Data").Range("A1"), SQL:=sSql)

        With oQt
            .Name = "Query1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=True
            .RefreshStyle = xlInsertDeleteCells
        End With
End Sub

Second attempt

Sub S3_download()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String


Dim qt As QueryTable
ThisWorkbook.Sheets("Data").Activate

ConnString = "Driver={MySQL ODBC 5.1 Driver};DSN=MapR Drill;"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "SQL statement"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = ThisWorkbook.Sheets("Data").ListObjects.Add(SourceType:=XlListObjectSourceType.xlSrcQuery, Source:=oRS, _
            Destination:=ThisWorkbook.Sheets("Data").Range("A1")).QueryTable

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If

If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

Answer 1

I worked around the first way (macro clears the "Data" sheet first and then adds the results from the query in the desired place)

Sub S3Download()

    Dim sConn As String
    Dim oQt As QueryTable
    Dim sSql As String


    'removing old query data

    Sheets("Data").Cells.ClearContents

    For Each qTable In Sheets("Data").QueryTables
                qTable.Delete
    Next qTable



    sConn = "ODBC;DSN=MapR Drill;"
    sSql = "SQL statement"
    Set oQt = Sheets("Data").QueryTables.Add(Connection:=sConn,_
             Destination:=Sheets("Data").Range("A1"), SQL:=sSql)

            With oQt
                .Name = "Query1"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = False
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh BackgroundQuery:=True
                .RefreshStyle = xlInsertDeleteCells
            End With
    End Sub