Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monster slow form (XP sp 1 on Windows XP)

    Recently finished an database which is to hold information from a paper quesionnaire (deceptively only 2 sheets of A4 paper).

    The database has undergone gruesome evolution into something with about 30 tables (1 parent with loads of children) and a main form, comprising a fair few tabs pages, in the main each containing a subform. Many of the questions were of the 'tick all that are present' type, to cut down on the number of fields I stuck these in child table with each type as a row in the table.

    After adding a little of bit of code to validate some data entry choices, splitting the database and moving it onto the network I now have a bit of a monster. Opening the form is so slow.

    Any suggestions on how to speed it up?

    Forgot to mention, once its open it doesn't take long to move the the next (main record)

    I have read Microsoft Access Performance FAQ at http://www.granite.ab.ca/access/performancefaq.htm and the only thing I've tried is removing subdatasheets. Unfortunately, I the database is also some way down in a directory structure - no choice about this.

    I've noticed every time I get in a control on a subform the status bar says 'calculating' for a moment.

    Have I done something really daft or is it just unavoidable that if a form MUST contain so many subforms it will be slow?

    (Edited by HansV to make URL clickable by HansV - see <!help=19>Help 19<!/help>)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Have you turned off the Name AutoCorrect options for the database (Tools | Options..., General tab)? This can make quite a difference.

    Do you have many calculated controls? They can affect performance negatively.

    I don't really know how much effect a deep folder path has. The article you mention suggests asking the network people to create a share for the database folder, so that even if it is in reality 5 or 6 levels down, it will be perceived as the root of the share.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Wouldn't be able to get the network guys to create a share. The database doens't really have a proper home yet anyway .

    Just tried turning off the Name AutoCorrect option

    The form used to take about 16 seconds to open, now it takes less than 4 which I guess is acceptable. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    What does that option do by the way and is it a user/pc setting rather than db setting?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Name AutoCorrect is a per database setting.

    <!t>[rant]<!/t> I dislike the way Microsoft mixes up application-level and document-level settings in Tools | Options..., even within one tab page. This occurs in several Office applications, not only in Access. [/rant]

    In theory, Name AutoCorrect, which was introduced in Access 2000, is a nifty idea. Say you have several forms and reports that get their data from the same table. You decide to rename a field in the table. You will have to edit the design of each of these forms and reports to modify the control source of the controls bound to the field. Name AutoCorrect is intended to let Access do this for you automatically. But in practice, it fails horribly. In the first place, the overhead of keeping track of changes is large. Each time you open the form, Access first checks if names used on the form must be updated, hence the delay. In the second place, the implementation seems to be less than perfect, for several users have experienced database corruption caused by this feature.

    So the first thing I do with each new database is turn Name AutoCorrect off. Unfortunately, I don't know of a way to turn it off by default.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Cheers,

    I guess I've been taking that 'feature' for granted so I think in future I'll switch it off once I've finished tinkering with tables otherwise I'll probably forget and wonder why the form controls keep coming up with #name? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Still can't believe what a difference it made though <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    If your form contains lots of subforms or combo boxes, you can improve load times by setting the links when the form is loaded - not sure why it makes as much difference as it does, but it at least gives the perception that the form loads faster as it becomes visible and then the links are set. You can establish which control gives you the most bang by removing the subform links or the combo row source one at a time and see what helps the most. Just another performance trick.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    If you mean setting the Link Child fields/Link master fields when the tab page containing the subform get the focus, I had thought about that but decided it sounded too complicated.

    Don't suppose you hav ean example?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Monster slow form (XP sp 1 on Windows XP)

    Actually, setting the master/child links should be left alone. Change the sourceobject of the subform in the Change event of the tab control.
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    That makes more sense, still a bit confused with the syntax so without trying to do this for the change event of a tab I'm just trying to set to the sourceobject of a subform when its parent is opened. This seems to work

    Me!MySubformControlName.SourceObject = "SubformSourceName"

    Just realised I've probably done something really stupid - whenever I've put a subform on a form I've given it exactly the same name as it control source. I beginning to think this is not a very good idea, am I right or wrong on this one. Any tips on naming said items?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Wendell's suggestion was to set it in the On Load or On Open event of the form. I wouldn't use the On Change event of the tab control - it would fire each time the user clicks on the tab containing the subform; that seems unnecessary.

    I don't think that there is any objection against giving a subform control the same name as its source object.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Would the main form not open in the same time though, if the sourceobject for all the subforms were set when I was opened - how would that make it any quicker than if they were just there to start with - am I missing something?

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Monster slow form (XP sp 1 on Windows XP)

    I use the change event, Hans. You simply check to see if there is already a sourceobject and only set it if the sourceobject is "". That loads it the first time you open that tab page.
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    OK, makes sense.

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monster slow form (XP sp 1 on Windows XP)

    Thanks everyone, got it working but its still a bit of a beast so I think I'm going to leave it out for now. Switching off the name autocorrect increased the opening time of the form to something I think is accceptible. Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •