Hi there

I'm pulling data from a 3GB Access database using Excel VBA and ADO. I'm retrieving 250 fields for 100,000 records using a single active connection, and I'm further using the .Open Method (of the Recordset object), the .Execute Method (of the Connection object) and the CopyFromRecordset Method. I do this iteratively, i.e.

For i = 1 to 107288
rsData.Open strSQL, cnData, adOpenForwardOnly, adLockReadOnly, adCmdText
Worksheets(i).Cells(1, 1).CopyFromRecordset rsData
Next i

Doing things this way takes about 38 hours to pull all the data into an Excel workbook!

Is there a more efficient way to pull data from Access into Excel?

Best regards
Loane

Never heard of anyone using 107288 sheets in a workbook. If you can do with a single sheet, try this:

    rsData.Open strSQL, cnData, adOpenForwardOnly, adLockReadOnly, adCmdText

    If Not rsData.EOF Then
       Sheets("Sheet1").Cells(1, 1).CopyFromRecordset rsData
    End If

    rsData.Close
    Set rsData = Nothing

Hi
Just for your information,Excel(2000) only allow 65636 rows in one sheet so to use the method suggested you will ned to add som logic that will change to another sheet when you reach the limit.
Cheers
Karsten

Hi there, thanks for the suggestions.

I've added timers to my procedure and find approximately the following: 50% of the run time is spent running through my code (especially If ... End If, For ... Next and Do While ... Loop statements); 25% for the [Recordset].Open operation; and 25% for the [Range].CopyFromRecordSet operation.

Even taking out the code altogether, leaving only the ADO references, still involves a heck of a lot of time.

P.S. To overcome the 2^16 problem I'm using ...
For i = 1 to 107288
 If i <= 2^16 Then
 shtName = "Sheet1"
 Else:
 shtName="Sheet2"
 End If

[other stuff here]

Next i

Thanks again

It's just that in seems to me you're doing a lot of stuff within your loop that takes time.

Would it be possible to split your query in 2, e.g. run a Q on half of your data being sure it can fit into one sheet and doing it once more for the rest of the data for a second sheet ?

Loane,

I gotta ask - what on earth are you trying to do here anyway? I'm thinking that if this is your problem, you're possibly trying to solve the wrong thing. What are you intending to do with all this data once you've got it?

Chris

There are two secrets to success in this world:
1. Never tell everything you know