When queries fire more than once

[Brian]

At what points does a query re-query its source?

I had an issue with an ODBC connection, attempting to use a SELECT
pass-through query to assign a value to a variable. The SELECT query, when
run against the DB2 back-end, passed back a newly-created auto-number
sequence number. It always ran twice, so that the result always skipped every
other newly-created sequence ID. That is, the query apparently fired once
(which created a new record in DB2) and then fired again, which created
another record in DB2 and correctly assigned the value of the second new
sequence ID to the variable.

I finally made wrote an append query that called the SELECT query
pass-through query as its source. In this configuration, the query fired only
once, so that only a single sequence number was generated in DB2.

Now, in another circumstance, I have been experimenting with calling a
public function from within a query.

Here is the query, which just calls a Long auto-number value from tblTest:

SELECT tblTest.ID, Test([ID]) AS Test
FROM tblTest;

Here is the function:

Public Function Test(ID As Long)
MsgBox ID
End Function

When I run the query, I get the MsgBox until the screen is filled up, then
it stops. If I navigate to another window, then back, or resize the window,
it starts over at the first record again, indicating that it is, in fact
re-querying the source, not just for the additional records not previously
displayed, but for all records from the beginning.

If I again write another INSERT query that calls the query above, none of
this happens, so it has something to do with the nature of SELECT queries and
when/how they refresh results.

Actually, based on this behavior, I have pretty much opted for recordset
loops to do these types of actions, but it left me very curious about the
nature of the requery in Access queries.

 

[Stefan Hoffmann]

hi Brian,

At what points does a query re-query its source?

It doesn't. It's a quite passive object. You or something else triggers
the requery.

I had an issue with an ODBC connection, attempting to use a SELECT
pass-through query to assign a value to a variable. The SELECT query, when
run against the DB2 back-end, passed back a newly-created auto-number
sequence number. It always ran twice, so that the result always skipped every
other newly-created sequence ID. That is, the query apparently fired once
(which created a new record in DB2) and then fired again, which created
another record in DB2 and correctly assigned the value of the second new
sequence ID to the variable.

What query?

I finally made wrote an append query that called the SELECT query
pass-through query as its source. In this configuration, the query fired only
once, so that only a single sequence number was generated in DB2.

Can you post a straight forward example?



mfG
--> stefan <--

 

[Brian]

Thank you for the response, Stefan. I find the expertise of those in the
Access forums my most valuable development tools, somewhere above my brain
when sorted by value...

I will reply separate to each part of your response.

First, my live DB2 example. I have simplified the object names.

I originally had this:

qryGetID (pass-through to DB2):

SELECT NEXTVAL FOR SequenceName as FieldName
FROM DUAL;

in VBA:

Dim ABC as Long
ABC = DFirst("[FieldName]","[qryGetID]")
MsgBox ABC

This always (no exceptions) resulted in the next val for SequenceName being
incremented by two. When I ran the query back-to-back twice, for example,
when the last ID used was 10022, would return 10024, then 10026, then 10028,
or, when the last ID generated was 10051, would return 10053, then 10055,
then 10057 even when I was the only user in the system.

The query in DB2, does not actually store results as separate records in
DUAL- it just re-seeds a sequence number with the next/higher number. DUAL is
a valid table in DB2, but has only one single-character field and never
contains any records. I am not certain how it is done in DB2, but there is a
function or something that acts upon the SELECT against that table, using the
selected field name as an argument to identify the sequence to increment. At
any rate, it was clearly inferred by the skipping that the query was hitting
the source twice even though technically called only once. It was as though
it was pre-querying for the record, then querying again for the DFirst (it
did the same thing with DLookup).

I was sure I had posted this part of my question to one of the Access forums
in 2004 or 2005 but could not find my original post for reference, so perhaps
I worked it out without posting. Either way, I changed my approach to this:

qryGetID (pass-through to DB2):

SELECT NEXTVAL FOR SequenceName as FieldName
FROM DUAL;

qrySaveID (native Access):

INSERT INTO tblSaveID (ID)
SELECT qryGetID.FieldName
FROM qryGetID;

in VBA:

'first delete all records in tblSaveID
'then run qrySaveID to ensure there is just one record
Dim ABC as Long
ABC = DFirst("[FieldName]","[tblSaveID ]")
MsgBox ABC

This would return, correctly, consecutive ID's on consecutive successive
calls, without skipping.

 

[Brian]

Now, on the example of a query firing more than once and producing more
wide-ranging and potentially troublesome results than in the DB2.

