Problem Exporting over 500000 records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

x-rays - 24 Jul 2006 09:36 GMT

Hello All,

I'm using Access 2003 but my database has a 2000 format. When I have a
serious amount of records to export, a problem occurs and Access
crashes. This happens when I got more than 500000 records to export.
The data exported in another Access db or csv files.

Can anyone help me with that? What is the problem exactly?

Thank you in advance!

Reply to this Message

pietlinden@hotmail.com - 24 Jul 2006 10:18 GMT

> Hello All,
>
[quoted text clipped - 6 lines]
>
> Thank you in advance!

do you get an error message? do you run out of RAM? C'mon, help us
out here... Or would you like a turn at playing pin the tail on the
donkey?

Reply to this Message

pietlinden@hotmail.com - 24 Jul 2006 10:20 GMT

oh, and how about sharing your code? How are you doing the export?
what exactly are you exporting? recordsets? To where? are you
cleaning up after yourself? how much RAM do you have? what version of
Access?

How about you give us the steps you followed to get the error so one of
us could possibly recreate the problem?

Reply to this Message

x-rays - 24 Jul 2006 11:18 GMT

hmmm, ok then,

I got 1GB RAM and 5GB free disk space in my machine (which is enough),
I just wrote and saved a select query and use it through macro to
transfer the data it produces to an excel spreadsheet. I mention the
version of Access I use in my 1st post but to be more precise the
version I use is (11.6566.6568) SP2.

Hope this helps!

> oh, and how about sharing your code? How are you doing the export?
> what exactly are you exporting? recordsets? To where? are you
[quoted text clipped - 3 lines]
> How about you give us the steps you followed to get the error so one of
> us could possibly recreate the problem?

Reply to this Message

John Spencer - 24 Jul 2006 13:28 GMT

Excel will not support that many rows.

The maximum number of rows for an Excel (2003) spreadsheet is 65,536 rows.
The maximum number of columns for an Excel spreadsheet is 256.

> hmmm, ok then,
>
[quoted text clipped - 13 lines]
>> How about you give us the steps you followed to get the error so one of
>> us could possibly recreate the problem?

Reply to this Message

x-rays - 24 Jul 2006 13:40 GMT

Hello John,

Thank you for your response, nop, excel supports more than 65536 rows
but you just can't view the rest of them. Try to export more than 65536
rows/records and import them back, you won't miss a thing.

> Excel will not support that many rows.
>
[quoted text clipped - 18 lines]
> >> How about you give us the steps you followed to get the error so one of
> >> us could possibly recreate the problem?

Reply to this Message

Jerry Whittle - 24 Jul 2006 16:19 GMT

I just exported 700,000+ rows from Access 2003 (A2K format) to an Excel 2003
spreadsheet. The new spreadsheet had 65536 records in it. Imported the new
spreadsheet back into Access and had only 65535 records in a new table. The
difference of one record was the column headings.

Seems John is right.

Still that doesn't explain your original problem as I was able to export at
least some of the 700,000 records to Excel with out an error message.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hello John,
>
[quoted text clipped - 24 lines]
> > >> How about you give us the steps you followed to get the error so one of
> > >> us could possibly recreate the problem?

Reply to this Message

x-rays - 24 Jul 2006 17:05 GMT

You are right!

I have some dozen of exports here... as I wrote in my 1st post, my
problem is when I export delimited csv files.

Sorry about the mess.

> I just exported 700,000+ rows from Access 2003 (A2K format) to an Excel 2003
> spreadsheet. The new spreadsheet had 65536 records in it. Imported the new
[quoted text clipped - 37 lines]
> > > >> How about you give us the steps you followed to get the error so one of
> > > >> us could possibly recreate the problem?

Reply to this Message