Not sure why your Transpose worked.

This example is easily tested by adding the path and name to Northwind.mdb or NWind.mdb as I named it. Add a ListBox1 to a UserForm. Uncomment the 2nd sSQL to view all of the Orders records if you don't want to view just the 1 record.

I would just iterate the whole recordset and build the array. In this example, you can change the path and name to point to the NorthWind database to test. I included the fieldnames as Index 0 for the listbox.

[VBA]Option Explicit
'Global constants required
Const glob_sdbPath = "u:\Material\ADO\NWind.mdb"
Const glob_sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & glob_sdbPath & ";"

Private Sub UserForm_Initialize()
'Requires Reference to Microsoft ActiveX Data Objects 2.8 Library
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rcArray() As Variant
Dim sSQL As String
Dim row As Long, col As Long

'Set the location of your database, the connection string and the SQL query
sSQL = "SELECT * FROM Orders "
sSQL = sSQL & "WHERE OrderID = 10272"

'Open connection to the database
Set cnt = New ADODB.Connection
cnt.Open glob_sConnect

'Open recordset
Set rst = New ADODB.Recordset
' Record locking
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open sSQL, cnt

With rst
'Fill Array
.MoveFirst
row = .RecordCount
col = .Fields.Count - 1
If row < 1 Then GoTo endnow
ReDim rcArray(0 To row, 0 To col)
'Write the field names
For col = 0 To .Fields.Count - 1
rcArray(0, col) = .Fields(col).Name
Next col
.MoveFirst
'Write the recordset
For row = 0 To .RecordCount
For col = 0 To .Fields.Count - 1
rcArray(row + 1, col) = .Fields(col).Value
Next col
If row = rst.RecordCount - 1 Then
Exit For
Else: .MoveNext
End If
Next row
End With

'Place data in the listbox
With ListBox1
'.Clear
.ColumnCount = 3
.List = rcArray
.ListIndex = 1
End With

'Close ADO objects
endnow:
rst.Close
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub
[/VBA]