2007 Being Weird with AutoNumber

[Sarah0824]

I have a database that was created in 2003. When we switched to 2007, it
started acting weird with the primary key field in several tables. They are
set to autonumber. When we would add a new record, Access went back and used
a deleted record's number. That was fine becuase it was not creating a
duplicate primary key field. However, for the next new record, it would
number incrementally from the reused number, and then it was creating a
duplicate primary key so we could not enter a new record.

To fix this, I ran an eppend query to bump the autonumber up to a new
starting point. It appeared to be working, but now its up to its old tricks
again. The only thing I have done other than typing a new record in, is
using a different append query to restore a record that was appended to a
different table a while ago.

I hope I have explained this clearly enough. Has anyone else had this
problem with 2007? Is there an easy fix?

Please help!! Thank you.

 

[Allen Browne]

If this is an attached table, the problem you describe occurs in all recent
versions of Access (including 2007.)

More info in:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

 

[Sarah0824]

Thank you for your quick response. It is not an attached table, but I will
try what you have suggested. I've just never seen this before so I thought
it might have been something that occurred from switching to 2007.

Thanks again.

 

[Guest]

In Access 2000 they tried to make Autonumbers better, and
every version since has been broken. They made repeated
attempts to fix the problems, short of just going back and
removing the broken features, and what we have now is the
best they could do: it doesn't normally cause random corruption,
it normally only causes predictable errors like you are seeing..

If it is worse in Access 2007, it means that they have had
another go at trying to fix it. Are you using an MDB, still
in Access 2003 format? You can still open it in Access 2003?

(david)