AutoNumber Using Duplicate ID

[brfender]

I have a back end database that contains a number of tables linked to one by
an ID field. The ID field ("MainID") is the primary key in tblMain. The
field properties are as follows:

Data Type: AutoNumber
Field Size: Long Integer
New Values: Increment
Indexed: Yes (No Duplicates)

When a user added a new record through a form in the front end, it would not
allow the add. When I went into the back end to see why it wouldn't, the
AutoNumber would not enter the next highest number(1369) in the MainID field.
It added a lower number (1135) that was already assigned to a different
record. When I tried it again, the next number was 1136 (also reserved by
another record).

I tried the compact and repair function with no success. I created a new
database and copied the tables over. That seemed to reset them and the
AutoNumber went to the next highest number (1369).

Does anyone know why this is happening? I don't care what the number is in
the MainID field, but I can't have duplicates because other tables are linked
using this ID field.

Thanks for the help.

 

[Allen Browne]

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

It explains some causes, and how to reset the number using code.

Did you try to compact the back end? (i.e. not just the front end)?

 

[brfender]

Allen,
Thanks for the link. The knowledge base article referenced on your link
gives this as a possible cause:
• You install Microsoft Jet 4.0 Database Engine Service Pack 8 (SP8).

I don't know if this is the cause, but we recently had Windows XP SP2
(Msjet40.dll version 4.0.8618.0) installed.

Sorry if I wasn't clear on the compact and repair function, but I tried it
on the front end and back end.

I will keep the link to your site available in case it happens again.

Thanks.

 

[Allen Browne]

Sounds like you now have SP8 for JET 4.
I doubt it would contribute to this issue.

Much more likely to be an Append query statement on the attached table.