Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSAccess auto number problem

Posted on 2014-01-23
2
Medium Priority
?
682 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 39

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 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