3 million simple records in Access 2003 DB??

[am.It]

Hi all,
I have about 3 million records with 3 varchar fields to
uploaded to a database. Can anyone tell me whether an Access 2003 db be
able to handle this huge volume of records?

If yes, what can i do to make it work efficiently ??? One thing I know
is Indexing. Please do tell me what else I can do.

I know that maximum capacity of an Access 2003 db is 2GB. My sample
Access DB with 60 such records occupies 116KB.

It would be very helpful if someone can tell me whether I can go ahead
and upload approx 3 million such records in the DB.

Thanks,
Amith

 

[Douglas J Steele]

Is that 116 KB after compacting?

 

[John Vinson]

Hi all,
I have about 3 million records with 3 varchar fields to
uploaded to a database. Can anyone tell me whether an Access 2003 db be
able to handle this huge volume of records?

Three million records is fairly large, but I know of working
production databases with 20,000,000 records in the largest table.

If yes, what can i do to make it work efficiently ??? One thing I know
is Indexing. Please do tell me what else I can do.

Proper query design is probably the other critical factor. Of course,
the optimum design of both the indexing and the queries depend
entirely on the nature of the data and on what you're trying to
accomplish.

If this is to be a multiuser database, it's essential to "split" the
database so that the data is stored in one (shared) backend database,
and the application portions (forms, queries, reports etc.) in a
separate "frontend"; each user gets a copy of the frontend.

I know that maximum capacity of an Access 2003 db is 2GB. My sample
Access DB with 60 such records occupies 116KB.

Much of that is system overhead. The actual table data will be stored
pretty compactly.

It would be very helpful if someone can tell me whether I can go ahead
and upload approx 3 million such records in the DB.

Back up your database and try it to see. The worst thing that will
happen is that your database won't work, and you'll need to delete it
and restore the backup. Your computer won't explode <g>...


John W. Vinson[MVP]