Max Size of MS Access 97 Databases

[Todd....@ps.net]

Hi, we are designing a rather large customer satisfaction database and a
question arose about the maximum size of data MS Access 97 could handle
without any major problems. We are talking about max rows per table and
the max size in MB/GB for the whole database. If anyone has any answers,
we would appreciate it very much.

Thanks,
Todd

-------------------==== Posted via Deja News ====-----------------------
http://www.dejanews.com/ Search, Read, Post to Usenet

[J Gary Bender]

If you have Access 97, look in the online help under "Specifications". Here
is part of the spec for "Tables":

"Table specifications

Attribute Maximum
Number of characters in a table name 64
Number of characters in a field name 64
Number of fields in a table 255
Number of open tables 1,024. The actual number may be less because of tables
open internally by Microsoft Access.
Table size 1 gigabyte
Number of characters in a Text field 255
Number of indexes in a table 32
Number of fields in an index 10"

Typically you will run into performance issues before you bump your head on
a limitation. We have run multi-100's MB databases in read-only
environments with few problems. Theoretically your could have a terabyte
database .... since a table can be about 1 gig with one table per .mdb file,
and you can have 1,024 tables open at one time.

--
J Gary Bender
Tijeras, New Mexico USA
(Remove .nSoPAM from email address)

Todd....@ps.net wrote in message <884809516....@dejanews.com>...

[iain]

I have had a MSAccess2 running at 130MB. The main table had in excess of
60,000 rows. My only downfall was the computer I was using. Although I do
recall being faced with some error message, but when I moved the mdb into
access 97 there was no problem. Admittedly, the mdb was only for a temporary
purpose, but I would use 97 if you have a option. For whatever technical
reasons, 97 was much more robust and stable compared to MSA2.

Last word, I can only suggest that you create a sample database and populate
the mdb with sim data, and benchmark it / stress test it in the operating
environment / PC's that will be using it. As MSAccess can be an emotional
product and tend to misbehave for unknown reasons. Therefore if you are
using that amount of data, and it is relatively important, perhaps you
should consider SQL server? Not that I know anything about it.

Cheers from Sydney

[Gina Gibellini]

In article <69tps4$pd0$1...@excalibur.flash.net>, J Gary Bender
<gbender...@thuntek.net> writes

--
Reading the message from J Gary Bender above you, will definitely run into
performance issues beefore the theoretical limitations are reached. I'd like to
know how MS arrived at the table spec figures. I think they're a bit over
optimistic.

I did notice the the figures quoted above are the same for Access 2.0 so
maybe with the same spec. but a new version (97), things have improved.

I do know we used an Access 2.0 DB at work with 6 tables none with more than 30K
rows and it became really unreliable and we had to regularly rebuild the DB. It
would just fall over for no apparent reason. The main tables were stored on our
server with six other terminals accessing it so I reckon this made it less
stable than a single user environment.