loading 5 milllion rows

  • Thread starter Thread starter leo
  • Start date Start date
L

leo

Hi all,

Is there a way I can load 5 million rows into one table in
an access database.

Thanks,
Leo
 
I'm not sure if access has a limit to the amount of rows
in the database, but I don't think it can handle
efficiently that amount of data. Try SQL Server.
 
Access has no explicit number of rows, but the file size for Access 2000+ is
2GB.

How big are the rows?

I'm not sure Access would handle an import of that size all at once. It may
need to be broken into chunks, with a compact and repair between chunks.

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001
 
The rows are on average 400 bytes long, mostly
alphanumeric datatype and there are 84 fields.

Thanks,
Leo
 
What is a compact and repair? Could you please explain how
to do it. Are you talking about the access file or the
input file?
 
Ivan said:
I'm not sure if access has a limit to the amount of rows
in the database, but I don't think it can handle
efficiently that amount of data.
Click to expand...

With proper indexing it should be fine. Now if the poster mentioned
100 users adding 1000 records per day each I'd be concerned.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
leo said:
Is there a way I can load 5 million rows into one table in
an access database.
Click to expand...

Another tip is to remove all indexes before starting to load so many
records. And then add them back afterwards.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
5000000 * 400 = 2000000000 which is pretty darn close to 2GB. Then there is
additional overhead associated with the database itself, the table, and each
row and column in the table. And if that original 400 bytes is not currently
stored as Unicode, it will be become 800 bytes when stored as Unicode by
Access - though Unicode compression may reduce that somewhat.

In short, while I can't say for certain that it is not *possible* to do this
in Access, in my opinion it is not *practical* to do this in Access.
 
Back
Top