What is the fastest way to pull a recordset using ADO to Oracle.

[Gummy]

Hello,

I am connecting to an Oracle 9i database through ADO. I loop through the
rows and columns in Excel and pass those parameters to a query which returns
a single value and places it in the appropriate cell. This works fine, but
is awful slow and I have lots and lots of cells to fill.

My question is: Instead of pulling a singe value and placing it in the cell,
would it be faster to pull several values (a larger recordset) loop through
that recordset and place the values in the appropriate cell?

I don't know if the Seek method is faster on a recordset of several values.
Maybe it would be faster to drop the recordset into a tempory sheet and loop
through those values? Or is there a completely better way to do this?

Thank you in advance for any suggestions.

-Gummy

 

[Guest]

Try this, rsData is my ADODB connection to a SQL database but I think it
would work the same for your purposes.

..Range("A4").CopyFromRecordset rsData

Then process this, read that recordset back into a variant array (2d) and
process it that way making any changes then repopulate the cells from the
changed array. (MUCH faster then looping through the cells). It might be
possible to use the .MoveNext type commands to loop through the recordset
building the 2d array that you then send to the worksheet.

Dim vaArray as Variant
vaArray = Range("A1:C100").value 'loads a 2d array from the cells
....process array...
Range("A1:C100").value = vaArray 'Replaces the cells with the 2d array data.

HTH