Excel VBA SQL query ADODB

Question

Hi I have an existing Excel sheet with some data inside, and now I want to perform queries directly from VBA. This is what I have now:

Private Sub CommandButton1_Click()
Dim sSQLQry As String
Dim ReturnArray
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
Dim newSheet As Worksheet
'DBPath = ThisWorkbook.FullName
DBPath = "C:\someData.xlsm"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
Conn.Open sconnect
sSQLSting = "SELECT username,count(username) FROM [Sheet1$] group by username order by count(username) desc;"
mrs.Open sSQLSting, Conn
Set newSheet = Sheets.Add
ActiveSheet.Range("A1").CopyFromRecordset mrs
mrs.Close
Conn.Close
End Sub

This query performs well and gives the desired result, but when I change it to this one:

Select param0,count(param0) From [Sheet1$] where eventid='addToCart' group by param0 order by count(param0) desc;

Because the param0 is like this: most of them are numbers, but some of them are numbers and characters mixed together, so the query result only returns the pure-number entries. So how can I configure the database so that it recognizes the param0 field should be text, instead of int? Also, when I perform this query:

Select eventid,param0,param1,count(*) From [Sheet1$] where eventid='search' group by param0, param1 order by count(*) desc;

It gives 'automation error'. I searched for it but could not get a suitable solution. Can anyone help with this? Thank you!

Edit: all the three queries give correct results in MySQL workbench. Now I need to perform the query directly in Excel sheet.

user4039065 · Accepted Answer · 2016-02-11 04:45:44Z

Your second problem lies within the fact that you are asking for the eventid field without including it within the aggregate GROUP BY clause.

SELECT eventid, param0, param1, count(*)
  FROM [Sheet1$]
  WHERE eventid='search'
  GROUP by eventid, param0, param1
  ORDER BY COUNT(*) DESC;

I ran your repaired queries against some sample data that I made up and came up with this.

Sub grp_param()
    Dim cnx As Object, rs As Object, rs1 As Object
    Dim sWS1 As String, sWS2 As String, sWB As String, sCNX As String, sSQL As String
    Dim ws1TBLaddr As String

    ws1TBLaddr = Worksheets("Sheet4").Cells(1, 1).CurrentRegion.Address(0, 0)
    sWS1 = Worksheets("Sheet4").Name

    sWB = ThisWorkbook.FullName
    'for 64-bit Office
    'sCNX = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" & sWB _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    'for 32-bit or 64-bit Office
    sCNX = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWB _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Debug.Print sCNX

    Set cnx = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set rs1 = CreateObject("ADODB.Recordset")

    cnx.Open sCNX

    'Select param0,count(param0) From [Sheet1$] where eventid='addToCart'
    ' group by param0 order by count(param0) desc;
    sSQL = "SELECT param0, COUNT(param0) " & _
           "FROM [" & sWS1 & "$" & ws1TBLaddr & "] " & _
           "GROUP BY param0 " & _
           "ORDER BY count(param0) DESC;"
    Debug.Print sSQL

    rs.Open sSQL, cnx

    With Worksheets.Add(after:=Sheets(Sheets.Count))
        .Name = "Summary Data"
        .Range("A1").Resize(1, 2) = Array("param0", "count")
        .Range("A2").CopyFromRecordset rs
    End With

    'Select eventid,param0,param1,count(*) From [Sheet1$] where eventid='search'
    'group by param0, param1 order by count(*) desc;
    sSQL = "SELECT eventid, param0, param1, COUNT(*) " & _
           "FROM [" & sWS1 & "$" & ws1TBLaddr & "] " & _
           "GROUP BY eventid, param0, param1 " & _
           "ORDER BY count(param0) DESC;"
    Debug.Print sSQL

    rs1.Open sSQL, cnx

    With Worksheets(Sheets.Count)
        .Range("E1").Resize(1, 3) = Array("eventid", "param0", "count")
        .Range("E2").CopyFromRecordset rs1
    End With

    rs.Close: Set rs = Nothing
    rs1.Close: Set rs1 = Nothing
    cnx.Close: Set cnx = Nothing

End Sub

I'm unclear on why mySQL allows that last query but my background is in T-SQl and it would certainly choke on that.

Community · Accepted Answer · 2017-05-23 11:52:27Z

0

For the first confusion, I noticed the post here: link , but I don't want to add in another file for processing, so in the end no other choice, I pre-process the file by adding in 4 lines of texts right below the header line. (As in my case all fields can be text; I did this in MySQL) As the amount of data is quite big, these dummy texts don't affect the result yet help me produce the DB correctly.

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Feb 11, 2016 at 7:10

richards

52710 silver badges28 bronze badges

Collectives™ on Stack Overflow

Excel VBA SQL query ADODB

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related