Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Increase Speed of Opening Form (Access 2000)

    I have a database on a network drive with a form that contains 2 listboxes. Each listbox's row source is a saved query. The form doesn't open very fast. Would it be help the performance of the form if I generated the listbox's queries in code upon open of the form?

    Sarah

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

    Re: Increase Speed of Opening Form (Access 2000)

    I wouldn't think so - Access kind of compiles saved queries to optimize their performance. Obviously, this can't be done if you create an SQL string to act as row source in code.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    I don't know if that would help. What makes you think it is the listboxes that are slowing down the opening of the form? Do you have a lot of code in the form's module? I've read that heavy forms can be slow to open. Have you tried removing the listboxes temporarily to see if there's a difference? If your form is code heavy you can create custom functions in standard modules and call them from the form passing whatever values you need to.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    I find that if the query is linked to other queries that contain grouping functions or many calculations, the form may be slow to open while the queries compile that generate the data for the listbox. Using code to do this doesn't really help as the queries would still need to be run to generate the data for the listbox. (You could try using indexes in the queries to speed them.) However, In these cases, if the time to open is too long, I have written the data to a table and used the table data to open the listbox (so the queries aren't run). Of course, you would then need to take into consideration updates to the table.

    You might want to post what it is you are doing to see if there are better ways of doing the process. I have done this in the past to much success. Not only was the code shorter, but much more efficient.

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    I'm attaching a JPG of the 2 queries in design view.

    I guess I'm assuming the performance prob is due to the listboxes(lstAvailable and lstInstalled) because qryAvailable is using qryInstalled. And qryInstalled has a criteria of a text box on the main form. I'm thinking it's the circular effect that's causing the slowdown. There isn't a lot of code on the form at all. A couple of buttons with code in the OnClick event and code to requery the listboxes in the OnCurrent event of the form. That's it.

    Sarah
    Attached Images Attached Images

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    FYI - The main form's record source was an SQL statement, so I saved this as a query and now the form opens up in half the time. It still takes about 15 seconds to open, but it' s better than waiting 35 sec.

    If anyone has more ways to improve the speed of opening this form let me know.
    Thanks.
    Sarah

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

    Re: Increase Speed of Opening Form (Access 2000)

    Is there a specific reason for using a query on a query? It may be significantly faster if the two queries are combined into a single query. Also, how many records are involved, and what indexes exist on the underlying tables - those two issues can have major impacts on performance. Another thing you could do is make the control unbound, so it opens up promptly, and then set the control data source in the OnCurrent event. The delay would still be there, but the user would know things were going on - not a perfect solution, but something to consider.
    Wendell

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    I took your advice on setting listbox row source in the OnCurrent event of the main form. This decreased open up time by 10 seconds. Now opens in about 5. Getting better! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    As for your other comments/questions...

    Yes there is a specific reason for using a query in another query. I'll try and explain it. I have a listbox that contains all the company's software(lstAvailable). In another listbox I have a single employee's software they have installed on their computer (lstInstalled). When I click on an entry in lstAvailable and click a button, that entry appears in lstInstalled and disappears from lstAvailable. The queries allow this "disappearing" to happen. So it's really not possible to combine the queries (as far as I can tell).

    As for # of records, it's under 1000. As for indexes, if there is a lot of indexes (meaning over 5) doesn't that decrease performance just as much as not having any indexes? I have 4 indexs set on one table and 12 indexes on the other. If 12 is too many, I'll have to remove some.

    Sarah

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

    Re: Increase Speed of Opening Form (Access 2000)

    I assume your two listboxes are refreshed so that each time the there is a button click to change the selection for one listbox, both are requeried? That is, if I want to delete a currently installed software package, I click on the package in the installed listbox and then click the update button and both listboxes are updated. In that case, the listboxes would have different data sources, but I don't understand why either would require a query on a query. It seems you should be able to do it based on joins where one would do the unmatched software and the other the matched software. I also assumed that you have some DAO or ADO code that adds or removes records from tbl_Installed each time the update button is clicked.

    As far as indexes, the only important ones would be the primary keys for each table, and a corresponding index on any foreign keys in other tables. Performance in select queries isn't really affected, or is improved by, the number of indexes - that is not true however of action queries that edit, append or delete data.
    Wendell

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

    Re: Increase Speed of Opening Form (Access 2000)

    Another thing that speeds up form loading in A2k and above is turning off subdatasheets on the tables involved. Subdatasheets act like additional queries and can seriously slow the loading of tables themselves, let alone queries based on them.
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    Answer's to your questions/assumptions, Wendell...

    W: I assume your two listboxes are refreshed so that each time the there is a button click to change the selection for one listbox, both are requeried? That is, if I want to delete a currently installed software package, I click on the package in the installed listbox and then click the update button and both listboxes are updated

    Me: YES.

    W: In that case, the listboxes would have different data sources, but I don't understand why either would require a query on a query. It seems you should be able to do it based on joins where one would do the unmatched software and the other the matched software.

    Me: I don't know about this, but I'll look at it again.

    W: I also assumed that you have some DAO or ADO code that adds or removes records from tbl_Installed each time the update button is clicked.

    Me: YES, ADO.

    W: As far as indexes, the only important ones would be the primary keys for each table, and a corresponding index on any foreign keys in other tables. Performance in select queries isn't really affected, or is improved by, the number of indexes - that is not true however of action queries that edit, append or delete data.

    Me: Thanks for the info.

    Sarah

  12. #12
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    North Dakota, USA
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Increase Speed of Opening Form (Access 2000)

    I would just like to add that I found out turning off the Name AutoCorrect on the database speeded up the opening of my forms. By quite a bit actually on the one I asked about in this post. I'll just turn it back on when I change names of objects and then turn it off again.

    Sarah

Posting Permissions

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