Hi,
The query optimizer knows that AutoincrementNo(), where it appears in your
query, does NOT depends on any FIELD of any TABLES implied in the query.
THAT IS THUS A CONSTANT, in its opinion, and a constant expression needs to
be evaluated only once, exactly as if you have
SELECT f1+3+4 FROM myTable
will likely become, internally
SELECT f1+7 FROM myTable
That is the kind of thing an Optimizer looks for. To "fool" the optimizer,
send an argument, a field name:
SELECT AutoIncrementeNo(fieldNameHere) FROM myTable
the Optimizer will then 'think' the function has to be evaluated for each
record.
Have your function like:
---------------------------
Public Function AutoIncrementNo( Optional arg As Variant) As Long
Static intTransacNoEDI As Long
If IsMissing(arg) then
intTransacNoEDI =0
Else
intTransacNoEDI =1+intTransacNoEDI
End If
AutoIncrementNo=intTransacNoEDI
End Function
-----------------------------
thus,
SELECT AutoIncrementNo(), AutoIncrementNo( fieldName)
FROM myTable
will automatically initialize your counter to 0, because of the 'constant'
expression evaluated once, through AutoIncrementNo(), and also, you will get
1, 2, 3, ... through AutoIncrementNo( fieldName) . So, no need to
'remember' to reset the global variable to 0, before launching the query!
is it not nice?
Only problem, Jet also optimized the computation of functions (in recent
versions of Jet). As example, if you look at the result, the first page will
look fine, make a page down, again, fine, but then, make a page up to go to
the start, and oops, the sequence continue to fire, rather that being 1, 2,
3 ... as initially.
So, as elegant as it can be, I suggest you append the data to a new table
that would have an autonumber (autoincrement by 1), and read that
(temporary) table once the data (other than the autonumber) had been
appended.
If you are using MS SQL Server 2005, you can use the new function RANK to
get a continuous sequencing.
Hoping it may help,
Vanderghast, Access MVP
