Diasble query?

  • Thread starter Thread starter john
  • Start date Start date
J

john

I have a saved query that calls a function as a variable in the WHERE, I've
noticed that every time I use Dlookup to retrieve data from the query, it
requeries that saved query, recalling the function variable, even though the
data is already there that I want.

Is there a way to temporally disable the query, or is this just part of its
required function?

If not, would using a recordset do the same thing?
 
If you use DLookup to get a value from a query, then naturally the query
must be run by ACCESS in order to get the value that you want. How else
would it get it -- queries' results are not automatically stored unless you
use a maketable query to write the query's results into a table, and then
you would do your lookup on the table, not the query.

I don't know what you mean by "the data is already there that I want"?

Using a recordset that is based on the query will also cause the query to be
run.
 
(not sure if I understand your question correctly but ...)

Queries don't store any data. Thus, if you use the Query as the domain for
the DlLookUp, (i.e. the second argument of DLookUp), the Query need to be
executed again to get the data which is used in DLookUp to give the desired
result.

If you don't want this to happen, use the Table, if possible, as the domain
in DLookUp.
 
That is probably my misunderstanding of the query, in that, I know it was
just called in a previous procedure, and assumed that it was therefore still
in access memory as a query result. The data I am using the query for are
external db's, once it is collected when the query runs once, I just thought
it was part of my current db until it was given new data to query.

Is it reasonable idea to store this information in an dynamic array after
the first query, so the query is only running once, or should I write it to
a temp table?

Thanks
 
Is it reasonable? That really depends upon how long the query needs in order
to run and produce its values. If it takes many, many seconds pr minutes to
run, then you could see a performance improvement if you run it once and
store its result in a temporary table. If it takes just a few seconds, I
wouldn't bother.
 
Thanks, I went with the maketable, and it really improved my performance,
especially on load time, plus it eliminates my need to save settings on
close, because the table still exist where I left off. I just did not know
the query only exist for the instance of it. If that is the right term, any
way, thanks.

Don't get lonely, I'll be back for more advice, you guy's are great with
it!.
 
Back
Top