DLookup uses twice the SEQUENCE numbers

[DFS]

FYI,

Using DLookup("Result sField","Pass-thru query") consumes 2 SEQUENCE numbers
each time it's called.

Anyone know why?

[Trevor Best]

DFS wrote:

FYI,

Using DLookup("Result sField","Pass-thru query") consumes 2 SEQUENCE numbers
each time it's called.

Anyone know why?

Without seeing what "pass-thru query" contains, no, but I would suspect
it's something in there since DLookup is as it's name suggests just
something to look up, it doesn't write to tables.

(comp.databases .oracle dropped as my ISP doesn't carry the group)

--
This sig left intentionally blank

[DFS]

Trevor Best wrote:

DFS wrote:

FYI,

Using DLookup("Result sField","Pass-thru query") consumes 2 SEQUENCE
numbers each time it's called.

Anyone know why?
Without seeing what "pass-thru query" contains, no, but I would
suspect it's something in there since DLookup is as it's name
suggests just something to look up, it doesn't write to tables.

"Pass-thru query" contains a SQL call to an Oracle nextVal function that
increments a SEQUENCE: SELECT SEQuenceName.ne xtVal AS ResultsField FROM
DUAL;

If I use a recordset with the same SELECT statement, it uses only one
SEQUENCE number.

It's just a little strange. There's no need to waste half the ID numbers,
so I'm going with the recordset method.

Thanks
(comp.databases .oracle dropped as my ISP doesn't carry the group)

[Trevor Best]

DFS wrote:

Trevor Best wrote:

DFS wrote:

FYI,

Using DLookup("Result sField","Pass-thru query") consumes 2 SEQUENCE
numbers each time it's called.

Anyone know why?
Without seeing what "pass-thru query" contains, no, but I would
suspect it's something in there since DLookup is as it's name
suggests just something to look up, it doesn't write to tables.

"Pass-thru query" contains a SQL call to an Oracle nextVal function that
increments a SEQUENCE: SELECT SEQuenceName.ne xtVal AS ResultsField FROM
DUAL;

I know almost nothing about Oracle itself (apart from the bits that are
common to all db engines) so the above is a bit foreign to me and I
don't know how it works.
If I use a recordset with the same SELECT statement, it uses only one
SEQUENCE number.
That's interesting.
It's just a little strange. There's no need to waste half the ID numbers,
so I'm going with the recordset method.

Very interesting (I sound like Captain Jack Sparrow). I just tested
this, did a DLookup (Dlookup("FileN ame","dbo_tblFi le","FileID=5") ) on a
SQL Server table while running a trace in Profiler, I got one hit in the
trace.

For my next trick I made a pass-through query, a simple "select Filename
from tblFile where FileId=5" then did a DLookup
(DLookup("FileN ame","xxx")) (I named the query xxx), having restarted
the trace just to make sure I got two selects on that table (and I'm the
only one logged in). If I open the query there is one select in the
trace so it would appear that DLookup() on a passthrough query will
execute the query twice.

I also tested this with my own tLookup() (see
http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) and this
gets just the one select so you could revert back to your original code
and use tLookup() instead :-)

--
This sig left intentionally blank