AutoNumber Reset Doesn't Work in XP

[RSwanson]

We are running Access 2000 against a database on a network
drive.

I have a query in this database that resets an autonumber
field (jrnl_line_nbr) back to 1 when a criteria is met.

I accomplish this by appending the jrnl_line_nbr field
from another table that has one row with the Long Integer
number 1 in it (Microsoft recommended method).

It has been working flawlessly for over a year running on
a Windows 98 PC.

I now need to have this run from a Windows XP PC.
Resetting AutoNumber no longer works.

The appended row has the value 1 in it, but the following
rows pick up with the last used autonumber (incremented by
one). In other words, inserting a row with a 1 in the
AutoNumber field does not reset the AutoNumber counter.
This is consistent with the XP PC's that I have tried, it
works consistently with the 98 PC's that I have tried. All
are running Access 2000. I have updated XP and Office with
all of the latest updates to no avail.

 

[Douglas J. Steele]

Yes, I believe the default behaviour has changed. Fortunately, though, it
shouldn't be a problem. There's no legitimate reason to need to reset the
value of the AutoNumber. It exists for the sole purpose of providing an
(almost guaranteed) unique value that can be used as a Primary Key, and it
fulfills that requirement just as well using 32365, 32366, 32367 as 1, 2, 3.
You should never assign any significance to the actual value of the
Autonumber field. In fact, you shouldn't even be showing its value to the
user.

 

[rswanson]

Thanks. I'll rewrite my query.

BTW, as far as autonumber's sole purpose, I was using it
differently. It was a very handy tool for incrementing a
record number in sets of records within a single table.
This functionality is now broken.

 

[david epsom dot com dot au]

My understanding is that the behaviour depends on which
version of the Jet Service pack you are using, rather
than on windows version as such. (note that the some windows
service packs include jet updates).


(david)