By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,530 Members | 880 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,530 IT Pros & Developers. It's quick & easy.

Reset Autonumber when trying to keep Table data

mkremkow
P: 5
Access 2003 on XP

Someone here at work deleted a record (&$%^&*&!!!) and screwed up the Autonumber "Job ID" field . I need to reset the Autonumber field back to it's original numbers and still keep all the data in the table assigned to the correct Job ID (1-155).

I copied everything from the table to Excel for safekeeping. I deleted the Autonumber field and went through the steps (http://support.microsoft.com/kb/812718 ) but when I reinsert the Autonumber Job ID field, it reorders everything and the wrong number gets assigned.

I tried again by importing from the Excel file into a new Access table but the reports, queries and forms all have huge association errors. It won't let me "copy" the data from Excel into the old Access table.

What do I do?

Thanks, Marjo
May 8 '07 #1
Share this Question
Share on Google+
2 Replies


JConsulting
Expert 100+
P: 603
Access 2003 on XP

Someone here at work deleted a record (&$%^&*&!!!) and screwed up the Autonumber "Job ID" field . I need to reset the Autonumber field back to it's original numbers and still keep all the data in the table assigned to the correct Job ID (1-155).

I copied everything from the table to Excel for safekeeping. I deleted the Autonumber field and went through the steps (http://support.microsoft.com/kb/812718 ) but when I reinsert the Autonumber Job ID field, it reorders everything and the wrong number gets assigned.

I tried again by importing from the Excel file into a new Access table but the reports, queries and forms all have huge association errors. It won't let me "copy" the data from Excel into the old Access table.

What do I do?

Thanks, Marjo

Hopefully you still have a copy of your table in tact (one where you haven't deleted the autonumber)

Copy that table...paste it in as a new table, structure only.

Open it in design view and delete the autonumber field.

Add a new one...same name.

Now you have a new table ready to fill up

Create a query that takes the records sorted by the old auto number field and append them into the new table. Do not include the autonumber fields in the query grid.

Run the append query.

Rename your tables accordingly.

J
May 8 '07 #2

NeoPa
Expert Mod 15k+
P: 31,419
Another way is to :
  1. Remove all the data (Store safely for later re-input).
  2. Compact and Repair the database (Autonumber now reset).
  3. Put the data back in from your storage.
May 11 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.