Friday, January 04, 2013

The AutoNumber "goes crazy" fix

There are posts everywhere about Autonumbers misbehaving in Access 2007 and Access 2010. Most post end up pointing to a solution on Allen Brownes great website

Unfortunately there are problems with that solution (and if I am wrong about that sorry).

1) It doesn't work in Access 2010 but seems to work.
2) It fixes the random number seed but eventually the problem returns.

There is only one solution and its a manual fix.

Open the table in design view. Find the auto-number field.  Make it the primary key. Look at the sort on the primary key. It must be Ascending. 

Do not remove the Primary key from the autonumber key, no matter how you want that table to behave.

What I think is going on

At some stage, someone in Microsoft decided that when a database is compacted, they could look at the very last record in the table and then look at the autonumber field and save that number as the seed.  Then when the next record is added, it looks at the seed, adds one and then updates the seed.  All fine if the table is sorted correctly.  I think the bug crept in because Microsoft templates all have autonumbers as the primary key in each table. Therefore they wouldn't have noticed the bug.  For people who are getting an error like "Autonumber already exists", its because the seed is lower than the highest autonumber in the database and eventually it hits a record that already exists.

I hope I am wrong, this is totally unnecessary bug that should be fixed in the next service pack. It has caused problems in at least 3 of the databases we are maintaining.

We have this bug in Access 2000 databases but we are running them with Access 2010 now.

I do not want anyone to lecture me about primary key design or autonumbers but if you know anything about house paint colours and kitchen design, happy to chat.

Here is an article that shows you how to change (primary key) indexes

Garry Robinson
Access MVP 2006-2013

4 comments:

Garry Robinson said...

Hey Garry,

Thanks for taking the time to let me know.

Your blog post is very timely because I recently discovered the relationship between the Compact/Repair option and the screwy auto number. I just didn't know about the possible relationship between the auto number and it not being a primary key. That makes sense at matches my scenario perfectly.

Your solution will provide a quick fix for my application while my long-term solution will be to move away from auto numbers and convert completely over to GUIDs.

I removed my web page and in its place, I put a 301 Moved Permanently notice that directs future visitors to your blog post.

Thanks again!
Mark

Garry Robinson said...

I have tested out Garry’s resolution to the 2010 auto number bug and it works.
Scott

pacstar said...

The problem does also occur when you append a record back into a table with a primary key that was lower than the last number. When this occurs for some reason access thinks the next number is the new value when it is not.

Nasr Doss said...

Here is another solution that worked for me:
1- Change the Autonumber to Number Long Integer
2- Create a BEFORE CHANGE data macro on the table as follows:

[YourFieldName]
DMax("[YourFieldName]","YourTableName")+1

It is working perfect and I don't think there is any chance for errors since this is handled at the table's level not at the user form level
I hope that will help someone
Thanks