Unexpected Reset of Autonumber field's value

[Bill Lucas]

I have an application that has been in production for
over three years. We recently introduced an Archiving
procedure.

Records are inserted into archive database and deleted
from the production database. The process of archiving
is set inside a transaction so if one step fails it is
all rolled back.

This process is carried out by a series of append queries
and delete queries.

The deletes occur in a parent table (tblPerson) and
cascaded through out several child tables (and if these
tables have child tables the delete is cascaded to those
tables).

tblPerson has a one-to-many relationship with
tblAddress. This relation has the cascade delete set to
true. tblAddress has one other relationship with
tblOrderAdress - also with cascade delete set to true.

Everything works fine except occasionally tblAddress has
its Autonumber value reset. Hence any future inserts
fail since that value alreay exists in tblAddress. Then
number chosen each time appears to be the lowest current
autonumber value in the table.

All other tables also have autonumber PK's and they are
not affected.

Does anyone know what might be going on?

 

[Tony Toews]

Everything works fine except occasionally tblAddress has
its Autonumber value reset. Hence any future inserts
fail since that value alreay exists in tblAddress. Then
number chosen each time appears to be the lowest current
autonumber value in the table.

I vaguely recall reading that this problem was fixed in a Jet 4.0
update. Are all your users at Jet 4.0 SP6 or SP8?

What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the tips page at my
website for more details including sample code: Verify Appropriate Jet
Service Pack is installed www.granite.ab.ca\access\verifyjetsp.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Bill Lucas]

I found an KB article on this which stated that you must
install Jet Database Engine 4.0 sp8 to resolve this
problem.

Bill

 

[Tony Toews]

I found an KB article on this which stated that you must
install Jet Database Engine 4.0 sp8 to resolve this
problem.

Also see the following article to reset the autonumbers.
Set AutoNumbers to start from ...
http://allenbrowne.com/ser-26.html

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm