Put SQL output directly into array written in XL VBA, so I can bypass the worksheet

Question

Is it possible to put the output from the SQL query (written in Excel VBA) directly into a comma separate string variable, rather than having to add it to a Worksheet as in the below code and THEN add it to an array? I just need the SQL output (id's) added directly in a comma separate variable and bypass the worksheet. The ID's are all numeric and there can be 50K of them

 Const sqlconnection = "Provider=sqloledb;"
conn.ConnectionString = sqlconnection
conn.Open

    Dim arrData As Variant
    Dim rs As Recordset, output As String
    Dim v As String
        
        v = "SELECT id FROM table"
    
    Set rs = conn.Execute(v)
    With wb.ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
        .Refresh
    End With
    
    arrData = wb.ActiveSheet.Range("A2").CurrentRegion.Columns(1).Value
    Set Dic = CreateObject("Scripting.Dictionary")

        For idx = 2 To UBound(arrData, 1)
            If arrData(idx, 1) <> "" Then
                Dic(arrData(idx, 1)) = ""
            End If
        Next idx
    
    output = Join(Dic.Keys, ",")
    Debug.Print output

Answer 1

There is an easy method GetRows for a ADODB recordset that reads all data from a recordset into a 2-dimensional array (note that even if you read only one column, the result is 2-dimensional). Only thing you need to know is that the first dimension is the field index and the second dimension the row number, which I found always a little bit counterintuitive. Both dimensions are 0-based.

Unfortunately the Join-command doesn't work on 2-dimensional arrays, so you need to create a 1-dimensional array out of it manually. Of course you can also use a dictionary as you do in your code.

v = "SELECT id FROM table"
Set rs = conn.Execute(v)
' Read all the data into 2-dimensional array
Dim mydata
mydata = rs.GetRows
' Create a 1-dimensional array 
Dim i As Long
ReDim a(LBound(mydata, 2) To UBound(mydata, 2))
For i = LBound(mydata, 2) To UBound(mydata, 2)
    a(i) = mydata(0, i)
Next

Dim output As String
output = Join(a, ",")
Debug.Print output