473,320 Members | 1,814 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Reset Autonumber when trying to keep Table data

mkremkow
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
2 2800
JConsulting
603 Expert 512MB
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
32,556 Expert Mod 16PB
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

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

Similar topics

12
by: deko | last post by:
Is there a way to reset the AutoNumber sequence? I have several tables that use the AutoNumber field as the Primary Key, and I'd like to somehow do an Import/Export that will make remove the...
33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
3
by: Cillies | last post by:
Hello, I was wondering if anyone knew how to reset an auto number. I.E. in my database I have two tables with autonumbers as primary keys. So I was wondering if anyone knew an easy way of...
35
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
2
by: N. Graves | last post by:
Hey is there a command like in SQL Transactional that will reset the Autonumber field to the highest number in the found in the field? I developing a access database with a several table that...
16
by: John Baker | last post by:
Hi: I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number...
4
by: Danny | last post by:
I have a db that has a table that is never deleted but just has records cleaned out "delete * from table" but each time it is populated, the autonumber is not reset to 0 and picks up where it...
3
by: trueblue7 | last post by:
I apologize if this question has been asked before... I'm trying to reset autonumber fields back to 1. The autonumber fields are part of the composite primary keys. I followed the MS help with...
2
by: junkaccount | last post by:
Hello, I currently have a field named QuoteNumber in a table named Quotes. The field is set as autonumber and is used to assign sequential numbers as users enter information in the table through...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.