Fast export Access table to Excel

[JohnPapa]

I have an Access table which has anywhere from 20000 to 80000 records. The number of table fields is variable ranging from 9 to 20. Exporting the table records one by one takes time (several minutes).

Is there a more time-efficient way of exporting the records.

In the specific case where the table has 20000 records with 9 fields, I can have the Excel spreadsheet prepared in advance with the specific field sequence that appears in the table.

Thanks,
John

 

[JANR]

If you just want to dump a table or a query into excel, just click/mark the table/query and select export form the menu and the wizzard will guide you through it.

JR

 

[JohnPapa]

JR,

We are talking about a commercial product where the user needs to export with a click of a button.

John

 

[JANR]

Then try one of Bob's export to excel http://www.btabdevelopment.com/ts/default.aspx?PageId=10 functions it does the same but in code.

JR

 

[JohnPapa]

JR, thanks for your reply,

Do you know which is the fastest way (timewise) to export?
John

 

[JANR]

No you have to test it, but the computers today are fast so just a recorddump should be pretty fast regardless of the methode.

What could take time is formatting the excel sheet if you want to do something fancy, or if you manipulate/evaluate the recordset prior to export.

JR

 

[JANR]

Just did a quick test using the code solution and it took less than a second to pull 25000 records over LAN from the time I clicked the button to excel was up and running with all the records.

JR

 

[JohnPapa]

Thanks JR,

There is no need for formatting the Excel, since this will be preformatted.

I will trya bit later when I am in front of the pc.

Did you use "SendTQ2Excel" for your test?

Thanks John

 

[JANR]

Yes I used that function.

JR

 

[JohnPapa]

Thanks JR,
I just tried it. It turned more than 2 minutes of run time into less than 1 second!!
Good job.
Many thanks.
John

 

[JohnPapa]

To post reply,

The method I was looking for was CopyFromRecordset

objResultsSheet.Range("A3").CopyFromRecordset rst

where rst = Recordset (and could be RecordsetClone)