Limitations of Access?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this is a very generaized question with little chance of a single "right" answer. However, my own experience with Access - specifically, Access with massive amounts of data - is rather limited in this regard

I can guess that there is some theoretical point where an Access table has "too many" rows and the application doesn't function properly anymore (n terms of returning the correct records in queries, etc). Does anyone have any experience where an Access table is "too big"? 10,000 records? 100,000? 1,000,000
 
Hi MDW,


Under Access help: "Specifications" - you would find various limitations of
Access mdb file.

No specific limitation on number of records. However the file size limit for
Access97 is 1GB and Access2000 is 2 GB. Also this size limit can be
circumvented with "split design" where multiple mdb files are used.


Immanuel Sibero


MDW said:
I know this is a very generaized question with little chance of a single
Click to expand...
"right" answer. However, my own experience with Access - specifically,
Access with massive amounts of data - is rather limited in this regard.
I can guess that there is some theoretical point where an Access table has
Click to expand...
"too many" rows and the application doesn't function properly anymore (n
terms of returning the correct records in queries, etc). Does anyone have
any experience where an Access table is "too big"? 10,000 records? 100,000?
1,000,000?
 
I know this is a very generaized question with little chance of a single "right" answer. However, my own experience with Access - specifically, Access with massive amounts of data - is rather limited in this regard.

I can guess that there is some theoretical point where an Access table has "too many" rows and the application doesn't function properly anymore (n terms of returning the correct records in queries, etc). Does anyone have any experience where an Access table is "too big"? 10,000 records? 100,000? 1,000,000?
Click to expand...

There's no precise answer, of course. How well is the database
constructed? How mission-critical is the data? (That's probably
actually a more important decision-driver!) How tolerant are the users
to delay: is 2 seconds intolerably slow, or is three minutes
delightfully fast (I've had both reactions)?

The largest Access application I've heard of (still in 2.0, last I
heard) had some 20,000,000 records spread over several backends. I've
also seen apps moved to SQL at 100,000 rows. Usually the driving
factor is NOT that "the application doesn't return correct records",
but that it returns the correct records too slowly or that the
database gets corrupted too frequently.

Your decision will be based on your own constraints and criteria, but
if you want just a very broad rule-of-thumb guideline, I'd start
looking at upsizing at a few million rows in the largest table.
 
Thanks for the info. I'm looking at an app that is likely to have at most around 5,000 rows in the largest table (and that's a very liberal estimate....around 250 employees, and maintaining a record for each employee for each month, up to a year old...250 * 12 rows).
 
Thanks for the info. I'm looking at an app that is likely to have at most around 5,000 rows in the largest table (and that's a very liberal estimate....around 250 employees, and maintaining a record for each employee for each month, up to a year old...250 * 12 rows).
Click to expand...

Piece of cake; Access won't even need to breathe hard.
 
Back
Top