Solved

MSAccess auto number problem

Posted on 2014-01-23
2
670 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 38

Accepted Solution

by:
PatHartman earned 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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