Help with copyfromrecordset please

[Mightyginger]

I have some code (using ADO) which queries a database and then dumps the results to a range. Fair enough, usual story. Is there an easy way instead to declare an variant and set the result from the database to that variant.

Existing code
Set sqlRs = New ADODB.Recordset
sqlCmd.CommandText = &quot;SELECT bf.ISIN,ts.Value FROM TSeriesLookup tsl,TSeries ts,BondDefs bf WHERE ts.Id=tsl.Id AND tsl.NWCode=bf.BondCode AND ts.TradeDate='&quot; & strMonth & &quot;' AND (tsl.NWCode LIKE 'G_D_EUR%' OR tsl.NWCode LIKE 'G_D_GRD%') AND tsl.ValueType='MTedSpread' AND bf.ISIN <> '' ORDER BY bf.ISIN&quot;
Set sqlRs = sqlCmd.Execute
Sheets(&quot;Output_Data&quot.Cells(6, 2).CopyFromRecordset sqlRs

Could I have something like this e.g.
dim output_data_array() as variant
output_data_array.CopyFromRecordset sqlRs

this doesn't work though. Any idea guys?

Thanks for your help once again by the way!


Neil.

 

[pvw]

Hi Neil

CopyFromRecordset is a method of the range object. It's not made for reading records into an array. But you can use 'GetRows' instead:
Dim output_data_array As Variant
output_data_array = sqlRs.GetRows(sqlRs.RecordCount)

With this, you'll get a two dimensional array.

HTH
Philipp

 

[Mightyginger]

Thanks very much Philipp. I have one more question if I may,

this then returns an array to a variant object, say (3,256), where I have Name, Price, Last, Vol and 256 entries for each. I would normally use UBound on an array to get the max num of records but because it's variant UBound(output_data_array,0) doesn't return 256. Is there a command I can use to get the max no of records returned please?


Many thanks,


Neil.

 

[pvw]

Hi Neil

You can still use UBound. The number of records is stored in the second dimension, so you have to write
UBound(output_data_array, 2)

When you've got 256 records, 'UBound(output_data_array, 2)' returns 255 because the array is zero-based (by default). Add the statement
Option Base 1
to your code to use one-based arrays.

HTH
Philipp