AUTO ID CREATING DUPLICATES

[Guest]

Hi,

I have a database that until recently worked fine. I've been on leave from
work and others administering to it, and a problem has appeared that they
dont know how they created, and we're not sure how to fix it.

The auto id which is the primary key has always been set to indexed no
duplicates. For some reason, even thought that is still the setting, when we
try to add a newecord, it doesnt assign a new unique id- rather it puts in an
old number. then it wont save the tecord because it would create a
duplicate. I cant edit the number field.

Any ideas?

 

[Guest]

You don't give which version you are using, so I'll have to guess that it's
Access 2000. There is a bug in Access 2000 that causes this to happen on very
rare occasions. The fix is in applying all the Service Packs. Go to support
at Microsoft.com and find the appropriate SPs for your version and apply them
to each machine running Office.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[david epsom dot com dot au]

You need to reset the autonumber. See this

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

You can reset the autonumber seed by doing an append query,
or by using an Alter Table statement, or by using ADOX code
like he shows.

Note, in addition to the problems he lists, the current version of
Jet (Jet 4 Sp8) corrupts the autonumber seed when you append to linked
tables.
http://support.microsoft.com/?scid=kb;en-us;884185&spid=2509&sid=106

(david)

 

[Allen Browne]

David, thanks for that kb link, which is now included.