fetching data with ADO

[markelly]

Hi All,

I am running VBA code which connects to Oracle database. Then the SQL query is executed.
And then I am trying to get the data to Excel, using the below piece of code.

fldCount = rsOra.Fields.Count
For iCol = 1 To fldCount
Worksheets("Sheet1").Cells(1, iCol).Value = rsOra.Fields(iCol - 1).Name
Next

Worksheets("Sheet1").Cells(2, 1).CopyFromRecordset rsOra

It works fine to get the column headings but then it falls over (it's running for a few minuts then 'not responding) on line:
Worksheets("Sheet1").Cells(2, 1).CopyFromRecordset rsOra

I need to close and restart excel application. There's about 4000 records to be copied from the database. I wouldn't think this is a lot.
Is there any other way to fetch the data using ADO and recordset?

Thanks a lot.

 

[DonkeyOte]

try forcing Excel into Manual Calculation prior to running your retrieval... the recordset will load in cell by cell so will recalc each time if set to auto which will slow things down...

Sub ...
Application.Calculation = xlCalculationManual

...your code...

Application.Calculation = xlCalculationAutomatic 
End Sub...

 

[Richard Schollar]

Hi

The way you've got it is the way I would do it. I'm not sure why the CopyFromRecordset would fail. Maybe try the following alternative (I wouldn't want to use this instead of CopyFromRecordset, but it that isn't working...):

Dim varArray As Variant
 
 
'...
varArray = rsOra.GetRows()
 
Worksheets("Sheet1").Cells(2, 1).Resize(UBound(varArray,1),UBound(varArray,2))

This may well fall over again. If so, you can loop thru the recordset writing each record individually, but that isn't going to be fast...

 

[markelly]

Thanks for your quick response.

lasw10,
i tried what you have suggested, unfortunately it hasn't helped.

Richard,
Thanks for your idea, could you tell me what's missing in line:

Worksheets("Sheet1").Cells(2, 1).Resize(UBound(varArray,1),UBound(varArray,2))

As im getting compilation error

 

[Richard Schollar]

Sorry, missed out the crucial bit:

Worksheets("Sheet1").Cells(2, 1).Resize(UBound(varArray,1),UBound(varArray,2)) = varArray

 

[markelly]

Thanks again,

it runs for a long time on the below line:
varArray = rsOra.GetRows()

but finally moves to line:

Worksheets("Sheet1").Cells(2, 1).Resize(UBound(varArray, 1), UBound(varArray, 2)) = varArray

and then i get an run-time error '1004'
Would you have any suggestions on the cause of the error?

Also I have been thinking of loading the data to Access first and then to Excel, do you think it's a good idea?

Thanks

 

[Richard Schollar]

I like the idea of using Access as an intermediate - assuming of course you can get the data into Access!

It's possible that the lower bound of GetRows isn't 1 based or varArray may not hold an array so try the following in place of that line:

If IsArray(varArray) Then 
  Worksheets("Sheet1").Cells(2, 1).Resize(UBound(varArray, 2)-LBound(varArray,2)+1, UBound(varArray, 1)-LBound(varArray,1)+1).Value = Application.Transpose(varArray)
Else
  Msgbox "varArray doesn't hold an array!"
End If

I have used Transpose too as I believe the variant returned is different compared to the one returned by assigning a range to a variant.

 

[markelly]

Thanks a lot for all your help.

Was trying to run it - excel crashed againg. Will try to do it using Access, see how it goes.