Access 2003 with over 1 million records on a single file

[IT Staff]

I don't use the above, but this is from a end-user question.

1. they try to load the records into excel, but fails.

Is there any tools or utilities to break the single access file *.mdb into
multiple mdb files so that records will be fewer in each new mdb files to
allow loading into excel ?

p/s : we are using office 2003

 

[John W. Vinson]

I don't use the above, but this is from a end-user question.

1. they try to load the records into excel, but fails.

Is there any tools or utilities to break the single access file *.mdb into
multiple mdb files so that records will be fewer in each new mdb files to
allow loading into excel ?

p/s : we are using office 2003

An Access *DATABASE* does not contain "records". It contains tables (which
contain records); so the direct answer to your question is No.

What you can do, however, is create Queries based on the table that you're
trying to export. These queries can use criteria based on fields in the table
to select subsets of the table to export, or can use the Top Values property
of the query to select <65536 records (the limit for exporting). If they're
exporting into Excel2003, however, I'm pretty sure there is still a 65536 row
limit *in the spreadsheet* so a million rows won't fit. I realize that the
limit has been lifted in 2007 but I'm not sure how you could do the export in
one shot.

 

[Tom van Stiphout]

On Wed, 22 Oct 2008 19:50:06 -0600, John W. Vinson

It would also be interesting to know what the user wants to do with 1M
(or whatever subset) records in Excel. If she wants to make some
further calculations it is good to know Access can do most things
Excel can do.

-Tom.
Microsoft Access MVP

 

[boblarson]

John:

For Excel 2007 / Access 2007 You can export using the Excel object model and
use the CopyFromRecordset object to quickly place hundreds of thousands of
records (I've done up to 300,000 before without a problem). You might be
able to export a million records that way as well. It would take a little
bit longer than the normal CopyFromRecordset that most of us are used to but
hey isn't that to be expected?
--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[David W. Fenton]

For Excel 2007 / Access 2007 You can export using the Excel object
model and use the CopyFromRecordset object to quickly place
hundreds of thousands of records (I've done up to 300,000 before
without a problem). You might be able to export a million records
that way as well. It would take a little bit longer than the
normal CopyFromRecordset that most of us are used to but hey isn't
that to be expected?

Er, what?

I use TransferSpreadsheet when I need to output Access data for
Excel.

 

[boblarson]

David:

Our initial tests with Access 2007 and Excel 2007 is that
TransferSpreadsheet doesn't deal with the large, large datasets (over 100,000
records). If you haven't had that problem, can you verify that?

--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[David W. Fenton]

Our initial tests with Access 2007 and Excel 2007 is that
TransferSpreadsheet doesn't deal with the large, large datasets
(over 100,000 records). If you haven't had that problem, can you
verify that?

Well, I don't have Access or Excel 2007. I would assume that
TransferSpreadsheet in Access 2007 would have been extended to allow
for exporting more rows than the old 8-bit limit by choosing the
appropriate value for your Spreadsheet Type parameter. In A2K3, the
highest version is indicated by the built-in constant
acSpreadsheetTypeExcel9 (which would be Excel 2000, the last time
the file format was changed), so I'd assume there's a new
acSpreadsheetTypeExcel12 constant, for Excel 2000 format.

Isn't there?

 

[Pete D.]

acSpreadsheetTypeExcel12
acSpreadsheetTypeExcel12Xml