Multi user Access database performance issue. (1 Viewer)

C

caylott

Guest
We are encountering severe performance problems when running our access database on our Network.

Symptom:

Huge drop off in performance when 2nd and subsequent users refresh links to chosen back end database. If the database is opened by another users front end database.

Takes up to 10 minutes to refresh the links to 120 tables in Back-end MDB database.

The system is designed as a split front and backend Access database. The back-end Data MDB database is located on a Windows 2000 server.

The first time a user logs on they are required to locate and Re- Link the tables from the back-end database which holds data for the current year. If they wish to work on another year they re-link the tables from the relevant back end database.

When running locally the Front end takes between 5 and 10 seconds to refresh ALL the links to around 130 tables.

When the back end is on a network drive the process takes about twice as long. But is still quick.

If another user has the file open via their own (locally held) front end, then the refresh link process takes between 5 and 10 seconds PER TABLE !!

This means that that it takes up to 10 minutes to complete the re-link.
Same behaviour if using Linked table Manager or refresh links using code

As soon as second user logs out of database the refresh link process speeds up.

The behaviour has been observed on a variety of servers and customer networks.

General data access is also much slower after 2nd user - but just about bearable. Data files are small – few records – occupy 7mb on disk.


Development Platforms – Windows XP / 200 clients, Windows 2000 Server hosts the back end database.

Front end is Access XP (MDB or MDE File) with al the latest service packs and security updates installed.

We have experienced this problem with a number of different systems running in various environments. But this wasn’t a problem with the old Access 1 database

HELP! Access should be able to support 10 – 20 users - we can barely run 2!
 

John.Woody

Registered User.
Local time
Today, 07:24
Joined
Sep 10, 2001
Messages
354
caylott

Have a look at this thread. This applies to 2kserver according to microsofts support page.

hth
John
 

R. Hicks

AWF VIP
Local time
Today, 02:24
Joined
Dec 23, 1999
Messages
619
Why do you need to relink the tables ...
If there are users accessing the BE file .. the relinking will be painfully slow as you have seen.
You should avoid any relinking if possible ...

RDH
 

RV

Registered User.
Local time
Today, 07:24
Joined
Feb 8, 2002
Messages
1,115
caylott,

If they wish to work on another year they re-link the tables from the relevant back end database.

That smells like you've set up separate tables per year.
If so, that's definitely a school example of wrong database design...
You should be using one table and one table only.
Your users should be able to retrieve data in a specific year by, for instance, using a list box on a form that enables you to select a specific year.

RV
 

Users who are viewing this thread

Top Bottom