Keeping query open throughout application

[Ruth]

I'm wondering if this would make my Access 2003 application run more efficiently:

In background, when a user opens the application, I would like to run a query that pulls pertinent data from a join of two large tables. I would like that query stay open throughout the session (instead of having the query run each time that it's required).

The query is used in many of the dialog boxes for selecting form and report criteria.

Is this possible and efficient, or is there another method I should be exploring?

Thanks, Ruth

 

[boblarson]

I think you're going to have a better time of it by using a table to temporarily store the data you've pulled with the query. When you go to use the query again, it doesn't just use cached values but will run again against your large data sources.

So, create a table and then change the query to be an append query. You can use a delete query to delete the data out of the table and then append the applicable data. It might be useful to do a search here on a side end. What that means is to have a throwaway database that is linked to the frontend so that you don't suffer the bloat of adding/deleting records or tables.

 

[LPurvis]

Can I just add...

>> Reason: grammar
Wow. Just huge kudos.
Even if I do say so myself, what a fantastic reason to edit a post. (That and the more than occasional typo are why I make 90% of my edits. In fact I can't even remember the last techical or syntax reason. It's all about making each post a little work in itself ;-).
And of curse makeing sur that the spilling is rite.

Anyway, I agree with Bob in that if you have data fetched from BE tables then persisting in local FE tables is very common in Access. (Indeed it's a huge benefit of Access: effectively native, local and inherently trivial data storage, not to mention fast too!)
That said, there are alternatives. You can simply open a recordset on that query and keep it open.
The data won't be requeried every time you read it from there and if you use a disconnected ADO recordset then you won't even maintain an open connection to the BE because of it (unless you Shape the data request - but I can't think why you'd be doing that... Hmm well I can - but it's extremely unlikely .
Similarly dumping the values into a memory structure of some sort succh as an array or collection (or discrete variables if you really wanted).

Many folks like to use a hidden form for application level persisted variables.
You might consider that. (Especially if you're wanting to persist a connection to the BE for performance reasons).
Just bear in mind that this isn't required due to the, often over-stated, fear of unhandled errors "resetting" variables in memory. (If that occured a lot during development then it points to places where you need better error handling - that's a good thing. If not then, for me anyway, it can't be a prroblem once implemented - as I always distribute MDEs).

Cheers!

 

[Ruth]

Thank you BobLarson and LPurvis for your thoughtful replies. I think I've interpreted them correctly, and I've been playing with both of these ideas:

1) an appended table in a linked side-end local DB
2) a bound hidden form with a recordsetclone call that opens on start-up

For each user, there is a persistent connection to the BE - yes, for performance reasons. I'm really just trying to speed things up across the network. This is a relatively simple application; the BE is running at about 1.3Mb, and yet it's slow.

Your thoughts on either/or or both?

 

[LPurvis]

Is everything slow?
Literally, if you open a form bound to a single row, simple linked table - what sort of time lag does that present?

If not then what, by comparison, is slow - and by how much?

Cheers.