Database size limitations

[Phil]

I am new to using MS Access, but have set up a three table database with
about a total of 50 fields, most in one table with unique address
information for 538 homes on our neighborhood. I will be adding as many as
25 new records per day to one of the tables, and perhaps another 10 per day
to another related table. This means that the database records on one table
could grow to 9000 records in a year, and we will be adding records
indefinitely. Even I continue with this just for the next 15 years, that is
137,000 new records just for one table. What are the limitations of Access,
and how might one go about ensuring best performance with a lot of records?

Thanks,

- Phil

 

[Allen Browne]

No worries. Hundreds of thousands of records represent no drama for Access.

Just use a good design (normalized), with astute indexing.

 

[Mike Painter]

I am new to using MS Access, but have set up a three table database
with about a total of 50 fields, most in one table with unique address
information for 538 homes on our neighborhood. I will be adding as
many as 25 new records per day to one of the tables, and perhaps
another 10 per day to another related table. This means that the
database records on one table could grow to 9000 records in a year,
and we will be adding records indefinitely. Even I continue with
this just for the next 15 years, that is 137,000 new records just for
one table. What are the limitations of Access, and how might one go
about ensuring best performance with a lot of records?

Come back in about 150 years and report to us

There is a total file size limit to Access (2 Gb) and until you get near
that limit, assuming you have proper indexes you should see no problems.

Even without indexes you might not see any slowing of the system for several
years.

This happened to me several years ago with a 200MHz machine. They were
adding over 100 records per day. I happened to be there one day when someone
said that once in a while it seemed to take a couple of seconds before the
screen came up.

Which leads to the lesson for the day.
If you have a two part key that does *not* mean that the individual fields
have their own index.

 

[Tom Wickerath]

Hi Phil,

What are the limitations of Access, and how might one go about
ensuring best performance with a lot of records?

To ensure the best performance, make sure you have plenty of RAM memory (I have 512 MB on my
system) and lots of free space on a regularly defragmented hard drive.

If you search Access help with the keyword "specifications" (without the quotes), you should find
a listing of all the applicable specs. for your version of Access. This includes maximum file
sizes of 1.0 GB for Access 97 and 2.0 GB for Access 2000/2002/2003. You really don't want to
come too close to these maximum sizes--I recommend keeping at 75% or below as a maximum. Access
does not have any limit as to the number of records. To help prevent database bloat, it is
recommended that you do not embed images or any other type of BLOB objects such as Word
documents, etc. Also, run a compact and repair process regularly: Tools > Database Utilities...

Compact and repair. In Access 97, the repair is a separate process and doesn't need to be run

regularly.

I question the need for 50 fields in one table. This doesn't seem like proper database
normalization.

Tom
_____________________________________


I am new to using MS Access, but have set up a three table database with
about a total of 50 fields, most in one table with unique address
information for 538 homes on our neighborhood. I will be adding as many as
25 new records per day to one of the tables, and perhaps another 10 per day
to another related table. This means that the database records on one table
could grow to 9000 records in a year, and we will be adding records
indefinitely. Even I continue with this just for the next 15 years, that is
137,000 new records just for one table. What are the limitations of Access,
and how might one go about ensuring best performance with a lot of records?

Thanks,

- Phil

 

[Fred Boer]

Dear Mike:

Which leads to the lesson for the day.
If you have a two part key that does *not* mean that the individual fields
have their own index.

Thanks for the "lesson of the day"! I was just wondering about that last
night, and puzzling about whether individual indexes were necessary when
both fields were already part of a two-part key...

Your timing is impeccable... well... as far as I am concerned!

Fred Boer

 

[Douglas J. Steele]

Dear Mike:


Thanks for the "lesson of the day"! I was just wondering about that last
night, and puzzling about whether individual indexes were necessary when
both fields were already part of a two-part key...

Your timing is impeccable... well... as far as I am concerned!

You may want to do some testing, but I've always assumed it's like other
DBMS, where if you've got FieldA and FieldB in an index, that will be used
for queries that only reference FieldA in the WHERE clause, but you need to
create an index on FieldB for queries that reference FieldB and not FieldA.
In other words, I don't think you need to create a FieldA only index if
FieldA is the first field in a multi-field index.

 

[Phil]

I was wrong. 25 fields in the one table, not 50. But, the record for each
field in the address table is almost always a unique value. Some of these
include:

Owner Name Last
Owner Name First
Street Address #
Mailing Address fields (when different)
Date Sold
Telephone

 

[Allen Browne]

That is my understanding also, Doug.

 

[Fred Boer]

Almost forgot to thank you both! And Tom, too!

Cheers... and thanks!
Fred

That is my understanding also, Doug.