PDA

View Full Version : ADODB.Connection Copy RecordSet to Array



mferrisi
06-06-2007, 06:48 AM
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,


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=Mi crosoft 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

Bob Phillips
06-06-2007, 09:58 AM
myArray = rst.GetRows

unmarkedhelicopter
06-06-2007, 10:18 AM
myArray = rst.GetRows

Note that myArray HAS to be declared as variant

mferrisi
06-06-2007, 11:29 AM
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
06-06-2007, 11:37 AM
You need to identify what is gobbling up the memoery, and release that.

mferrisi
06-06-2007, 11:44 AM
How do I make that identification? What if it is the recordset? how would I flush that?

Thanks

Bob Phillips
06-06-2007, 12:30 PM
Release objects, clear arrays, when done with. It is a tedious, slow process.

mferrisi
06-06-2007, 01:18 PM
what would the code look like for releasing the objects?

Bob Phillips
06-06-2007, 02:02 PM
Set ADOConn = Nothing
Set rst = Nothing
'etc.