loading 5 milllion rows

[leo]

Hi all,

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

Thanks,
Leo

 

[Ivan]

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.

 

[Kevin3NF]

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

 

[Guest]

I have to do it access and so far only 700k rows can get
in.

Thanks,
Leo

 

[Kevin3NF]

How big are the rows? How many fields, what datatypes, etc.?

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001

 

[Guest]

The rows are on average 400 bytes long, mostly
alphanumeric datatype and there are 84 fields.

Thanks,
Leo

 

[Kevin3NF]

What is the file size after 700K rows? Have you done a compact and repair?

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001

 

[Guest]

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?

 

[Kevin3NF]

Access file. Tools>>Database Utilities>>Compact and repair.

Reclaims space that was used by records that are no longer there.

--
Kevin3NF

Sick of all that junk filling up your mailbox?
http://spamarrest.com/affl?2967001

 

[Tony Toews]

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.

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

 

[Tony Toews]

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

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

 

[Brendan Reynolds \(MVP\)]

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.