Exporting Excel to Access

[jsudo]

I have a Access database with a large amount of records (close to
500,000) that I would like to export to Excel. I found out that Excel
has the capability of of about 65,000 rows so I know I cannot export
the whole file at once. What I would like to do is divide the
database records into smaller groups that Excel can handle.

Does anyone know how I can export a group of records to Excel without
getting an error? I tried exporting 60,000 lines to Excel which would
be the ideal amount, but an error message popped up. I cut the amount
to 40,000, but the same thing happened. Is there a maximum limit to
the number of lines I can export? Any help would be very much
appreciated!

Thank you,
Janet

[Pieter Linden]

Re: Exporting Excel to Access

Umm... what error did you get? What code did you use to export the
data? Does a line in your code get highlighted? If so, which one?

[Janet Sudo]

Re: Exporting Excel to Access

Thanks for your response. I'm not too familiar with using Microsoft
Access, so I'm not sure what you mean by "code."

Here's what I did:
I opened a *.csv file with 466,000 rows in Access. When the file was
opened in Access, I clicked on File-> Export-> to the file location to
be saved (as an Excel file). I then got the message, "You selected more
records than can be copied onto the Clipboard at one time." Then
another message popped up, "Divide the records into two or more groups,
and then copy and paste one group at a time. The maximum number of
records you can paste at one time is approximately 65,000." Another
message, "There are too any rows to output, based on the limitation
specified by the output format or by Microsft Access."

Then, I typed in record 65,000 to take me to that record and highlighted
all the records preceding it and clicked on File-> Export, as *.xls.
Next I clicked on "save formatted," and then "save selection" from the
drop down box on the right. The same message popped up: "There are too
many rows to output...."

I tried fewer rows each time, to see what the limit was. The maximum
limit seems to be somewhere between 16250 and 16500 records.

I'm not sure why it's not allowing me to copy over the 65,000 rows that
it claimed to.

I hope what I typed above makes sense. Any help would be appreciated.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Peter Russell]

Re: Exporting Excel to Access

Janet Sudo previously wrote:
[color=blue]
> I tried fewer rows each time, to see what the limit was. The maximum
> limit seems to be somewhere between 16250 and 16500 records.
>
> I'm not sure why it's not allowing me to copy over the 65,000 rows that
> it claimed to.[/color]

Because the default format for the export that you are running is Excel 5
format which had a limit of 16K rows.

To solve your problem you will have to create queries which select
separate groups of 65K records and output them using Transferspreads heet
which allows you to select the Excel format for 97-2000+.

Regards

Peter Russell

[Billpybus]

Re: Exporting Excel to Access

Why do you want to move from a single Access database holding all records to
multiple excel files? A single central repository of the records is easier to
handle rather than multiple files. Is the issue unfamiliarity with Access?

You need to create a number of queries that will select the number of records
that you can import into an Excel spreadsheet. Make sure that each query is
selecting records not selected by other queries, so no duplicates. Can you
identify a unique identifier (one or more columns) for each record?

To export the records, choose the query you want to export and go to
File-Export. It will prompt for the name of the file and the type and then
create that file.

Make sure that you can account for all records and ensure that all records have
been export to the various Excel files.

If unfamiliarity with Access is the issue, consider taking some time to review
Access before discounting it. Use the best tool for the job, not necessarily
the tool you know.

If all you have is a hammer, everything looks like a nail...

Bill

[onedaywhen]

Re: Exporting Excel to Access

Users' familiarity with Excel is usually the issue, in my experience,
closely followed by the cost of licencing the MS Access application to
non-developers.
[color=blue]
> Why do you want to move from a single Access database holding all records to
> multiple excel files? A single central repository of the records is easier to
> handle rather than multiple files. Is the issue unfamiliarity with Access?[/color]

<snip>
[color=blue]
> If unfamiliarity with Access is the issue, consider taking some time to review
> Access before discounting it. Use the best tool for the job, not necessarily
> the tool you know.
>
> If all you have is a hammer, everything looks like a nail...
>
> Bill[/color]