Too slow to save a query to excel sheet

[awu]

All:
I have a query that has 5 fields and around 15500 records. I need to
save it to a excel file that has two sheets. First sheet saves all the
15500 records. Second sheet saves around 7500 records based on 5th
field contents. So there is comparison in the loop. I am using VBA to
execute this operation. The problem I have is the operation is too
slow. It takes more than 5 minutes.

Any other suggestion to save a query to a excel file in a faster
manner?
Thank you
awu10

[pietlinden]

awu wrote:

All:
I have a query that has 5 fields and around 15500 records. I need to
save it to a excel file that has two sheets. First sheet saves all the
15500 records. Second sheet saves around 7500 records based on 5th
field contents. So there is comparison in the loop. I am using VBA to
execute this operation. The problem I have is the operation is too
slow. It takes more than 5 minutes.

Any other suggestion to save a query to a excel file in a faster
manner?
Thank you
awu10

Post the code you're using. If at all possible, use pure SQL to return
the dataset you want to export. Set-at-a-time operations are MUCH
faster than row-at-a-time ones. Sounds like you should create another
query for the second dataset. You can base the second query on the
first and then just use it to do the export.

[Tom van Stiphout]

On 8 Nov 2006 07:06:45 -0800, "awu" <aw***@hotmail.comwrote:

DoCmd.TransferSpreadsheet cannot be beaten.
-Tom.

>All:
I have a query that has 5 fields and around 15500 records. I need to
save it to a excel file that has two sheets. First sheet saves all the
15500 records. Second sheet saves around 7500 records based on 5th
field contents. So there is comparison in the loop. I am using VBA to
execute this operation. The problem I have is the operation is too
slow. It takes more than 5 minutes.

Any other suggestion to save a query to a excel file in a faster
manner?
Thank you
awu10