ADODB.Connection Copy RecordSet to Array

[mferrisi]

I got the following code to work, which copies the recordset to the worksheet. I can't seem to figure out how to copy it instead into an array. Does anyone have any suggestions as to how I could populate an arry instead?

Thank you,


[VBA]Sub Test()
Dim cnMTPS As ADODB.Connection
Dim cmd As ADODB.Command

Set rst = New ADODB.Recordset
Set cnMTPS = New ADODB.Connection
cnMTPS.ConnectionString = "DRIVER=SQL Server;SERVER=SQL000000;UID=;APP=Microsoft Data Access Components;WSID=;Network=0000000;Trusted_Connection="
'cnMTPS.ConnectionString = "Driver=SQLServer;UID=;DSN=MTPS;Trusted_Connection=true;database=MTPS;APP=M icrosoft Data Access Components; SERVER=SQL000000;"
cnMTPS.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnMTPS
cmd.CommandType = adCmdText
cmd.CommandText = "SELECT DISTINCT id As id, rm_date As dt, rm As firm INTO #ID " & _
"FROM MTPS.dbo.rm WHERE (group = 'Firm') AND (category = 'Total') AND (id < 5) " & _
"ORDER BY id, rm_date"
cmd.Execute
'cmd.CommandText = "Select * from #ID"
'Set rst = cmd.Execute
'Sheet1.Range("A2").CopyFromRecordSet

cmd.CommandText = "Select id,dt,AVG(firm) from #ID GROUP by id,dt " & _
"ORDER BY id, dt"
Set rst = cmd.Execute
Sheet1.Range("A2").CopyFromRecordset (rst)
'cmd.CommandText = "DROP TABLE #ID"
'cmd.Execute
cnMTPS.Close
End Sub[/VBA]

[Bob Phillips]

[vba]

myArray = rst.GetRows
[/vba]

[unmarkedhelicopter]

[vba]
myArray = rst.GetRows
[/vba]

Note that myArray HAS to be declared as variant

[mferrisi]

Thanks for the solution. One other problem though with memory management. Unfortunately, I was making an addition to a pre-existing program that was written in a giant loop, and the rest of the program was written with SQL requests. By loop #3, I run out of memory and the program fails. Is there a way to flush the memory at the end of each loop?

[Bob Phillips]

You need to identify what is gobbling up the memoery, and release that.

[mferrisi]

How do I make that identification? What if it is the recordset? how would I flush that?

Thanks

[Bob Phillips]

Release objects, clear arrays, when done with. It is a tedious, slow process.

[mferrisi]

what would the code look like for releasing the objects?

[Bob Phillips]

[vba]

Set ADOConn = Nothing
Set rst = Nothing
'etc.
[/vba]