What's Practical Limit of Access App?

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

Mike Thomas

We have two Access apps running on Citrix, each one is used by about 15
people at a time. The back end Access databases are both about 120 meg, and
doubling every year for the past four years. It's grown more than we
thought.

I'm waiting for it to explode, but before that happens I thought I'd ask
whether anyone has any idea what the practical limits are. I would says
performance is already degrading.

Many thanks,
Mike Thomas
 
It's almost impossible to give an absolute answer, as there are so many
variables involved: how good is the database design, what's the usage
pattern like (read-only vs update), hardware, etc.

120 MB is still pretty small for an Access database.
 
Thanks Doug, that's good news - I know Microsoft advertises that the
database can be much larger.

Mike Thomas
 
120 MB is not huge, but you mentioned that the database has been doubling
every year for the past four years. Do you expect that rate of growth to
continue? At that rate, you'll reach 1,920 MB in four years, and that's
getting very close to the 2 GB limit.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
It is possible it will continue to double, probably not likely. My worry is
that 2 gb is what Msft advertises. In fact, the practical limit could be
much lower, but I don't know where that would be.

Before Access gives up, though, I think performance will get so bad that
action weill have to be taken.

Thanks
Mike Thomas
 
I have heard of someone using an Access 97 MDB that was very close to the
Access 97 1 GB limit without any problems, but I think that was a single
user database, and I think it was mainly or only used for looking up data, I
don't think there was a lot of updating going on. The largest MDBs of which
I have personal experience would be in the range of 200-300 MB. These are
multi-user databases that are being frequently updated, and performance is
satisfactory.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Brendan Reynolds said:
I have heard of someone using an Access 97 MDB that was very close to
the Access 97 1 GB limit without any problems, but I think that was a
single user database, and I think it was mainly or only used for
looking up data, I don't think there was a lot of updating going on.
The largest MDBs of which I have personal experience would be in the
range of 200-300 MB. These are multi-user databases that are being
frequently updated, and performance is satisfactory.
Click to expand...

I've had a multiuser Access 97 application up to 800MB before any
problems arose. It definitely depends on what the application is doing,
because some actions can cause the file to grow very quickly.

Mike -- is it plausible for your application's data store to be growing
the way it is? Certain actions, like storing pictures in the database,
cause rapid bloat and can be avoided.
 
Thanks Dirk,

Yes it's plausible - there are very many transactions occurring. The
database is well designed, normalized, and there is nothing fancy like
pictures. There is a little fat to squeeze out, but not too much.

I think the next big project will be to archive old data, but that will be a
complicated task fraught with danger.

Mike Thomas
 
Back
Top