Fastest way to dump Recordset results onto a spreadsheet

[MartinF]

Hi all,

Please can anyone tell me the fastest and most efficient way to dump the entire contents of a recordset (in this case anything from 700 to 60000 rows) into a specified worksheet?

The way i am doing it thus far is:
1. query the db to obtain records
2. loop through each field of each row in the recordset and place the value in the correspnding worksheet cell.
3. re-query for the next set of data
2. loop through as before and place the data onto the next sheet etc.

The problem i am having is that the first recordset loops through nice and fast while inserting the data, however subsequent recordsets loop through painfully slow (like one record per second!) Does anyone know why this is? between each query i disconnect the database and close the recordset.

Thanks

 

[pjm]

You could try:

dim mySheet as Excel.Worksheet 
dim rst as dao.recordset
set rst = ...
set mySheet = ...
mysheet.cells (2,1).copyfromrecordset rst

Should be much faster than cell-by-cell assignment.

pjm

 

[SkipVought]

Hi,

With just a few mouse-clicks, it is possible return a recordet to Excel, using MS Query, via Data > Import External Data > New Database Query...

I regularly get 'drive by' questions, that can simply be answered with a query, in 2-3 minutes.

Is it the most efficient? Probably not. The more complex the question, might mean that ADO would be a better tool, as pjm suggested above.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MartinF]

PJM's solution worked a treat..thanks a lot that was excatly what i wanted.