AutoNumber Using Duplicate ID

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
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.
 
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.
 
Back
Top