Form opens VERY slowly if another user is on the database

[MarkGibson]

I have a front-end/back-end database made up of about 10 related tables; I have been working to speed it up, and thought I had it working fine.

I have just found out, however, that when I open a two copies of the database on separate workstations (connected by 100-base ethernet to a Win2000 workstation), that the first database opens in 22 seconds, but the second one opens in 130--140 seconds; about 120 seconds of that time is _before_ it gets to the OnOpen Event of the form. The form has 8 tabs connecting to the main table and 7 others.

I have had this difficulty before, and thought I had resolved it by compacting BE database, and by restarting the Win2000 workstation (I presumed that there was memory fragmentation after a long day of work with many applications running; although I do have 392M RAM). These solutions do not work this time!

I have the same problem whether the first copy is opened on the serving machine or elsewhere, so long as the second copy is on a workstation out on the network; but if the second copy is on the server, it is as fast as usual.... There is no other traffic on the network; it is my development network of 3 PCs only, and I am the only one using them.

Any ideas would be most appreciated!

Mark

 

[cmmrfrds]

The problem with a tabbed form is all the controls(tabs and forms) are populated before the main form is opened. If all the tabs forms data is from dynasets, this would produce a lot of work for access, since there is potentially 8 dynasets or more open on each PC and Access needs to control locking etc on the data.

Have you tried opening a non-tabbed Form first.
How many of the tabs have forms that are opened for update?

 

[MarkGibson]

I found a solution to my problems via Tony Toews at

http://www.granite.ab.ca/access/a2kslow.htm.

As his site is not always up, I will reproduce his key statements below:

"When the symptoms encountered indicate that performance is acceptable with a single user in the database but drops significantly when two or more users are in the database, the problem may be caused by interaction with the LDB file.

"In Access 2000, when a second and subsequent user tries to
access a shared backend database on the server, there seems to be a situation where Access tries to perform a delete on the LDB file (which fails because another user is currently in the file). This attempt is made about 15 times before silently failing and the records are returned from the linked table.

"To resolve this issue we can create persistent connection to the back-end from each of the front-end workstations. To implement this change, we can create a dummy (test) table in the backend file and create code in the front-end file which opens a recordset on this table and persist the recordset until the front-end app is closed."

As well, he points to the problem with allowing Access to set the subdatasheet table property to [Auto] instead of [None].

I am glad this forum is here!

Mark