Problem Exporting over 500000 records

[x-rays]

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!

 

[pietlinden]

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!

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?

 

[pietlinden]

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?

 

[x-rays]

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!

 

[John Spencer]

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.

 

[x-rays]

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.

 

[Jerry Whittle]

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.

 

[x-rays]

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.