Access Performance

[Irina]

Hello Access Community,

Have couple of questions:
We have an application with 90 tables linked from front end to the back end.
Both in MS Access.

What is the optimal number of records that access can handle without
sacrificing the performance in the multi-user environment?

Is there a recommended number of tables for better performance?

How many consecutive users can Access allow with no performance problems?

Little Background:
We have an Access multi-user application (front and back end is access) with
90 tables that are linked from front part to the database. We find that the
application works much better is a stand-alone mode, but when we put it on
the network and allow multiple users, performance sometimes is unacceptable.
Recently we had a request to import 40,000 records into the application, so
our client can use it. I am not sure if Access can handle this many records?
Can the performance be improved?

Any help iwould be appreciated,

Thank you.

 

[Jerry Whittle]

The number of records nor the number of tables make all that much difference.
More importantly is the design of the database. Is it normalized properly?
Are the tables properly indexed? How's the network. I've seen Access
databases with a half million records run very well. I've seen poorly
designed databases with less than 10,000 records just crawl.

The number of tables is based on the proper design of the database and not
trying to hit some optimum number of tables. This is sometimes a balancing
act and a place for compromise. A database at the First Normal Form will be
inefficient. A database of any complexity at the Fifth Normal Form just won't
run. Try to get to the Third Normal Form.

Number of users? Microsoft says 255. They also said that Windows 95 would
run well on 16 MB of memory. As a rule of thumb, I start worrying around 20
concurrent users even with a well designed database.

Where's the FE? Do each user have a copy of it or do they all share the same
FE? That can make a big difference right there.

 

[Tony Toews]

Have couple of questions:
We have an application with 90 tables linked from front end to the back end.
Both in MS Access.

What is the optimal number of records that access can handle without
sacrificing the performance in the multi-user environment?

A few million.

Is there a recommended number of tables for better performance?

A few hundred.

How many consecutive users can Access allow with no performance problems?

A client had 10 or so doing date entry and updates with another 20
doing inquiry and reporting.

We have an Access multi-user application (front and back end is access) with
90 tables that are linked from front part to the database. We find that the
application works much better is a stand-alone mode, but when we put it on
the network and allow multiple users, performance sometimes is unacceptable.
Recently we had a request to import 40,000 records into the application, so
our client can use it. I am not sure if Access can handle this many records?
Can the performance be improved?

Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm