Query hits ODBC twice

[Brian]

I have a pass-through SELECT statement that runs against a
sequence generator in a DB2 database via an ODBC
connection. The result should be incremented by
1 each time the query is run.

When I run my pass-through query manually (i.e. double-
click on the query), it correctly increments the value by
one and returns the incremented value to me. However,
when I call that query from another query as follows,

DLookup("[VariableName]","[PassThroughQueryName]")

the returned value increments by 2. It appears that the
query hits the ODBC connection once when run manually, but
twice when the query is called from VBA or another query.

Does the domain aggregate call (DLookup, DFirst, etc.)
result in the query actually hitting the database twice,
or is it something else? The result is also the same
whether I use DLookup or DFirst. If so, is there another
way to get my variable assigned to the results of the pass-
through query that will not result in the duplicate call?

 

[Van T. Dinh]

(guessing only, I don't use DB2)

1. Try creating a Recordset based on the Pss-Through Query. You can get
the Field value from the Recordset.

2. Create a Recordset using the Connection Object to the DB2 Database and a
suitable SQL String and then get the value from the Recordset.