Home > Blockchain >  How to display a 2D array recordset from access in excel
How to display a 2D array recordset from access in excel

Time:10-21

I was wondering how to display correctly the result of my SQL request :

In access the SQL Query show :

enter image description here

and my VBA code just display the sectors name. How can I display both (Sector Count result) ?

Below my VBA Code :

sQuery = "SELECT Sector, COUNT(*) FROM Test WHERE Search = 'FR' AND LaDate = #20/10/2021# GROUP BY Sector"
Set dbRecSet = New ADODB.Recordset
dbRecSet.Open Source:=sQuery, ActiveConnection:=dbConn

If (dbRecSet.RecordCount <> 0) Then
    Do While Not dbRecSet.EOF
        Debug.Print dbRecSet.Fields(0).Value
        dbRecSet.MoveNext
    Loop
End If

According to a comment I added

Debug.Print dbRecSet.Fields(1).Value

Does a way exist to dislpay without loop ?

CodePudding user response:

You would need to show field 1 also, like so: Debug.Print dbRecSet.Fields(0).Value,dbRecSet.Fields(1).Value

There are also, getrows, getstring and copyfromrecordset available to you as well. GetRows will return an Array, so will need looping or pasting to Excel and getString will return a String. CopyFromRecordset, will paste the recordset contents to a range in Excel for you.

  • Related