CODEWITHSUNDEEP
excelvbaado
richards
richards

Reputation: 527

Excel VBA SQL query ADODB

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.

Upvotes: 0

Views: 5533

Answers (2)

richards
richards

Reputation: 527

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.

Upvotes: 0

user4039065
user4039065

Reputation:

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.

Upvotes: 1

Related Questions