Multi-User Access Database design question

[Brett Smith]

Hello everybody, I am new here to the Access DB forum. I was contacted by a
company to improve their performance, and ultimately decide on what to do
with their Access database. The former programmer had one large database
which housed all the tables, and the other database stored all of the forms,
reports, modules and queries. The main databases are on a regular Windows XP
Professional box. Other machines in the office also connect to this
centralized XP box and mostly everybody has Microsoft Access. The other
machines that connect to the centralized box are very very slow when trying
to run stuff on the Access DB front end. There are at least 15 users and it
will probably increase to 25 by early next year.
I have been tasked with going through the whole database. I'm not sure
whether I should use MSDE as the back end, use SQL Server Standard edition or
just stick with Access and figure out a way to make things quicker. What do
you guys think? Can anybody help in this situation?

 

[Allen Browne]

Nothing in your scenario suggests that you would benefit from moving to a
different back end.

The database *must* be split into front-end and back-end for stability and
maintainability.

To solve the performance problems, work through each item in the Access
Performance FAQ at:
http://www.granite.ab.ca/access/performancefaq.htm
There are several very important aspects to this issue.

 

[Dirk Goldgar]

Hello everybody, I am new here to the Access DB forum. I was
contacted by a company to improve their performance, and ultimately
decide on what to do with their Access database. The former
programmer had one large database which housed all the tables, and
the other database stored all of the forms, reports, modules and
queries. The main databases are on a regular Windows XP Professional
box. Other machines in the office also connect to this centralized
XP box and mostly everybody has Microsoft Access. The other machines
that connect to the centralized box are very very slow when trying to
run stuff on the Access DB front end. There are at least 15 users
and it will probably increase to 25 by early next year.
I have been tasked with going through the whole database. I'm not
sure whether I should use MSDE as the back end, use SQL Server
Standard edition or just stick with Access and figure out a way to
make things quicker. What do you guys think? Can anybody help in
this situation?

It sounds as though the users are all, or mostly, opening the same
front-end file on the server. This may not be the cause of all the
performance problems, but it's not the best way to set it up. Every
user should have his own copy of the front-end, installed locally on his
own PC.

For the remaining performance issues, I believe Allen has already posted
a link to Tony's Access Performance FAQ. That's a good place to start.