Capacity of Access

[Guest]

The database I created receives about 50 new records daily. What is the
capacity of Access? How many records, or to what MB is Access capable of
reaching without significantly reducing performance or crashing? Also, the
correct step when the database reaches too large a size is to upgrade to SQL
right? Is this easy and again when should it be considered?

 

[Joseph Meehan]

The database I created receives about 50 new records daily. What is
the capacity of Access? How many records, or to what MB is Access
capable of reaching without significantly reducing performance or
crashing? Also, the correct step when the database reaches too large
a size is to upgrade to SQL right? Is this easy and again when
should it be considered?

Current versions have a 2 gig max, older version (97) have a 1 gig max.
There is no specific record number. At 50 records a day, most databases
will far outlast both of us.

 

[Albert D. Kallal]

It really depends on how large the current database is already, how many
users will access the data, and also how well the application is designed.

If things are setup correctly, a table of 100,000 records is rather small,
and ms-access will not even break out in a sweat.

So, 100,000 / 50 = 2000 days.

At 365 days a year, you get 5 years to hit 100,000 records, and 10 years to
hit 200,000 records. And, if you are not entering 50 records every day of
the month..then to hit 200,000 records will take considerably longer.

Depending on your setup, often ms-access is actually FASTER then sql server.
And, MOST of the time the reason to upgrade to sql server is for reasons of
security, reliability, number of concurrent users, and increased flexibility
in the kinds of connections you can make to the data. However, performance
is often NOT the reason you upgrade!

There is weekly posts in the sql newsgroups about how an access application
was upgraded to sql server, and it slowed down. So, sql server all by it
self will RARELY fix poor performance problems due to poor designs.

With proper designs, files of 200,000 records are really nothing for
ms-access.

 

[John Vinson]

The database I created receives about 50 new records daily. What is the
capacity of Access? How many records, or to what MB is Access capable of
reaching without significantly reducing performance or crashing? Also, the
correct step when the database reaches too large a size is to upgrade to SQL
right? Is this easy and again when should it be considered?

The largest Access database I'm aware of has 22,000,000 records in the
largest table; I'm sure there are bigger ones. You're limited to 2
billion bytes in any individual .mdb file (and a database application
can use several .mdb files if necessary).

I don't think you're in any danger yet, at least not for the next 1205
years at your current rate of addition.

John W. Vinson[MVP]