Dear Access gurus,

I am retrieving data from Access into Excel through ADO. It is quite
slow. The data are retrieved from an Access queries. Will it be much
faster to retrieve data directly from tables instead of queries? I
should think table will be faster but I am not sure how much faster.
As it entails quite a bit of work on the Excel side to retrieve
directly from tables, I would like to ask the experts here whether the
increased speed is just a little or much more or not much at all(not
noticeable).

Thank you very much.

On Jan 10, 6:48*pm, Lane <lightai... (AT) gmail (DOT) com> wrote:
Do you need to filter?

Is the query blindingly fast within Access?

Lane wrote:
the query returns a subset of the fields in the table, in which case you
might notice a difference with the query.

The best method of all would be to initiate the export from within Access,
using TransferDatabase.
If that is not possible:

If the query returns data quickly when run in Access, then the slowness you
are experiencing is likely due to the method you are using to write the data
into Excel. If you are looping through a recordset, then this will be very
slow. To maximize efficiency, you need to get the recordset out of the
equation as soon as possible. To maximize efficiency:
1. Make sure you set the recordset's cursorlocation to adUseClient to make
sure you are using a static, clientside cursor.
2. Make sure you open it for read-only
3. After the cursor (recordset) is opened, if EOF is false (the recordset is
not empty), use the recordset's GetRows method to quickly dump the data into
an array. Then close the recordset and the database connection
4. Use the IsArray method to verify that records were put into the array
variable and, if so, loop through the array (much faster than a recordset
loop) and write the data into your spreadsheet.

Alternatively, use DAO instead of ADO to connect to the database and
retrieve the data into a DAO recordset. This allows you to use the Excel
Range object's CopyFromRecordset method to directly write the data from the
recordset into the worksheet range. This _will not work_ with an ADO
recordset if using a version of Excel earlier than 2000.!

http://www.exceltip.com/st/Import_da...Excel/428.html

http://support.microsoft.com/kb/246335

"Lane" wrote in message
news:085c272a-dcf9-4bc0-89c7-5736b4ef2b0f (AT) ck5g2000vbb (DOT) googlegroups.com...

You should be able to pull 30,000 to about 100,000 records with ease in less
then one second.

And no, the speed difference of using that query vs that of a table will be
nil in most cases
(if the query is not sorting, doing joins, group-by etc, then just opening a
table or a query that is based on that table should not make a difference).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com