Slow form when typing first character of new record?

[dmcheng1]

Hello. I've built an ADP using Access 2002 SP3 and MSDE 2000 SP4. I
have a main form with 32 separate fields, 8 option groups (3 radio
buttons each), 16 checkboxes, and 6 subforms.

When I go into Add mode and type in the very first character of a new
record, the hourglass appears and "Calculating" appears on the bottom
left of the status bar for about 4 seconds, then I can continue
entering data normally. If I edit an existing record, there is no
delay.

As a test, I deleted all the option groups and checkboxes. When I did
that, the delay disappeared. If I left the option groups and
checkboxes and removed the subforms instead, I had to delete all the
subforms before the delay went away.

Is this delay caused by something particular to checkboxes and option
groups, or to the way they are bound to the back-end SQL columns? Or
are there simply too many objects on this main form?

Thanks
David

 

[TC]

If this happened when you typed the first edit character into an
existing record, I'd suggest that your database was set to "pessimistic
locking", and the delay occured while the lock was acquired. But I
doubt it would be this when you are typing in to a new record.

Personally, I'd delete all of the subform controls; check the problem
still occurs; then delete the other controls, one by one, hoping to
find a single control that caused the problem. Then post back here with
that information.

The other thing to try - if the form has any code behind it - is to
disable that code by adding the line:

#IF FALSE THEN

at the top, and:

#ENDIF

at the bottom. If that makes the problem go away, you know it is in the
code somewhere. Use a "divide & conquor" approach to find exactly where
it is. (Eg. #if-out each sub in turn.)

HTH,
TC

 

[TC]

PS. I guess if the combo boxes had lots of columns, and dirtying the
record caused the combo boxes to populate, that might explain it, no?

HTH,
TC

 

[dmcheng1]

Thanks for your reply. I thought it was the subforms too, but it seems
that the option groups/checkboxes have a greater effect. I have plenty
of code behind the form, but none of it is involved in the Add record
or field event areas. In fact, most of the code has to do with the
subforms and not the controls on the main form.

 

[TC]

I have plenty of code behind the form, but none of it is
involved in the Add record or field event areas.

Maybe - maybe not! The thing about debugging, is that the code is
behaving inexplicably. So you shouldn't make assumptions about what it
is or si not doing.

Do the #IF / #ENDIF thing. It would only take a few moments, and it
will /prove/ whether the problem is or isn't related to the code
module. No point assuming, when you can prove it!

HTH,
TC
..

 

[Sylvain Lafontaine]

Use the SQL-Server Profiler to see what's happens. ADP has the very bad
habit of making a lot of - unecessary - requeries of the record source for
bound and unbound control and subforms on many occasions.

Using the syntax « exec MyStoredProcedure P1, P2, ... » instead of directly
setting the record source to the name of the stored procedure may reduce
this behavior; if I remember correctly.

 

[dmcheng1]

Hi Sylvain, thanks for your reply. Actually I have set my record
sources to the actual table names. Is it recommended to use stored
procedures for ADP record sources instead of the actual table names?

David

 

[Sylvain Lafontaine]

Hum, not sure if this is a good idea; as these tables are probably big and
Access will retrieve the first 10000 records by default. However, only the
use of the SQL-Server Profiler will tell you exactly what's happening.

The use of SP gives you the possibility of filtering the various resultsets.

 

[TC]

Sorry, I meant /rows/.

Do the combo boxes have lots of /rows/? Does dirtying the record, cause
them all to populate? Maybe that could be part of the problem.

HTH,
TC

 

[dmcheng1]

I found an earlier usenet post that describes the problem - There's a
FetchDefaults form property in Access 2002. "Therefore, with Fetch
Defaults set to Yes, this calls a stored procedure on SQL Server
called: SP_Mshelpcolumns to obtain the default values. However, this
stored procedure goes through every table associated with the record
source of the main form and ALL subforms that are bound. As such, this
causes a performance degradation when there are numerous forms and
subforms involved."

My options are to

* turn off FetchDefaults and explicitly set the values of the controls;
* If a tab control is being used, set the record source of the subforms
as the user changes tabs on the tab control.

I'll try the latter.

Thanks
David

 

[dbahooker]

i just wish that MDB had the same functionality-- tracing-- so that we
could determine just how inefficient MDB is.

i mean-- just because ADP makes a couple of unnecessary calls (yeah; im
sure you're brighter than the drunk PM that is in charge of access for
microsoft) it doesn't mean that ADP is a poor choice.

ADP requerying is WHY ADP is so much better than MDB. Because it
requeires; and it's fast as shit