 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / December 2005Slow form when typing first character of new record?
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 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 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. > 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 . 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 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 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.  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > 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 [quoted text clipped - 17 lines] > Thanks > David 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 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. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > 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 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 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 sh.t |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.