Solved

MSAccess auto number problem

Posted on 2014-01-23
2
644 Views
Last Modified: 2014-04-04
I have a table I have been using for years.  It has a field named JobID that auto numbers.  The last time I used it it jumped form 8092 to an 8 digit number.  I can also no longer copy or "save as" the table.  I get reserved error -1524 whenever I try to do this.

Can anyone explain this problem or give me a workaround?
0
Comment
Question by:Nyackflash
2 Comments
 
LVL 84
Comment Utility
Most likely answer is corruption, but there have been known issues with the AutoNumber field. Most have been corrected in the ACE upgrade (i.e. moving to the .accdb format instead of the .mdb format).

First, make a backup of your database.

Next, try to Compact your database. How you do this depends on the version of Access you're using. In 2010, it's on the File menu (i.e. the "backstage" menu). Click Compact, and hopefully all will complete. If you're using 2007, it's on the "Office" button - click that, then Manage, and you should see the Compact option. Earlier versions had this on the Database Tools menu option.

If the Compact works, you might also consider moving everything to a new, blank database. To do that, create your new database then Import everything from the old db into the new one.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
You will also need to reset the seed.  Hopefully you won't have too many records that need fixing.

As always start by backing up the database.  Two copies wouldn't hurt and verify them also since you will be deleting an entire table during this process.  You really don't want to loose it.

1. create a make-table query to copy all the data to a temporary table.
2. replace the large ids with numbers in the correct sequence.
3. delete all the data from the original table.
4. delete the autonumber PK and save the table.
5. add the autonumber PK field back.  This is going to ensure that the seed resets.
6. compact and repair.  This used to always reset the seed but it doesn't any more.
7. create an append query to copy the data from the temp table and put it back in the original table.  Be sure to include the original autonumber field and append it to the new autonumber field.  This is the ONLY situation in which you can populate manually an autonumber field.
8. compact and repair again.

Now, that was relatively easy.  If you have RI that you need to deal with, it will be somewhat more complex since you will have to disable it at the beginning.  You will then have to actually keep the original autonumber column instead of deleting it but change its type to number because you can't have two autonumber columns.  Then after step 7, you would need to fix up any foreign keys by joining based on the old autonumber and then updating to the new autonumber.  And then you would have to reestablish RI before doing the final compact and repair.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now