Reinstate deleted autonumbered rows

[Brian Hutchings]

I have inadvertently deleted some autonumbered rows. Is it possible to replace the rows and insert the deleted number

 

[smozgur]

Hi Brian,

I don't know if this would help but if that AutoNumber field is not LINKED to another table FIELD (relation) then you can :

Goto table design
Select AutoNumber field and delete it (it will warn two times)
And without quitting table design, goto first field and select Insert_Rows.
Change the field name as you need (as old one probably) and also set Data Type to AutoNumber then quit table design by saving.

This will change field values from 1 to record count.

If that field is related by another table field then you should first remove the relation but this will cause data inconsistency.

I hope this helps.
Suat

 

[smozgur]

If you want to create missing rows (records) without data (or with) but with AutoNumber field and don't want to change the current records' autonumber field values :

Sample:
Current Table

ID Field1 Field2
1 10 50
3 20 60
7 30 70

And you want to get:

ID Field1 Field2
1 10 50
2
3 20 60
4
5
6
7 30 70

(My previous suggestion makes it like below:
ID Field1 Field2
1 10 50
2 20 60
3 30 70)

then:

Please backup your original database file before everything.

(My sample table name is "mytable")

Copy mytable as a new table into same database (Edit Copy/Paste)
Rename new table as "tmptable"
Open mytable and remove all data (delete all)
**YOU DO NOTHING IN MAIN TABLE STRUCTURE, ONLY DELETE ALL DATA AFTER COPYING IT AS NEW TABLE**
Open tmptable in design view and remove Primary Key property (DO NOT DELETE field, just remove Primary Key property) and change AutoNumber data type to Number.
Save tmptable and goto open it in data view and enter the missing fields (just missing numbers into related field or if you need, all other fields data with it)

Before entering data
ID Field1 Field2
1 10 50
3 20 60
7 30 70

After entering missing data
ID Field1 Field2
1 10 50
3 20 60
7 30 70
2
4
5
6

Close tmptable
Goto Queries and New.
When you get query design view do NOT select any table and close Show Table window.
Select View_SQL view in main menu (you will see SQL code with SELECT; only. Delete it.)
Paste the following SQL string into this SQL view.

INSERT INTO myTable SELECT * FROM tmptable;

(Make sure you changed myTable and tmptable table names in the SQL string above with yours)

Point to Query_Run in main menu. See what happened in main table.

This is what I would do when I accidentially delete records from a table has AutoNumber field and I need deleted autonumber fields back and also consistency for saved records. And I don't know if easier way exists

Suat

 

[Brian Hutchings]

Smozgur, many thanks for the replies, especially the second one which has worked fine. The only problem now is that I am unable to resume the Autonumber sequence. Do you have a 'fix' for this also?

Regards, Brian

 

[smozgur]

Hi Brian,

Do you mean it doesn't act as an Autonumber field after applying second suggestion ?

Suat

 

[Brian Hutchings]

Smozgur, thats correct. I can add the deleted numbers as per your second suggestion but when I try to reinstate the Autonumber in design view, I get a message "Once you enter data in a table, you can't change the data type in any field to Autonumber, even if you haven't yet added data to that field. Add a new field to the table,and define its data type as Autonumber. Microsoft Access then enters data in the Autonumber field automatically, numbering the records cosecutively starting ith 1" This would be a problen as the database does not start at 1. I need the numbers to match exactly with the exising data. Hope you can help, Regards, Brian

 

[smozgur]

Brian,

When you are using my second suggestion, you should NOT do any changing in main table design, just need to delete all data after creating it copy. It is in my comment also:

**YOU DO NOTHING IN MAIN TABLE STRUCTURE, ONLY DELETE ALL DATA AFTER COPYING IT AS NEW TABLE**

You will only change the copied table's structure (primary key and autonumber type). After you followed all steps in my second suggestion then data will be loaded into main table.

Suat