MS Access Multiuser - Limitations of MS Access XP

Author

Limitations of MS Access XP

Pharah via AccessMonster.com

2005-04-06, 8:06 pm

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.

--
Message posted via http://www.accessmonster.com

Paul Overway

2005-04-06, 8:06 pm

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?

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

"Pharah via AccessMonster.com" <forum@AccessMonster.com> wrote in message
news:c52263996e114fb
b969df81cbfe1de3c@Ac
cessMonster.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
> 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.
>
> --
> Message posted via http://www.accessmonster.com

Pharah via AccessMonster.com

2005-04-06, 8:06 pm

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.

--
Message posted via http://www.accessmonster.com

Paul Overway

2005-04-06, 8:06 pm

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.

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

"Pharah via AccessMonster.com" <forum@AccessMonster.com> wrote in message
news:dcc77896f72548d
cb88580ee936048b1@Ac
cessMonster.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.
> 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.
>
> --
> Message posted via http://www.accessmonster.com

Klatuu

2005-04-06, 8:06 pm

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.

"Pharah via AccessMonster.com" wrote:

> 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.
>
> --
> Message posted via http://www.accessmonster.com
>

Sponsored Links