Table Autonumbering Not Working Properly

[Chaplain Doug]

Access 2003. All of a sudden in a database that has worked fine for years,
the primary autonumber key in a table is assigning a number that has already
been used. The primary autonumber key goes up to 863. When I go into the
table and try to add a record at the end, it tries to assign a value of 793
to the primary key, which is a number that has already been assigned to a
previous record. Of course I get an error and the record cannot be added.
How might this happen and how can I fix it? Thanks for any help.

 

[Chaplain Doug]

The same problem is occurring in another table in my database that has an
autonumber primary key. It too is trying to assign already assigned primary
key values, and of course is erroring out. What could be my problem?

 

[Ken Sheridan]

Take a look at the following MSKB article:


http://support.microsoft.com/kb/291162/


Ken Sheridan
Stafford, England

 

[Chaplain Doug]

Thanks Ken. It appears that I have the latest service pack for Jet Engine
4.0, namely,
4.0.8618.0 Windows XP SP2 and Security Bulletin MS04-014

What I found fixed the problem was to do the following:

1. Create a blank database in Access.
2. Import all the tables from the backend of the databse that was having
autonumber problems.
3. Replace the "bad" backend with this new database.

The autonumber fields now give the values they shouldm i.e., incrementally
from the last number assigned.

 

[John W. Vinson]

Access 2003. All of a sudden in a database that has worked fine for years,
the primary autonumber key in a table is assigning a number that has already
been used. The primary autonumber key goes up to 863. When I go into the
table and try to add a record at the end, it tries to assign a value of 793
to the primary key, which is a number that has already been assigned to a
previous record. Of course I get an error and the record cannot be added.
How might this happen and how can I fix it? Thanks for any help.

This is a known bug; you need to update JET. See

http://support.microsoft.com/kb/291162/en-us

John W. Vinson [MVP]

 

[dbahooker]

JET IS A KNOWN BUG

USE ACCESS DATA PROJECTS

This is a known bug; you need to update JET. See

http://support.microsoft.com/kb/291162/en-us

John W. Vinson [MVP]

 

[Chaplain Doug]

Dear John:

I posted a reply earlier in this thread that indicated my update level. It
appears that I am beyond the Jet SP8 referred to in the article. Namely,

4.0.8618.0 Windows XP SP2 and Security Bulletin MS04-014

Am I mistaken in this?
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

 

[Tony Toews [MVP]]

Access 2003. All of a sudden in a database that has worked fine for years,
the primary autonumber key in a table is assigning a number that has already
been used. The primary autonumber key goes up to 863. When I go into the
table and try to add a record at the end, it tries to assign a value of 793
to the primary key, which is a number that has already been assigned to a
previous record. Of course I get an error and the record cannot be added.
How might this happen and how can I fix it?

Resetting AutoNumbers
http://www.allenbrowne.com/ser-40.html

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a
Database
http://support.microsoft.com/?kbid=257408

AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database
http://support.microsoft.com?kbid=291162

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Chaplain Doug]

Thank you Tony. The code provided at:

Resetting AutoNumbers
http://www.allenbrowne.com/ser-40.html

Works well. It is SOOO much easier than the Microsoft solution provided in
Access help.
--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.orgResetting AutoNumbers

 

[Tony Toews [MVP]]

Thank you Tony. The code provided at:

Resetting AutoNumbers
http://www.allenbrowne.com/ser-40.html

Works well. It is SOOO much easier than the Microsoft solution provided in
Access help.

You're welcome.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm