Speed Up Allen's Brown's Concatenate Code

[PSSMargaret]

I thought that I posted this on another forum but have been unable to locate it. My apologies if this is a repeat.

I’m using Access 2016 with a split database.



Each record (project) can have multiple Districts and Treatments which have been set-up as a many-to-many relationship with junction tables. One forms contains a datasheet subform of all projects that allows the user to filter all project data.

I have incorporated Allen Brown’s code “Concatenate values from related records” (http://allenbrowne.com/func-concat.html) in two separate queries (one for District and one for Treatment) so these values can be included into the datasheet subform.

In an effort to speed up this code, I created two separate modules that are called by the form’s open event that delete all data in temporary tables and then repopulate the temp tables with updated data from the concatenated queries. The temp tables are located in the front end are used in the query that populates the subform. However, it’s still very slow. The backend resides on a file server and the form takes over a minute to open.

Any idea how I can speed up this process?

[orange]

Suggest you post a "pared down" version of your database (FE and BE) with only enough records to show the problem.
Also, describe step by step with an example of what you are dealing with and what the "proper" result should be.
Why exactly must the "fields be concatenated" for display??
Readers need more info to offer more focused responses.
Good luck.

[June7]

This function (and similar ones) known to run slowly with large dataset. Doubtful it can be speeded up.

[CJ_London]

so these values can be included into the datasheet subform.

the implication from this statement is that the subform recordsource is filtered based on the mainform PK or similar using the linkchild/master properties and is not filtered when loaded.

Subforms load before the main form so you are loading the whole dataset which as June says, will be slow for large datasets

So suggest it is time you start to make your FE behave more like a web page (which only loads the required data) and not use the convenience of Access.

To do this, do not set the recordsource to the subform in its design, but instead leave it blank and use code in the main form current event to populate the recordsouce, making use of the WHERE condition. Code might be something like

me.mysubform.form.recordsource="SELECT * FROM myTable WHERE someFK=" & me.PK

[PSSMargaret]

Ajax,

The mainform doesn't have a record source and only contains buttons to print and export data in the current view of the subform. The subform's data source is based on one query and two temporary tables as mentioned above. My issue is that running the concatenated code is very slow over a network and it take over a minute for the form to open. Hoping for some way to increase the speed.

[CJ_London]

that was my suggestion based on the information provided in particular your comment 'the form takes over a minute to open.'. Which implies you are opening a full dataset/recordsource - and then filtering, rather than not opening a dataset until you have some criteria and then apply the criteria to your recordsource.

i.e.
have the subform recordsource as something like

SELECT * FROM myTable WHERE False

and when you have some criteria to apply after the user has pressed buttons/selected from combo/whatever have an 'apply' button with code something like this in the click event

me.mysubform.form.recordsource="SELECT * FROM myTable WHERE ........

If you are already doing this, then suspect there is little that can be done, but not prepared to speculate what you are actually doing

I suggest you provide more information per orange's request.

edit: One other question - are all fields used for criteria and/or sorting indexed? If not ensure they are unless the field has limited values such as yes/no. And if predominately null, modify the index to ignore nulls

[PSSMargaret]

I did eventually figure out my issue and wanted to post it in case anyone else needed it. In the concatenated queries, I had clicked TOTALS and then added GROUP BY in the total rows none of which was needed. After figuring this out, I no longer needed the temporary tables. All working great now.