Limitations of MS Access XP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pharah - 05 Apr 2005 18:57 GMT

I am using an MS Access database for a Business Services company with over
2,000 products and services. I am aware Access can handle this amount of
records, but I do not have reliable figures on the number of users that
will register with the site. Each user will be added to the database. At
what level of records does Access start to become sluggish/inadequate?

Thanks in advance.

Reply to this Message

Paul Overway - 05 Apr 2005 20:37 GMT

It depends on the application and how well it is designed. The database
size cannot exceed 2GB...there is no set number of records that pose a
problem. You cannot theoretically have more than 255 concurrent users, but
I say theoretical because I have yet to hear of anyone that has a database
with that many users in production. IMO if you have 20+ concurrent users
and are not having performance problems, you're doing pretty well...but that
isn't to say that you can't have more users (I've heard of apps that do).
However, some apps will have problems with just one user...and usually
they're poorly designed. So, it really depends on how well you've designed
the app.

2000 products/records is nothing for Access. Your statement that each user
will be added to the database is unclear. Meaning each user will add
record(s)? How many users? And how well is your app designed?

Signature

Paul Overway
Logico Solutions
http://www.logico-solutions.com

>I am using an MS Access database for a Business Services company with over
> 2,000 products and services. I am aware Access can handle this amount of
[quoted text clipped - 3 lines]
>
> Thanks in advance.

Reply to this Message

Pharah - 05 Apr 2005 21:24 GMT

According to my project manager, 5 users will be working on the database
adding and updating records. More will be added as the department grows.
As for the design of the database. That remains to be seen. I am in the
process of testing out the database and was wondering if it was worth
splitting the database if there were to be a problem.

Thank you for your advice. I will let her know this.

Reply to this Message

Paul Overway - 05 Apr 2005 22:00 GMT

You definitely need to split the database if you're designing a multi-user
app and have any expectations of it being successful. Otherwise, it is
likely you'll see corruption. Also, splitting allows you to update the
application without having to kick everyone out of the database.

Signature

Paul Overway
Logico Solutions
http://www.logico-solutions.com

> According to my project manager, 5 users will be working on the database
> adding and updating records. More will be added as the department grows.
[quoted text clipped - 3 lines]
>
> Thank you for your advice. I will let her know this.

Reply to this Message

Klatuu - 06 Apr 2005 18:13 GMT

Allways split the database! Another thing that will help, is to set both the
front end and back end to compact on close (Tools->Options->General - Check
"Compact on Close") This will help keep front and back ends healthy. Note
that the back end will close and compact when the last user closes his front
end.

Also, NO OBJECT IN THE BACK END EXCEPT TABLES!

Distribute mde versions to the users.

I also recommend "Database Open mode shared" and "Default Record Locking No
Locks" (Tools->Options->Advanced). This does not mean no locking is in
effect, it means that a record is only locked for the time it takes to update
it. This is optimistic locking. Using Pessimestic locking means the record
is locked while a user has it is the current record on a form.

Final thought - use unbound forms. Use a query that retrieves the requested
record, and code to add, update, or delete it. This minimized locking
conflicts and improved performance.

Although MS says 20 concurrent users, it can be more or less depending on
the design of the application. Using the strategies I have defined and some
other tricks, I have an application supporting 80 users. How many may be
concurrent, I don't know.

Reply to this Message