In a database how do you change an "auto number" column

[Lisa]

One of the tables in a database which someone created prior to my use and who
is no longer available to answer questions has an "auto number" column which
is now repeating numbers that it already used for other records. How do I
get it back on track so that it doesn't repeat because it will no longer safe
the new records that I am trying to add.

 

[Ray]

This is a bug in MS Access and not the programmer's fault. There is a known
bug, confirmed by Microsoft, that when you compact a database, some
autonumber fields get repeated, preventing you from adding records since the
autonumber field might be a primary key which cannot be duplicated.

You need to get the latest version of the Jet engine. Which version of
Access do you have? I currently have that problem and even with the correct
version of Jet, I still get that happening.

What I do is I keep adding records until the autonumber value is higher than
the maximum, then I delete the records I just added. Then I avoid compacting.
Arggg Microsoft!

 

[Lisa]

It is Microsoft Office Access 2003 part of Microsoft Office Professional
Edition 2003
I had already started adding records like you suggest, but there is over 200
records needed before it stops duplicating so I wanted to find out if there
was an easier way but I guess not.
What is this Jet engine that you speak of? I am a new user to Access and am
actually starting a class on it tomorrow night so forgive me if I seem
immature.
I also wanted to ask you if this can occur at anytime because I have been
using this database for almost 2 years now and never had this problem.
Thanks.

 

[Lisa]

Ray:

It is Microsoft Office Access 2003 part of Microsoft Office Professional
Edition 2003
I had already started adding records like you suggest, but there is over 200
records needed before it stops duplicating so I wanted to find out if there
was an easier way but I guess not.
What is this Jet engine that you speak of? I am a new user to Access and am
actually starting a class on it tomorrow night so forgive me if I seem
immature.
I also wanted to ask you if this can occur at anytime because I have been
using this database for almost 2 years now and never had this problem.
Thanks.

:

Click to show or hide original message or reply text.

 

[Ray]

Lisa, If you want more info on the bug, go to:
http://support.microsoft.com/kb/287756

I've been working with Access since version 1.0 and this never happened in
the past. It's a rare bug, and it only was apparent in Access 2000, 2002 and
2003. But even if you have these versions, it doesn't happen often.

The Jet engine is the storage mechanism used in Microsoft Access. Every once
in a while there's a new version out that fixes the problems of the previous
version. It's the layer that stands between your code and the data.

The good news is that you can often download (for free) a new version of the
Jet engine without having to buy a new version of MS Access, to solve some
problems like the one you have. Microsoft probably already has a new version
that solves the problem, see the link.

Ray

 

[Lisa]

Ray:

I thank you for all of your help and the answer to my questions. I have
fixed my problem using your suggestion. Have a wonderful day and take care!!

Lisa

 

[Mikal via AccessMonster.com]

Lisa:
I've been bitten by the bug, too. My solution is to reset the AutoNumber
field to replication id rather than incremental numbers. It takes more room,
but storage is getting cheaper all the time.
Mike

Ray:

I thank you for all of your help and the answer to my questions. I have
fixed my problem using your suggestion. Have a wonderful day and take care!!

Lisa

Lisa, If you want more info on the bug, go to:
http://support.microsoft.com/kb/287756

[quoted text clipped - 71 lines]

 

[Mikal via AccessMonster.com]

I just re-read what I wrote. DONT DO IT. Or if you do, do it on a copy of
your data. What I meant to say was use replication ID instead of incremental
numbers. If you change your autonumber field, access will let you do it but
will change the existing numbers also, although they are essentially the same
numbers, but padded with zeros. I haven't checked yet to see what effect
this will have on parent/child relationships.
Mike

Lisa:
I've been bitten by the bug, too. My solution is to reset the AutoNumber
field to replication id rather than incremental numbers. It takes more room,
but storage is getting cheaper all the time.
Mike

Ray:

[quoted text clipped - 8 lines]

 

[Mikal via AccessMonster.com]

OK. I tried actually changing the field to replication id with 2 related
tables. Access wouldn't let me do it unless I first deleted the relationship.
I'll go wipe the egg off my face now.
Mike

I just re-read what I wrote. DONT DO IT. Or if you do, do it on a copy of
your data. What I meant to say was use replication ID instead of incremental
numbers. If you change your autonumber field, access will let you do it but
will change the existing numbers also, although they are essentially the same
numbers, but padded with zeros. I haven't checked yet to see what effect
this will have on parent/child relationships.
Mike

Lisa:
I've been bitten by the bug, too. My solution is to reset the AutoNumber

[quoted text clipped - 7 lines]

 

[Lisa]

Mike:

Thank you for your suggestions but before I read your responses I actually
took Ray's advise and it worked. I still have to read up on this bug and
possibly update my Jet engine but I was able to get the autonumbers back on
track by adding and deleting records.
Peace, Love & Happiness,
Lisa

OK. I tried actually changing the field to replication id with 2 related
tables. Access wouldn't let me do it unless I first deleted the relationship.
I'll go wipe the egg off my face now.
Mike

I just re-read what I wrote. DONT DO IT. Or if you do, do it on a copy of
your data. What I meant to say was use replication ID instead of incremental
numbers. If you change your autonumber field, access will let you do it but
will change the existing numbers also, although they are essentially the same
numbers, but padded with zeros. I haven't checked yet to see what effect
this will have on parent/child relationships.
Mike

Lisa:
I've been bitten by the bug, too. My solution is to reset the AutoNumber

[quoted text clipped - 7 lines]

get it back on track so that it doesn't repeat because it will no longer safe
the new records that I am trying to add.