Hi,
(Env: Excel O’2003, DAO 3.6)
I’m running a query to a DB and got a functioning SQL string that returns a recordset which is a two dimensional array by say 10 cols by X rows
What is the best way to put this into an array?
Public Function SQL_QueryForArray(ByRef SQL_QueryForArray As String) as Variant
sSQL = SQL_QueryForArray
Set qy = con.CreateQueryDef(“”, sSQL)
Set rs = qy.OpenRecordset(dbOpenForwardOnly, 0, dbReadOnly)
If rs.EOF Then
MsgBox = “Not available in the database”
Else
‘Problem is here – how do I load the recordset into an array??
SQL_QueryForArray = rs.Fields().Value
End If
Set rs = Nothing
Set qy = Nothing
End Function
Will I have to loop the recordset and fill it into the array, one row at a time – or can it be done in a smarter way??