Autonumber stopped incrementing to next highest number

[lalexander]

I have been having this strange occurence on one of my relationship table -
not frequently but 2 times is enough for me.

Apparently the Autonumber seems to stop incrementing to the next available
highest number and begin to give a number
that's already in existence . That of course will give the error on "Unique
identifier - blah blah blah"

To temporary correct this problem I have been copying the table to a
temptable ( structure & data ) -

rename the corrupted table and then rename the temptable to the original
name of the corrupted table.

This works, but I'm looking for a permanent fix to this problem.

I'm using Access 2003 with SPK2. Have been using this table for almost 2
years without a hitch.

Any help would be appreciated.

 

[Maurice]

Maybe as a last resort. Have you tried creating a new autonumber field. I
don't know how critcal the key is but if it's just a regular sequential
number you could try it.

But before attempting be sure to Backup first...

Maurice

 

[Amy Blankenship]

I've seen this problem as well, and the only fix I found to it is to create
a new db and import all tables, forms, reports, etc. into it.

HTH;

Amy

 

[lalexander]

The numbers that Autonumber generates are used in a relationship - so it's
important that cannot renumber those records.

if I copy to a new structure with its data then the table is fine and the
numbering continues from the highest number.

About 10 minutes ago a user told me that the table is again indicating of a
record violations - which to me means that
the autonumber has reverted to creating records previously used.


Any help in this matter would be appreciated

 

[lalexander]

Creating a new db would entails starting the autonumber sequence from #1 (
and not from the highest number key number in the table)

You see I use those ID numbers as a relationship to another table , so its
critical that I preserve those numbers.

 

[Amy Blankenship]

Creating a new db would entails starting the autonumber sequence from #1
( and not from the highest number key number in the table)

You see I use those ID numbers as a relationship to another table , so its
critical that I preserve those numbers.

If you import the tables and all the data, the ID's will be preserved...

 

[Guest]

In the current(?) version of Jet, appending a new value to
an autonumber field on a linked table resets the autonumber seed.

If that's not the problem you are seeing, you probably
have an older version.

You can reset the seed using this ADOX code:
http://allenbrowne.com/ser-40.html
or this
http://support.microsoft.com/kb/287756

or by doing another append, or by using DDL:

strSQL = "ALTER TABLE tblUICounterDataTypes
ALTER COLUMN Field1 COUNTER(10,10)"
application.currentproject.execute strSQL

(david)

 

[Phil]

If the problem is that access is trying to use autonumbers that are already
in use, then a compact and repair should sort this out, however if you are
trying to use autonumbers as something such as customer number they were
never meant for this and will not be incremental e.g. 1,2,3,4 as you will end
up with gaps e.g. 1,4,7,43 and sometimes negative numbers -1,2,34

hth

Phil

 

[Guest]

in use, then a compact and repair should sort this out,

If you are using Jet 3.5 then a compact/repair will fix up
autonumbers. On the other hand, if you were using Jet 3.5,
you wouldn't have problems with autonumbers in the first place.

In the current version Jet 4.0/DA0 3.6, Access 2K+,
compact/repair does not reset/fix autonumbers.

(dav)

 

[Phil]

Thanks for this info, i always thought it did,

thanks

Phil

 

[Guest]

Compact/Repair can still be used to reset
an empty table to zero

(david)

 

[Phil]

Thanks, you must have read my mind, that is exactly why I thought it would work

Thanks again

Phil