Cache all query results locally while in Design view for forms/reports to improve performance.
Sometimes queries can be expensive, or have errors affecting performance. Currently, if one of these expensive (i.e. very slow) queries is in a form's or control's Data source, (like a Combo Box, for example), that query can be re-evaluate repeatedly and frequently (very slowly EACH TIME) making any effort to edit or design the form a legitimate pain in the keester. Give us the option to cache these query results during development (Design View) so we can work FAST, like Access is supposed to!

3 comments
-
Mark Burns commented
Allow me to give you another example from very current personal experience:
GIVEN: You have an "expensive" ODBC Pass-thru query to a corporate data store that can easily run 20-30+ MINUTES _*each and every time*_ you try to run it (hundreds of millions of data rows, poorly indexed for your query purposes as is often possible in "Management reporting environment"-type corporate database scenarios).
Now, your task is to execute this query and report out the results. Simple, right?
So create a new query, and select the qryVeryExpensive from the table selection dialog under the Queries tab to past it into the query designer...
(wait that 20-30+ minutes here for the query to run - JUST so the query designer can read the resulting recordset's schema for the column/field names and data types...)
complete that process (eventually).
Then, select the "Make Table" option to change the query type and enter the target table name. oops - you misspelled it. Click on the SQL view to fix the target table name, and then click back to the designer view...
(NOW YOU AGAIN MUST wait that 20-30+ minutes here for the query to run - JUST so the query designer can read the resulting recordset's schema for the column/field names and data types...)
...get the picture here?
There are SO MANY paths to make little mistakes and mis-steps in Access like the above example that can cost you TONS of TIME - at the very least - for no real net gain!
When dealing with ODBC Pass-thru queries, ANY of the query/form/report designers MUST have a means of cache-ing a sufficient enough portion of the FIRST query's results so that subsequent "automatic" re-execution of the query DOES NOT NEED TO HAPPEN!
There needs to be an option to override the cached results and force it to run, if the circumstances dictate - UNDER THE USERS' CONTROL. -
Mark Burns commented
@AlanC Yes, of course I have, but in some cases, say when outside forces change the underlying technical conditions under which the application runs and/or was first developed - like Admins moving a SQL Server to the far side of a WAN link post-deployment, for one possible example, there are serious impacts to being able to edit existing objects. It is for occasions like these that I make this request (in can literally mean the difference between being able to maintain an application and not).
-
Alan Cossey commented
Have you tried leaving the query open when you are in Design mode. Sometimes that helps.