CODEWITHSUNDEEP
sqlexcelvba
TourEiffel
TourEiffel

Reputation: 4424

How to display a 2D array recordset from Access in Excel?

How do I display the result of my SQL request?

In Access the SQL Query shows:
enter image description here

My VBA code just displays the sectors name. How can I display both Sector and Count result?

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 display without a loop?

Upvotes: 0

Views: 62

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

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.

Upvotes: 1

Related Questions