ADODB transpose without array?

[Jaymond Flurrie]

When I pull out data as an ADODB recordset, it's transposed to have fields in rows and records as columns. When we speak about small amount of data (like dozens times dozens), there's no problem, just use something like

    'Check to make sure we received data
    If Not rsData.EOF Then
        vFromMySQL = WorksheetFunction.Transpose(rsData.GetRows(rsData.RecordCount))
    End If

where the key naturally is WorksheetFunction.Transpose.

But since I have 50 x 100 000 = 5 million elements, WorksheetFunction.Transpose falls short.

Plain and simply: What can I do?

 

[Jaymond Flurrie]

The best solution I've found so far is plain and simply to copy the array element by element to another array by swapping the rows and columns. Fast? Not really. Works? Yes.

 

[Jaymond Flurrie]

The best solution I've found so far is plain and simply to copy the array element by element to another array by swapping the rows and columns. Fast? Not really. Works? Yes.

Correction. Does not work.

 

[Jaymond Flurrie]

I wonder if I'll make up my mind tonight. It does work, here:

    'Check to make sure we received data
    If Not rsData.EOF Then
        vFromMySQL = rsData.GetRows(rsData.RecordCount)
    End If
    
    ReDim vTransposed(LBound(vFromMySQL, 2) To UBound(vFromMySQL, 2))
    
    For L = LBound(vFromMySQL, 2) To UBound(vFromMySQL, 2)
        vTransposed(L) = vFromMySQL(0, L)
    Next L

 

[RoryA]

If you need it as an array, why do you need to transpose it?

 

[Jaymond Flurrie]

If you need it as an array, why do you need to transpose it?

Good question. It's just that everytime I've seen this used before it has been transposed.

Got to love these "What in the world are you doing!?" replies - Especially when you have a great point!

 

[RoryA]

It's usually transposed because it's going to end up on a worksheet. Most of the time CopyFromRecordset is easier for that.