Data dump to load form faster with subreport totals

[aellistechsupport]

There's a main form with several sub reports that summarizes the live SQL data.

It takes some time to load due to the "calculating".

I was thinking of doing a data dump (AKA: extract the data to a table then use that for the sub reports).
However, since the form is linked to the tables, it can't delete and recreate those tables when the form is loaded.

I can't figure out a solution on running the macro to do the data dump into the temp tables then the sub reports totaling from that WHILE the form is opened?

[June7]

You have subreports running on a form?

Why delete and recreate tables? Why is form linked to temp table?

A temp table is really permament - the records are temporary.

I use temp tables for several data manipulation processes. I delete all the records before each execution.

Is this a multi-user split db? The temp tables must be in the frontend.

[aellistechsupport]

Yes there are reports on the main form.
The user wants to see summarized totals.

The totals needs to update / refresh whenever they load the form so they have up to date totals.

No on the multi-user split db.

I'm using Make Table. Just trying to figure out the best way to load the current data and making it open faster.
Having the live links and calculating the summarized totals just takes a long time. So I thought let's do a data dump BUT since the sub reports run the tables for the totals it can't delete it while the form is loaded.

make sense?

[June7]

A process that routinely modifies db structure is usually not a good idea. Hence a 'temp' table that is permanent but records are purged.

But no, does not make sense to me. Form should be bound to raw data source. Reports bound to the manipulated data source. Where is the conflict?

[aellistechsupport]

A process that routinely modifies db structure is usually not a good idea. Hence a 'temp' table that is permanent but records are purged.

But no, does not make sense to me. Form should be bound to raw data source. Reports bound to the manipulated data source. Where is the conflict?

Do not know what you are talking about? What db structure is being modified?
That is correct, "temp" tables are created, prior data purged and new one created with current data with the Make Table queries.

Form is not bound to anything. it is being use as a dashboard where the user can make their selections to generate reports based on the selections. The reports on the form (or sub reports) are summarized totals of the total data as well as summarized data based on selections.

Issue again is that I can't delete the "temp" tables because the are linked to generate the summarized totals on the reports that's on the dashboard (form).

What this Access is doing is just really being used as a dashboard for the user to generate reports based on selections. Where the data are linked to SQL tables/views to pull the information needed. Nothing is being done to those tables except to extract the data needed. It's only being used to generate the data sets.

[June7]

You said you were using Make Table. Creating tables is modifying db structure.

When I use a temp table, I manually create the table - this is a one time action - then with code I add and delete records. But it is always the same table. I never delete the table.

So by "However, since the form is linked to the tables ..." you mean UNBOUND comboboxes are compiling lists from the temp tables?

[aellistechsupport]

I am extracting the data set from the SQL tables/views in order to generate the summarized totals since that'll be faster then calculating the live data when the dashboard (form) is loading. As mentioned it is SLOW. So I thought I'll use the Make Tables to create the "temp" tables of the data dump (if you will) in order to do the summarized totals in the report that's on the form.

Since the live calculating is currently done every time this Access file is opened throughout the day. Though arguably the "downloading" time of the data set to the "temp" tables via the Make Table will take time as well, but the form will load faster with the data already "downloaded" locally.

So for this use it doesn't matter the "temp" tables are deleted and recreated. You know what I mean?

That's all that matters at this point. How to load the dashboard of information faster where it contains reports that generates summarized totals and it just takes a long time being connected to the live tables and having to calculate all that data.
Hence "downloading" the data set to summarize against.

[June7]

Apparently it does matter if you "can't delete and recreate those tables when the form is loaded". Close form, run the data update, open form. Sorry, but I am still somewhat confused by the described process.

Frequent modification of db increases risk of corruption and also causes db size to expand. Run Compact & Repair regularly.

[aellistechsupport]

Yes that's right and the problem as to why I'm here ... can't delete it when the form is loaded.
I think I get what you're saying now, in order to avoid deleting the table since it's used to "query" results, make it an append query after deleting the data in the table without actually deleting the table itself.
DUH! Sorry!


There is no corruption and it always compacts and repairs on close.

So to recap the solution for the 1st part, summarizing the totals:
1. Instead of Make Table queries, because it deletes the tables that are used on the reports, create the table which establishes the table structure, the change the Make Table into an Append Table.
2. Create a Delete query to delete the data from the "temp" tables.
3. Create a Macro or Procedure, etc ... to process the Delete data query then the Append query so the tables are intact and the reports on the form can still read them.

I'll have to try for the 2nd part, where the summarized totals are based on user selections.