PROBLEM WITH AUTONUMBER IN FORM

  • Thread starter Thread starter Paul Martin
  • Start date Start date
P

Paul Martin

Hi All,
I have developed a problem with my applications on
customer sites where if some records are deleted, the
autonumber field is reset back to a newly available
number, so the next time a record is added to the form,
an error is displayed to say that the ID filed has been
duplicated.
The only help I found on the MS website said that the
latest Jet service pack would fix this problem, but I
think it is the latest Jet service pack that has caused
it.
Does anyone have any ideas on how to resolve this problem?
Any help would be greatly appreciated.
Regards,
Paul Martin
 
Hi,


Unfortunately no, for a long integer, and part of the reason is because
it is "by design" in Jet (where it is "easy" to specify a value for the
autonumber value). In fact, an autonumber is just a value automatically
generated for you, without promise of "unicity" (no duplicated value). You
have to add an index without duplicated values ( or make the field a primary
key ), to enforce unicity, but the generated number may fails under some
circumstances, like the one you described. A preferable technique is to use
either 1+DMax, either use a GUID (at the price of being 4 times larger than
a normal long integer) rather than a standard autonumber (Long integer). See
if you can change the Autonumber Long Integer to Autonumber Replication ID.
The referenced fields must also be changed from Long integer to Replication
ID (and I mentioned that you need 16 octets rather than 4).



Hoping it may help,
(note that with a name like the one you have, the same as the next Canadian
Prime Minister, your "wishes" at Microsoft site, under Contact US, may have
more impact than mines :-) )
Vanderghast, Access MVP
 
Michel Walsh said:
Hi,

Unfortunately no, for a long integer, and part of the reason is because
it is "by design" in Jet (where it is "easy" to specify a value for the
autonumber value). In fact, an autonumber is just a value automatically
generated for you, without promise of "unicity" (no duplicated value). You
have to add an index without duplicated values ( or make the field a primary
key ), to enforce unicity
Click to expand...

That is surely not entirely correct?

The "next available value" autonumbers are meant to guarantee uniqueness.
They just don't guarantee >monotonicity< (all the values step up by 1). It
should not be necessary to define a unique index on a "next available value"
autonumber.

I imagine the "random value" autonumbers might not guarantee uniqueness ...

TC
 
Hi

I really meant uniqueness.

In some (old) version, the autonumber was restarting the sequence based
on the last number you supplied. It was a way to "reset" the autonumber
starting value (make a search on GOOGLE would confirm that this was a way to
do it, and I confirm it was working).

Jet 4.0 SP8 (maybe SP7 too), with Access 2003, do not behave as I described,
on the other hand, they do like you say (it is possible that the behavior
changed even before SP7), but autonumber itself does not supply any warranty
about uniqueness, such as shown below:


CurrentDb.Execute "CREATE TABLE tu ( auto AUTOINCREMENT, f1 varchar(50)) "

CurrentDb.Execute "INSERT INTO tu(auto, f1) VALUES( 100, '100')"
CurrentDb.Execute "INSERT INTO tu(auto, f1) VALUES( 100, '100')"


and table tu will get two records with the value 100 under auto.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top