Slow loading form with multiple subs and SQL BE

[AdamFeerst]

I have an important form that has 7 (count 'em) sub forms. It takes several seconds to load locally, and 15-20 seconds for people at remote locations. I need it to load more quickly.

The data BE is on an SQL server. 6 of the 7 subforms use a parent-child relationship. I use pass-throughs for those forms RecordSources, establishing the source via OnLoad; e.g.,

Me.frmSub1.Form.RecordSource = "qryPassThrough1"
​

I use OnCurrent to filter the subforms; e.g.,

stSQL = "SELECT ... WHERE ParentID = " & me.ParentID
CurrentDb.QueryDefs("qryPassThrough1").SQL = stSQL
​

Why 7 subforms? The each display different information; e.g.,

• List of individual services, AcctNos, and financial summary for a Parent.
  
• Financial totals by service type.
  
• AKA names
  
• "Child", subsidiary customer companies.

All but one of the sub forms are continuous, and can contain one to many records.

How can I speed it up? What are best/better practices for working with linked subforms with an SQL BE?

Thanks

 

[Minty]

Put the SubForms on separate tabs and only load them when the user opens the tab. You are only then loading one Subforms data at a time.

 

[AdamFeerst]

Minty,

I'll consider that (haven't worked with tabs yet). However, the original idea was to have all of this info available on a single view, without requiring additional clicks.

Other thoughts?

 

[missinglinq]

...It takes several seconds to load locally, and 15-20 seconds for people at remote locations. I need it to load more quickly...

The Form/Subform scenario, here, is obviously complex, which probably explains the 'several seconds to load locally' part of the problem. How, exactly, are these users connecting from 'remote locations?' If their connections are made by WAN this may very well be the problem. Albert Kallal is a well known authority on the networking of MS Access, and has an often quoted article on the subject of attempting to run Access across WANs/VPNs:

http://www.kallal.ca/Wan/Wans.html

But take heart...Albert does offer some workarounds for this type of problem!

As for the using of a Tabbed Control...surely, with seven Subforms, the users are having to use the mouse to do a good deal of scrolling, in order to see all data; IMHO, clicking the tab on a page if far easier than using a scrollbar, to do the same thing...forcing a user to scroll a Form a is one of the most user-unfriendly things a developer can do!

And Minty's suggestion, vis-à-vis only loading a Subform when its page is selected, is a good one...another argument for using a Tabbed Control.

Linq ;0)>

 

[AdamFeerst]

They are not doing a lot of scrolling. I'm able to display everything. There's only one where they might scroll regularly, the list of individual services (his is one that they would click on to drill down into account detail). That would only happen if the customer has a lot of accounts. Even then, the ones they are most likely to see/click on are sorted at the top.

Besides tabbed controls, is the way I'm doing it with Form.RecordSource and QueryDefs OK?

 

[Minty]

You could try restricting the amount of information the sub forms display. So if there is time / date based information restrict it to the last 30 days or whatever makes sensible viewing.
If they need to see more put a button on the sub form to expand the record source to a wider time scale / all records.
If there is no editing required can you avoid some of the sub forms completely and just pull in matching data in the underlying main form data query?

As for your remote locations unless you have very good connectivity Access over Wan is not great, have a look into remote desktop / terminal server / citrix methods.

 

[Minty]

Also have you considered using SQL Views or passthough queries to perform any complex underlying queries. This can have a significant impact, as the processing is done at the server end.

 

[AdamFeerst]

It is a major corporation with lots of systems that are accessed from around the world. So, I would assume that the WAN is very good. I will look into the other methods.

They queries are all pass-throughs. What are SQL Views?
Time/date based info is not an issue.
Editing is required, for selected users, but that is done via code - when they click on a field to edit, I open an unbound text or combo box, then update the underlying data via SQL.
It would be challenging (i.e., making complex even more so) to put some of it on the main form. In general, is that better done via the underlying query, or putting formulas in text boxes on the main form?

 

[jdraw]

Since subforms load before form, I think your delay is because all 7 subforms are loading.
If you go the route that Minty suggested, I think you will speed up the process. This is a "load when needed" or JustInTime loading of subforms.

Here is a free video by Steve Bishop that illustrates the code involved.


For more info on Tabs
see:
This older video from Datapig

Intro to Tabs video by Steve Bishop

Good luck.