Hi Jamie

Thanks for your suggestions.

I pulled the data into an Excel workbook this weekend ... As a reminder, I'm
working with a Human Resources database comprising 3 Access database files
each measuring about 1GB, with 23 tables in total, about 250 fields and
107288 records (each record relates to an employee, either work history,
academic history, on-the-job performance, static information, etc. spread
across the 3 database files).

The Excel file (incorporating bits and pieces extracted and summarized from
the 3 Access database files) measures about 110MB, so there's a fair amount
of data summarizing going on.

The real pain is that it took 38 hours to pull the data into Excel
record-by-record.

I broke down my quite lengthy code into little pieces, to see where the
inefficiency is ... At first it seemed that I'd found the problem: turns out
I was instantiating a new Connection and Recordset object each time in the
For (i = 1 to 107288) ... Next loop. However, when I instantiate the
connection right at the beginning of the procedure (i.e. so that connections
to each of the 3 databases are established once and thereafter maintained
throughout the procedure), and when I instantiate the recordset object
outside the loop, the procedure doesn't run noticeably faster. It seems that
the "hog" is the [Recordset].Open method, which must be given for each of
the 107288 records.

Do you think the following will be a fruitful line of further enquiry? ...
I'm going to move my procedure to another (empty) Excel workbook, and this
workbook will be the only instance running. I.e. I'm going to SELECT from
the Access databases (Access not running) and INSERT INTO an Excel workbook
(workbook not open).

Best regards
Loane

"Jamie Collins" wrote in message
om...
"Jake Marx" wrote ...

I'm sure there is a more efficient way of doing this, but not knowing
the
data, it's hard to give specifics.

Another option is to use a UNION query to create a single recordset
and do the sort within the query (must use the column's ordinal
position e.g. ORDER BY 1). I haven't tested but I'm pretty sure it
will be more efficient to get the provider to do the sorting. For
example (note only the path of the non-connected database is required;
both are shown for clarity):

SELECT
Field1
FROM
[Database=C:\DataBase1.mdb;].Table1
WHERE
Index = 1
UNION ALL
SELECT
Field1
FROM
[Database=C:\DataBase2.mdb;].Table1
WHERE
Index = 1
ORDER BY
1
;

Jamie.

--