PLEASE HELP! auto number is making duplicates

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

I tried to enter a new file and my auto number is making duplicate auto
numbers that have already been used. I check my design properties index and
it is set to yes (no duplicates). I did a back up and compact and repair
twice. I can't think of any changes I have made that could have affected
this. I was working yesterday on the front end of the design of some forms
but did nothing to tables.
PLEASE HELP...

Rebecca S.
 
Hi Rebecca,

I had the same problem a couple of months ago.
It was a client/server application. The access file at the
server had the tables and from the client file I was
inserting new records into the linked table.

When a used an Append Query to insert new records, I
started to get duplicate autonumbers. But when I used an
SQL statement "INSERT INTO ..." in code, it worked fine.

I had to remove the autonumber column and then add it
again.

Igor.
 
Hi Rebecca,

According to some sources, this can be caused by index corruption, so it may
not have been somthing you did!

Before you do anything else, have a look at this and see if anything helps:

http://www.google.com/search?sourceid=navclient&ie=UTF-8&oe=UTF-8&q=fix+duplicate+autonumber+access

If you like, you could try this, I would suggest you read it through and
understand what I am suggesting before you start, in case I have made an
incorrect assumption, or a typo, or worse...

MAKE A COPY OF THE DATABASE. In case we make it worse...

MAKE ANOTHER COPY OF THE DATABASE. In case we make it worse...

In the following, I will assume that your AutoNumber field is called
'RecordId'
In design mode rename the autoNumber field to _RecordId

Create 2 new Fields:
one called OldRecId and one with the same name as your original AutoNumber
field (RecordId)

Run an update query to copy the values from your existing AutoNumber field
(the one that we just renamed to _RecordId) into the OldRecId field. This
makes a copy of the existing numbers.

Make the new Field we created (RecordId) an AutoNumber

When you save the table, you should then have a table thus:

_RecordID - Your original, messed up, autonumber
OldRecordId - A copy of the numbers in the above
RecordId - A shiny new AutoNumber field (with new hopefully non-repeating
autoNumbers in it)

You are now going to have to update any other tables that reference the
original AutoNumber field as the new AutoNumber will contain different
values. This should not be too difficult as you have both the old and the
new values in your table now.

You still have the problem of the records that had duplicated AutoNumbers in
them. You may have to deal with these by hand...?

Hope that this helps a little.

Cheers,

ChrisM
 
Thanks Chris

I understand up to here:
You are now going to have to update any other tables that reference
Click to expand...
the original AutoNumber field as the new AutoNumber will contain
Click to expand...
different values. This should not be too difficult as you have both
Click to expand...
the old and the new values in your table now.
Click to expand...



Almost all of my other (probably 20 or so) tables have relationships with
the MemberID (what you referred to as the recordID) How do I get all of
those tables to recognize the new MemberID? Do I need to: change the field
name, add the extra fields, do an update query to add the new autonumber to
all of these tables?



Right now I do not have any duplicate MemberID's I actually caught it and
deleted the dups right away. So all I need to change/fix is getting the
autonumber to work.



Rebecca
 
Right now I do not have any duplicate MemberID's I actually caught it and
deleted the dups right away. So all I need to change/fix is getting the
autonumber to work.
Click to expand...

What version of Access, and - more critically - what service packs?
There have been recurrant bugs with duplicate autonumbers in Access.
See http://support.microsoft.com/?kbid=247408, and get the latest
update for the JET database engine (links on that site).
 
I am using office XP.
Where do I find the service pack? I may need to do an update. I haven't
done one in probably 2 months.

If it is the update, will this fix my problem? or will I still need to
change the id numbers like ChrisM suggested?

Thanks
Rebecca S.
 
I found this in the database "about microsoft access"
Microsoft Access 2002 (10.4302.4219) SP-2

Does that give you what you need to know?

Rebecca
 
I did all my updates that I could find that my system needed.



I read on a page that suggested that my autonumber could be corrupted in the
old database and the website suggested making the new database and importing
the tables. So I did and the test is adding the autonumbers correctly.



My question is, I have a split database. What is the simplest way to get
the tables linked to this new database and not the old one where the
autonumber is not working? Is it possible?





Rebecca S.
 
My question is, I have a split database. What is the simplest way to get
the tables linked to this new database and not the old one where the
autonumber is not working? Is it possible?
Click to expand...

Close the database; rename the old backend; open the frontend; and use
Tools... Utilities... Linked Table Manager to find the new backend.
(Don't just rename the new backend, that probably will leave the links
messed up - go through the manager).
 
I found this in the database "about microsoft access"
Microsoft Access 2002 (10.4302.4219) SP-2

Does that give you what you need to know?
Click to expand...

My apologies! I posted

It's actually the JET update that seems to be critical for this. Again
see the KB article at http://support.microsoft.com/?kbid=247408.

That should be kbid=257408 instead. Thanks to Tom Wickerath for
pointing out my fumblefinger!
 
Hi Rebecca,

A lot has been said in this thread since my last post!

How are you getting on? Need any further assistance?

ChrisM.
 
Thank you for correcting the link .. I kept getting sent to a spooling
problem and couldn't figure out what you wanted me to get from that
information that could help me.

Rebecca S.
 
Chris-
Thank you, I believe I got it fixed. I ended up making a new database,
importing the tables and then updating my linked tables using the linked
table manager. It seemed to work great. At least for now. :)
Thanks again for checking and seeing if I needed further assistance.

Rebecca
 
Back
Top