Form with 11 cmbo boxes - very slow to save design changes

  • Thread starter Thread starter A C
  • Start date Start date
A

A C

Hello

Our app has a front end with the forms and a backend with the linked data.
One of the forms (the form has a table in the backend as the datasource) has
11 combo boxes which are used for the user to pick the proper domain entries
for the record fields. Each combo has a domain table as its recordsource,
and the domain tables used by each combo is also in the back end.

The backend and front end are stored on the network server.

This form takes a huge amount of time to save changes during design, up to
25secs! During the save there is lots of network traffic. For example if I
were to open the form in design view (which also takes a long time), simply
move a button slightly sideways, and then close and save, it takes 25secs to
do this and there is a fair bit of network traffic. No other form takes
this long to save design changes to (they are almost instant), even others
which have combo boxes (admittedly not as many...). Removing the combo
boxes seems to remove the problem (and network traffic). Can someone tell
me what is going on here and how to get around this issue?

In case it is relevant, the combos often have multiple fields, one with the
value to store in the record and the other which is "useful"
text/description. For example an entry in the "Concrete Type" combo might
be something like (33f4 | 33% full 4 layer), 33f4 is the value stored in
the table's record.

Thanks
AC
 
as for getting around it, i'd suggest not developing the database on the
server. once you have a "live" database running, it's better to keep a
development copy on your hard drive (including a copy of the "live" BE, so
that 1) you have "real" data to test with, and 2) so the development FE is
not linked to the server BE). when you've made whatever changes you need to
the development FE, then make a copy, link the FE copy to the BE on the
server, and move the FE copy onto the server - overwriting the "old" FE
that's on the server.

btw, i hope you don't have multiple users opening the same frontend database
on the server. if so, i suggest you either give each user a copy of the FE
on their PC hard drive, or create a separate FE copy for each user on the
server.

hth
 
Hi

Developing on my PC still takes around 5secs to save. Instant save for all
other forms. Whats causing this delay?

Thanks
Andrew
 
A C said:
Hi

Developing on my PC still takes around 5secs to save. Instant save for
all
other forms. Whats causing this delay?
Click to expand...

Try the following:

in your front end, open a table, (any table...as long as it is a table in
the back end on the network). Now, mi minimize this table.

Now, try opening your form, try doing it in design mode...try saving...

Note how the performance is the same as if you were no split or on your
local pc!!!

cool eh???

give this a try, and PLEASE post back on what you find!!!


In fact, if you want your application to really smoke, and run as if it was
un-split, then in your startup code (not during development), simply have
some code "open" a table in the back end..and keep it open. It will
eliminate these long delays.
Whats causing this delay?
Click to expand...

The answer to the above question is posted, and answered likely 10 times a
day (and, I am not be sarcasm). If spend more then a day reading stuff here,
you will see people tell you about the persistent connection trick.

The reason for the delay is that when ms-access opens the back end (be it a
report, or your going into design mode..and then saving a form) is that the
front end has to open the back end, and open the "lock" file. (the ldb
locking file). The opening of the lock file can take FOREVER across a
network. If you force a table to remain open, then this HUGE OPEN OF THE
LOCKING FILE DANCE only occurs once. In fact, ms-access actually tries to
delete this file first...again these operations take forever across a
network. If you FORCE the conneciton to STAY open, then this dance only
occurs once.

PLEASE try this, and better PLEASE report back on how the delay went away.
If you answer back, then hopefully others will read the SAME answer that we
give at least SEVERAL times a day!!
 
A C said:
Hi

Developing on my PC still takes around 5secs to save. Instant save
for all other forms. Whats causing this delay?
Click to expand...

Along with the other suggestions, see if you have the "Track name
AutoCorrect info" option turned on (Tools -> Options..., General tab).
If it is, see if unchecking that option makes a difference.
 
Hello

Thanks for the reply.

Your suggestion did make a difference, halved the time from ~30secs to
15secs. I suppose the weird thing is that this issue only appears on this
form, every single other form (including others with combo boxes, but not as
many) has split second design saves. This is why I had assumed it was
related to either this form or to the combo boxes rather than the other
regular slowdowns that Access can encounter.

So thanks for the suggestion, it has halved my problem :) but its still a
pain. Is there any reason why a form with 11 combo boxes (each sourced from
a different domain table on the back end) is so slow to save a simple design
change, when other forms dont seem to be so slow?

Thanks
AC
 
Hello

Thanks for the reply.

Your suggestion did make a difference, halved the time from ~30secs to
15secs.
Click to expand...

Not bad...100% improvement. Most folks are happy with a %25 increase...and
you done much better!!
I suppose the weird thing is that this issue only appears on this
form, every single other form (including others with combo boxes, but not
as
many) has split second design saves
Click to expand...

Start working your way though Tony's list. This sounds like a track name
auto correct issue also.
This is why I had assumed it was
related to either this form or to the combo boxes rather than the other
regular slowdowns that Access can encounter.
Click to expand...

Well, in operation of this form, 11 combo is 11 separate quires. You
certainly want to make sure those queries are optimized, and clean as
possible. So, the basic things like indexes on the primary key fields are
present? Remove select "distinct", since you do have a nice clean table with
NO duplicates...right?
So thanks for the suggestion, it has halved my problem :) but its still a
pain. Is there any reason why a form with 11 combo boxes (each sourced
from
a different domain table on the back end) is so slow to save a simple
design
change, when other forms dont seem to be so slow?
Click to expand...

I do think 11 combo boxes is a LOT. So, sure, that many combo is going to
hit you hard as you got 11 quires to be processed, and dealt with. So, much
of your problem could be 11 queries. After all, with 1 combo, you got 1
query, and with 11 you got 11 queries. That is not a 10%, or even a 50%
increase, but a FACTOR OF TEN TIMES!!....gee, 10 times is 10 times, and that
seems like a VERY large number....(don't you agree???).

Remember, a network is a very precious and slow resource.....and certainly
many times slower then a local hard disk....

So, I would check those combos, and make sure each one runs ok, and each one
has the approriate indexs etc.

And....check the track name auto correct, and further other things in that
performance list mentioned....
 
Thankyou to all who replied, it was greatly appreciated.

For reference, the track name autocorrect was the killer, down to split
second saves once that was turned off.

Tony's MS Access page was new to me, is great, and has been added to my
bookmarks (http://www.granite.ab.ca/accsmstr.htm) Recommended for other
"newbies/weekend_Access_developers"

Regards
AC
 
Back
Top