Full Version: Auto Number Field Started Over
UtterAccess Forums > Microsoft® Access > Access Forms
HELP! All the sudden today my DB did something funky? One of my tables has accumulated 12,447 records, this field is my [WOID] field. It is set to AutoGen, Number, LongInt, PrimaryKey, Indexed - Yes (No Dupes). Today at 12,447 the [WOID] number field began counting over at 0?? I do also have a second table to where the records go when they are closed (tblclosedWorkOrder). So the number 1 thru 14 (record 14 is where I realized issue) have not been duplicated in my first table as those records have been transferred to the (tblClosedWorkOrder) table months ago. Can you please give me any leads as to where I have failed and enabled my DB to do this?
First things first, make a backup.

Then check your table design, it the WOID still an AutoNumber data type or has it changed?
Have you tried a compact and repair?

Take a look at Allen Browne's article: Fixing AutoNumbers when Access assigns negatives or duplicates, see if it helps any.

Is this a single-user or multi-user database?
If the latter:
  • Is it split?
  • Does each user have their own copy of the Front-End (not sharing a common copy)?

Are you running Service Pack 3?
I printed the info from Allen Brown......good reading, I'll keep that in my files. In this case I was lucky, the compact and repair resolved the issue!! Bless YOU!! I helped a friend today with an old 2000 version DB. I copied her DB and modified it on my computer, about 15 mins later I began to get calls from my guys that the MY 2007 DB began counting over at #1. Is this the possible link which made my DB lose it's place with the AutoNum? What do you think? Also, in answer to your first question, yes, in the table design the WOID field still showed Autonum, keyfield, index-yes, no dupes, but that's not how it was acting. After I ran the compact and repair, it immediately began counting right where it left off, the test work order I put in was 12,448, perfect! Other question you asked - It is a shared DB, and it is split. The FE is also a shared FE. Their FE is a shortcut on a shared file to the only "core" FE DB.....Phew, I'll say this one spooked me! THANKS AGAIN!
DO NOT share a common FE. Each user needs their own copy of the FE, and it should be a local copy for best performance with a persistent connection to the BE. The fact that they are sharing a common FE could be the reason for your current problem and could lead to much more problematic issues/corruption.
Just to add a bit to Daniel's advice.

You were VERY lucky, IMO. Sharing FE files among multiple people tends to be one of the quicker paths to corruption. Your Autonumber problem is a strong indication of corruption in the table.

Let me add my voice, therefore, to his advice that you make sure every user has their very own copy of your FE, and that it is located on their computer.

There's next to no chance that working on that other mdb is related to this problem, I think. Coincidence, I would think.
Okay thanks guys, I'll get on this shared FE DB first thing in the morning. Thanks for the direction.
I have such an issue ones and it seems to happen when there is no data in the original table i,e moved to the tblClosedOrder for example and the DBMS is restarted. then the autoIncrement would reset to one.
You also test it with your dbms to make sure and take suitable action.

I think what you are describing is expected behavior for EMPTY tables. That is, the next used Autonumber value is reset to one in such tables by doing a Compact & Repair on the database.

In this case, where the table is NOT empty, corruption in the Autonumber mechanism may be restored as described by doing a Compact & Repair, but that's somewhat different.

Moving records from one table to another isn't recommended when you need to keep the autonumber consistent between the two.
A much more logical approach would be to merge the two tables into one and have an extra field that indicates a status of "Open" or "Closed".
The behaviour you described will not stop and there isn't a 'workaround' as it is supposed and is required to work that way.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.