autonumber weirdness--help

  • Thread starter Thread starter Xenophobe
  • Start date Start date
X

Xenophobe

I have a table with a unique autonumber ID column. The table contains about
1000 records. Recently the autonumber column value got changed to something
less than the highest value. This generates a runtime error when attempting
to insert a new record because the ID column value must be unique.

How could this have happened? Any comments or insight would be greatly
appreciated.

Thanks.
 
Xenophobe said:
I have a table with a unique autonumber ID column. The table contains
about 1000 records. Recently the autonumber column value got changed
to something less than the highest value. This generates a runtime
error when attempting to insert a new record because the ID column
value must be unique.

How could this have happened? Any comments or insight would be greatly
appreciated.

Thanks.
Click to expand...

Try a repair and compact. That may fix it.

Are you using that field as a link to any other table? If not, you can
delete it, do a repair and compact, then add a new field.

Remember that autonumber does not provide consecutive numbers. Auto
number is designed the add unique numbers. Most, but not all, the time they
are consecutive.

Tell us more about this "..the autonumber column value got changed to
something less than the highest value .." What exactly happened and how?
 
Joseph,

Thanks for your reply.

The autonumbers were indeed duplicates. It was trying to insert numbers
starting with 321. The errors would continue with 322, 323 and 324. All
already exist. Success was finally achieved with 325, which didn't exist
(available mostly likely from an earlier deletion.) Bottom line is the
numbers don't need to be consecutive, just unique.

I believe the problem might be solved with the service pack indicated in
Van's message. However, I'm going to adjust the current number to something
above the highest number just to be safe.
 
Xenophobe said:
Joseph,

Thanks for your reply.

The autonumbers were indeed duplicates. It was trying to insert
numbers starting with 321. The errors would continue with 322, 323
and 324. All already exist. Success was finally achieved with 325,
which didn't exist (available mostly likely from an earlier
deletion.) Bottom line is the numbers don't need to be consecutive,
just unique.

I believe the problem might be solved with the service pack indicated
in Van's message. However, I'm going to adjust the current number to
something above the highest number just to be safe.
Click to expand...
Do add the service pack. Don't try expect autonumber to be adjustable
to anything other than unique. ("I'm going to adjust the current number to
something above the highest number just to be safe") Just let it do its own
thing keeping things unique.
 
Joseph,

I installed the Jet service pack, but unfortunately that didn't fix the
current problem. I went ahead and changed the autonumber value (using a
procedure found elsewhere) and now new records are inserted successfully.

Hopefully the service pack will prevent any future related problems.
 
Xenophobe said:
Joseph,

I installed the Jet service pack, but unfortunately that didn't fix
the current problem. I went ahead and changed the autonumber value
(using a procedure found elsewhere) and now new records are inserted
successfully.

Hopefully the service pack will prevent any future related problems.
Click to expand...

That was my take on the recommendation. I did not expect it to fix what
already be there.
 
Back
Top