concurrent data table access

[el zorro]

I have put together an Access 2000 database that was
originally used by a group of about 4 people. The
database is split, with the back end on a server and the
front end on each user's PC.

THe database has been working fine for about a year and a
half, but I am becomming concerned because the number of
users is increasing. Now, there are about 8 users who
potentially are accessing the back end concurrently, and
this number could increase.

My questions are: (1) What are the limits (number of
users) of concurrent activity for an MS Access database?
(2) Are there steps I should be taking to keep the system
stable? (3)If I reach the limits of my current Access
system, Is there a more robust platform that I could
export the database to, yet still be able to manage with
my Access-based database knowledge?

 

[Chris Mills]

(1) What are the limits (number of

users) of concurrent activity for an MS Access database?

There's no real limit (the stated limit is 255). 20 or 30 is OK. Remember, if
there are 20 or 30 cuncurrent, 90% of them will be sitting idle (the PC's
hopefully not the operators), and even typing is the equivalent of idle. But
if they all need to produce a report at 5 o'clock...

But there ARE data limits. The more data, the slower Access runs in general.
How well the database is designed, or what's acceptable speed, is up to you.
Usually many speed-tweakings can be done, and I only know half of them. My
best one <g> is to delete or archive old data! When a single user runs too
slow!
Don't believe that the max db size given for Access (1gB or 2gB) is practical

I s'pose more users could lead to more record contention, if they were all
adding records or something.

If anyone bombs out of Access, the db may become unuseable until it is
repair/compacted. Obviously, this possibility is proportional to nbr users. I
have some sites which go through phases of bombing out every day or hours, and
others which never do. Obviously, some sites have more stable systems than
others!

(2) Are there steps I should be taking to keep the system
stable?

Regular compacts. Say once a month. Access XP is reported to be more stable
than Access 2000.

(3)If I reach the limits of my current Access
system, Is there a more robust platform that I could
export the database to, yet still be able to manage with
my Access-based database knowledge?

The usual answer is SQL Server, which subject to some design restrictions can
still use an Access FE.

Basically it all depends on how your particular app/scenario works, and what
tricks you can do in the meantime. There IS no definitive answer, only
opinions. As usual, expect a performance hit if you upgrade from any Access to
a later one.
Chris

 

[el zorro]

Thanks, CHris! I really appreciate you for taking the
time to carefully respond to all my questions!