Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form slow on open (2000/SP-1)

    I am making some design changes to a database that I created a while back...in Access 97. My main form seems like it's taking too much time to load. Actually the form loads and all the data is displayed right away, but in the navigation bar, the record count isn't displayed for about 2-3 seconds. My main form is linked to one table. It has 2 subforms that link directly to 2 other tables. This is not a very complex form and there are only 100 records in the database. I have disabled subdatasheets and autocorrect. I have no lookups setup in the tables. I have compiled, compacted, and repaired. I have also imported all the tables and the main form into a new database, still no luck.
    This database in Access 97 with over 2000 records opens and displays the records count on the main form before the form is even displayed. I also converted the 97 db to 2000 file format and it works great.
    Can anyone help me with this? I'm so stumped(stupid).
    Thanks
    Don

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form slow on open (2000/SP-1)

    Well I guess most forms open with a delay in the record count in the navigation bar. I just can't figure out why the original database that I created in Access 97 doesn't delay showing the count? Anyhow, I will carry on.
    Don

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

    Re: Form slow on open (2000/SP-1)

    Is this a split database? If so, is the backend also in A2k or in 97? Are you sure you turned off all the subdatasheets? Do you have a bunch of table lookups in your tables? Those create implied relationships and can create subdatasheets unexpectedly.
    Charlotte

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form slow on open (2000/SP-1)

    Hi Charlotte,
    This database isn't split. As far as turning off all the subdatasheets, I went through every tables properties and set them to none. Unless there are other places you need to go to disable subdatasheets...I'm not sure. I had a few table lookups but have since removed them all. I created this database a few years back when I wasn't commenting anything or using any naming standards. I guess I'm just being paranoid here. I have tested my other databases and they all but one have that delay. I guess I just never noticed it before. I just want to make sure that I get peak performance out of it.
    Should foreign keys be indexed? Or does the "one" side of the one-to-many relationship take care of all the indexing?
    Thanks for your help.
    Don

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

    Re: Form slow on open (2000/SP-1)

    The delay is normal but it shouldn't be particularly noticeable in a small database. If you have multiple subforms each loading data, as well as the parent form, I'm not surprised you notice the delay. It is more noticeable in Access 2000 in many cases. Normally foreign keys are indexed when you create the relationship, but you may want to take a look at your indexes again. Too few or too many can both cause problems. Also, if you use a lot of indexed text fields as keys, that can slow things down. Numeric fields are faster keys. If this code migrated from an old application, you may need to bring it up to date as well. There are a bunch of things in Access still supported for backward compatibility (yeah, right!) but they are no longer the best way to do the job. Wizard built code leaps immediately to mind, since it is still built in Access 95 syntax and needs rebuilding just to be comprehensible.

    In Access 2k, you also are penalized for stacked controls and for unoptimized SQL, so those can cause delays as well. There are various techniques for streamlining forms, but you need to bring your code up to date before you really know which ones you may need.

    You might also want to look at your queries. I've seen queries get slower and slower, even in Access 97, until I lost patience and built a new query with exactly the same structure, whereupon it returned to its original speed. You can't do this by just copying the SQL from one query to another, although you can do it by using the SQL property of a querydef to print out the SQL to the immediate window. You can then copy that SQL text and paste it into a query without the gremlins that infested the original.
    Charlotte

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form slow on open (2000/SP-1)

    Hi Charlotte

    What is a stacked control, Button on top of a button?
    How do you optomize SQL?
    What are these gremlins all about?

    Thanks, John

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

    Re: Form slow on open (2000/SP-1)

    Stacked controls are controls layered on top of other controls. For whatever reason, Access 2000 dislikes them and the documentation warns against them. The query engine optimizes SQL. A saved query is optimized once when it is saved. SQL created on the fly or inserted as a select statement has to be optimized whenever the code is run the first time, i.e., the first time a form is opened a record may load slowly. If I had an answer to the gremlins question, I wouldn't still be working for a living. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

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

    Re: Form slow on open (2000/SP-1)

    We've seen some odd behavior working with 97 databases that were converted to 2K. You might try converting your 2K version back to 97, and then import everything into a new (empty) 2K database. For some strange reason, we seen significant performance boosts doing that. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> Wish I knew why!
    Wendell

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form slow on open (2000/SP-1)

    Hi Charlotte

    In your experience, what are the penalty and/or symptom when using stacked controls?

    Thanks, John

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

    Re: Form slow on open (2000/SP-1)

    I can't tell you because I never used them in the first place. If I need to "stack" controls, I use a tab control to handle it, which eliminates the alleged penalty.
    Charlotte

Posting Permissions

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