How much data can access database store?

[Guest]

Can anyone please tell me how much data i can store in an access table?

thank you so much.

Regards,
Aalok

 

[John Vinson]

Can anyone please tell me how much data i can store in an access table?

There is no explicit record count limit; you're limited to 2 GByte in
any single .mdb file (and an application can consist of multiple
linked .mdb files, though a single table must be stored in one file).

In practice, anything over 10,000,000 records in the largest table is
probably getting a bit big for Access, though I know of databases in
productive use with tables twice that size.

John W. Vinson[MVP]

 

[Albert D.Kallal]

How tall, how big etc. These are good questions in a sense.

Ms-access (2000 and later) has a maximum file size of 2 gigs.

Thus, the limitation, or max number of records is going to depend on the
SIZE of each record. (there is no record limit, but only the number of
records you can FIT in the max file size).

For example, lets assume we have a typical mailing list. (name + address).
On average, you might find about 120 characters per name.

So, the number of records in this case would be

2 gig / 123 characters per records =

2,000,000,000 / 123 = 16 million names

In actual practice, and throw in a few indexes etc, you might max out around
10 million names. On the other hand, some mailings lists only average about
80 characters per name..and thus you would hit 25 million records.

So, if your tables are small, say only 75,000 to 150,000 recodes, you likely
don't have much to worry about. If you expect record numbers in the
millions, then I would certainly do some planning, and make some rough
calculations as to if ms-access is the right place to store that much data.

My personal view is that when you hit 1/2 the max file size, it is time to
either archive some data, or use a different data store.