Solved

MSAccess auto number problem

Posted on 2014-01-23
2
651 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
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 34

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access Tables Linking 6 42
Update fields from multiple tables via same form 2 24
Replace NULL Value in Crosstab query with previous day data 6 31
Query design issue 2 22
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

920 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

16 Experts available now in Live!

Get 1:1 Help Now