Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MSAccess auto number problem

Posted on 2014-01-23
2
Medium Priority
?
689 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 85
ID: 39803328
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 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39804435
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

972 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