2007 Being Weird with AutoNumber

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sarah0824" wrote in message
...
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.

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.

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sarah0824" wrote in message
...
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.

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)


"Sarah0824" wrote in message
...
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.