I am a longtime stalker of this forum, and have learned so much from all of you. This is my first post, please be gentle.
I have an Access 2002 fe with tables linked to sql server 2005. My main form is bound to a query that returns between 100 and 200 records and contains an empty subform control that has its sourceobject set to one of 12 different continuous subforms based on the selection made in one of 2 unbound comboboxes on the main form (the other unbound combo limits the number of items that appear in the second combobox). The master/child link is set for the subform control.
Each of the 12 subforms is bound to a query that returns less than 100 records. All of these queries -for the main form and each of the queries bound to a subform - open nearly instantaneously as queries (the entire recordset displayed nearly instantaneously). The main form opens acceptably (a couple of seconds to open, and another second or so after the first combobox selection). Things go very wrong when the sourceobject for the empty subform control is set. It takes 7-9 seconds for the subform to load, and 2-3 seconds to navigate between records. This is the primary “production” form for this application.
I am perplexed that the queries open so quickly - but the forms, not so much.
· there is no code in the on load or open events for the subforms (there is an if statement to enable/disable a single control in the on current event)
· there is no code in the main form except a docmd.maximize on open.
· Option explicit is declared for each form
· auto namecorrect is off
· the master/child field is indexed
· all the cangrow/canshrink properties are set to No for all form sections
· I can't turn off sub-datasheets for linked tables
· I have not added a persistent back-end connection (my understanding is that this connection aids in ldb file issues for an access be - which doesn't apply)
There is a second FE linked to a different large table in the same sql server database. The "problem" fe was copied directly from the non-problem fe (with changes to a few field names). This other table has about the same volume of data (40K rows vs. 48K rows), the master/child field is NOT indexed, there are two memo fields on each subform, there are triggers attached to the underlying table, validation rules applied to multiple controls on the subforms and yet the subforms in this other fe open within 2 seconds and there is no lag between records. This is perplexing as the non-problem fe is the one that should be suffering the poor performance.
there are overlapping controls on all of the subforms in both applications. These are unavoidable as the subforms are continuous forms with combobox controls.
Like a good googler, I have gone to:
http://www.granite.ab.ca/access/performancefaq.htm
and done/tried everything listed that I wasn't already doing that seemed applicable. But alas, there is no change in performance.
I have tried:
· changing the memo/ntext fields to text/nvarchar(255), no change
· loading recordsources for the subform & the subform comboboxes at runtime - again, no change
· indexing additional fields used in query criteria - again, no change but probably not a bad idea anyway
· hiding and then deleting textboxes displaying recordcounts thinking perhaps access was waiting until it had counted all 1-10 subform records per main form record. Again, no change.
I don't believe this is a network issue - since all other forms load/navigate respectably. It is just the primary "production" form in one of the two front ends that is causing a problem. I cannot use a passthrough query for the subform source (it must be updateable), but I could use a passthrough for the main form. I don’t think it’s necessary though –the query speed isn’t an issue.
Since the queries run so quickly, I'm assuming this is a form problem - I'm at a complete loss and don't know where to go or what to try next. Any ideas for what I can look for or what I should try next?
I have an Access 2002 fe with tables linked to sql server 2005. My main form is bound to a query that returns between 100 and 200 records and contains an empty subform control that has its sourceobject set to one of 12 different continuous subforms based on the selection made in one of 2 unbound comboboxes on the main form (the other unbound combo limits the number of items that appear in the second combobox). The master/child link is set for the subform control.
Each of the 12 subforms is bound to a query that returns less than 100 records. All of these queries -for the main form and each of the queries bound to a subform - open nearly instantaneously as queries (the entire recordset displayed nearly instantaneously). The main form opens acceptably (a couple of seconds to open, and another second or so after the first combobox selection). Things go very wrong when the sourceobject for the empty subform control is set. It takes 7-9 seconds for the subform to load, and 2-3 seconds to navigate between records. This is the primary “production” form for this application.
I am perplexed that the queries open so quickly - but the forms, not so much.
· there is no code in the on load or open events for the subforms (there is an if statement to enable/disable a single control in the on current event)
· there is no code in the main form except a docmd.maximize on open.
· Option explicit is declared for each form
· auto namecorrect is off
· the master/child field is indexed
· all the cangrow/canshrink properties are set to No for all form sections
· I can't turn off sub-datasheets for linked tables
· I have not added a persistent back-end connection (my understanding is that this connection aids in ldb file issues for an access be - which doesn't apply)
There is a second FE linked to a different large table in the same sql server database. The "problem" fe was copied directly from the non-problem fe (with changes to a few field names). This other table has about the same volume of data (40K rows vs. 48K rows), the master/child field is NOT indexed, there are two memo fields on each subform, there are triggers attached to the underlying table, validation rules applied to multiple controls on the subforms and yet the subforms in this other fe open within 2 seconds and there is no lag between records. This is perplexing as the non-problem fe is the one that should be suffering the poor performance.
there are overlapping controls on all of the subforms in both applications. These are unavoidable as the subforms are continuous forms with combobox controls.
Like a good googler, I have gone to:
http://www.granite.ab.ca/access/performancefaq.htm
and done/tried everything listed that I wasn't already doing that seemed applicable. But alas, there is no change in performance.
I have tried:
· changing the memo/ntext fields to text/nvarchar(255), no change
· loading recordsources for the subform & the subform comboboxes at runtime - again, no change
· indexing additional fields used in query criteria - again, no change but probably not a bad idea anyway
· hiding and then deleting textboxes displaying recordcounts thinking perhaps access was waiting until it had counted all 1-10 subform records per main form record. Again, no change.
I don't believe this is a network issue - since all other forms load/navigate respectably. It is just the primary "production" form in one of the two front ends that is causing a problem. I cannot use a passthrough query for the subform source (it must be updateable), but I could use a passthrough for the main form. I don’t think it’s necessary though –the query speed isn’t an issue.
Since the queries run so quickly, I'm assuming this is a form problem - I'm at a complete loss and don't know where to go or what to try next. Any ideas for what I can look for or what I should try next?