Based on my simple observations, I would have to respectfully disagree,
perhaps only semantically, with your statement that the SELECT query is
pasive. This is true only of the query object itself, but not of the results
window. One gets very different behavior when using a SELECT query as the
source of another query or a report than when one views the results by
running the SELECT query manually by double-clicking it. It most certainly
does refresh various records as part of its native behavior and without any
explicit triggering on the user's part.

Look at my original example from my post, where the query, as one of its
fields, calls a public function that has a simple MsgBox in it. If the SELECT
query retrieves each record only once, and in order, then theoretically,
because the function result is returned once with each record, I should get
the MsgBox once and only once for each ID.

Not so, however. It gives me the first several - just the number required to
fill the display of the query results window. If I then navigate away from
the query to another object and back to the query, it begins with record #1
and returns them all again.

I will not argue the fact that it is something besides the innert query that
is triggering this, but that "something", in this case, is the simple size or
location of the window containing the query results. Try it using my first
example. A real-world example has the same exact structure but would be too
complex to post here. This one has the same problem.

Created tblA with two fields, one a PK auto-number, and the other field of
any type. Populate it with 20 records (does not have to be that many).
Create function that accepts Long argument ID and simply performs MsgBox ID
Create query that SELECTS all records from tblA & Expr that calls the
function for each ID.
Run the query.
Click OK at each MsgBox
You will notice that it stops when it displays all the records needed to
fill the visual query results.
Page down - more results.
Page up - it requeries, starting with 1st record.

As I indicated, I opt for looping through a recordset when I need to do
something that requires an active function; however, it left me very curious
concerning the timing & manner in which the query results window actually
re-polls the underlying source by record.

Believe it or not, I can think of several circumstances when it might be
valuable to embed a function into a SELECT query. For example, here is a
real-life situation I am currently co-devloping as the Access developer along
with an C# developer. He is writing a proxy to handle the passing of requests
from a website through a firewall to Access. In order to keep his middleware
neutral (i.e. not action-specific - no need to modify it as we modify
queries), he wrote his proxy to accept a query name along with arguments as
an XML stream from the web, then hands it off to my MDB via ADO.NET.

He cannot, of course, call a function via ADO.NET, and without using the
Execute method, I have no way of handing back results. Now, if I could use a
SELECT query, selecting a single field from the source table as one output
field and the function call (as an expression, passing inbound data as
arguments to the function, and returning the function's value) as another
field, I can easily use the function to insert the records via DAO and
capture anything I want as the result: a newly-created ID, some combination
of fields as a delimited string, etc., and pass it back as results of the
SELECT query.

In my inital example above, if my function manipulates & inserts records
(for example, using Split to separate delimited inbound data into separate
records), it would result in duplicate entries because it would call for the
same result multiple times, resulting in the function being run multiple
times for the same record. However, this may, indeed, be due to the nature of
the visual query results window. I have been able to bypass this problem by
using the INSERT query as the source of another, neutral INSERT query into a
temporary table and thus ensure that each record is returned only once. Given
that the SELECT query would not have a visual component in my
ADO.NET-to-Access example, there may be no issue; however, the memory of my
duplicate DB2 records makes me very wary of using SELECT queries that are
involved in any way in direct data actions (i.e. INSERT/APPEND) outside of
including them as sources of other action queries.

There. I think I used up my allotment of characters for next year on this
forum in just two posts...sorry about the length.

 

[Stefan Hoffmann]

hi Brian,

Dim ABC as Long
ABC = DFirst("[FieldName]","[qryGetID]")
MsgBox ABC

Have you tried DLookup() instead of DFirst()?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Brian,

Based on my simple observations, I would have to respectfully disagree,
perhaps only semantically, with your statement that the SELECT query is
pasive.

Yup, it's semantically, but maybe my English wasn't quit precise enough.
I meant that a query is just a passive object. It only returns data on
request either due to opening it or when it is requeried.


mfG
--> stefan <--

 

[Brian]

Yes. I understand that the object is itself passive, but the query results
window is not passive. It filters the query to just those records that will
fit onto the screen at any one moment, then re-queries upon page up, page
down, end, re-gaining the focus, etc.

I guess this all just makes running a SELECT query manually a poor candidate
for embedding any type of active function as an expression.

 

[Brian]

Yes. I did both DLookup & DFirst.

When I called it using a domain aggregate, it fired twice. When I instead
used it as the source of an append query, and then simply called that append
query from VBA, the SELECT query fired but once.

Domain aggregate functions must do some sort of "pre-query" to test the
validity of a data source (or at least via the DB2 ODBC driver), then query
for results.

hi Brian,

Dim ABC as Long
ABC = DFirst("[FieldName]","[qryGetID]")
MsgBox ABC

Have you tried DLookup() instead of DFirst()?


mfG
--> stefan <--