Record limit CSV import and more - Access 2002 SP3

[mettá]

I am trying to work with a table that contains around 3 million records.

I import the data from a CSV file with approx 1 million records consisting
of two fields (a number and text) comma separated, I cannot view the CSV in
full? Max 65000 view in Excel so do not know if there are errors in the CSV
(Any suggestions welcome) When I import them into a table -

ID (Auto number)
Number (long integer import)
Text (125 character limit import)
Date (Auto generated)

The first time I imported with no problems, when I tried to import another
batch all hell broke loose -

Import errors and table grew by over 3.5 million when I tried to import
850,000 records!!

Am I asking too much of Access? Version 2002 (XP)

System is Win XP 32 bit with 3 gig ram and 4 core processor.

Thanks
M



--

 

[a a r o n _ k e m p f]

If you're using this much data; then you should be usign a real ETL
tool (like SSIS or Informatica).

jet doesn't work with this many records.


if it's truly a problem with a 2gb limit; then you shoudl move to SQL
Server, the free version has a _4gb_ limit (per database-- it's really
easy to make a new database in SQL)
Also, with SQL Server, you would be able to run these imports a lot
faster-- for example BCP / Bulk Insert would work 10 times faster than
anything that Jet can import, guaranteed.

Furthermore, you could use a real ETL tool like SSIS, and you could
'do stuff' with rows that fail.
Append them to a log file, or something; you know?

-Aaron