AutoNumber Using Duplicate ID



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.




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.




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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question