• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3995
  • Last Modified:

Restore deleted record with an autonumber field

A single record containing an auto-number field was accidentally deleted from Access 2010 table.  I need to replace it.  How do I give the replacement record the original (auto generated) number.
0
John Kincaid
Asked:
John Kincaid
1 Solution
 
FlysterCommented:
You can accomplish this by doing the following:

1. Open your table in design view
2. Change ID type from Autonumber to number
3. Open table and enter new data with deleted number
4. Close table, copy it and paste it structure only
5. Change new table ID field from number to Autonumber
6. Copy original table and paste it "Append Data to Existing Table" to new table
7. Delete old table and rename the new one

Flyster
0
 
PatHartmanCommented:
The easier way is to use an append query.

An Append query is the ONLY way you can supply an autonumber value.  If you have the record in another table, create a query that selects it.  Then change the query to an append query.  Make sure you include the original autonumber value and map it to the autonumber field in the append.

There is no need to modify the table.  

Sample 1:
INSERT INTO tblFacilityStays ( FacilityStayID, ClientID, FacilityID, StartDT, EndDT, Reason, ChangeBy, ChangeDT, CreateDT )
SELECT tblFacilityStays.FacilityStayID, tblFacilityStays.ClientID, tblFacilityStays.FacilityID, tblFacilityStays.StartDT, tblFacilityStays.EndDT, tblFacilityStays.Reason, tblFacilityStays.ChangeBy, tblFacilityStays.ChangeDT, tblFacilityStays.CreateDT
FROM tblFacilityStays;

Sample 2 - with values instead of select:
INSERT INTO tblFacilityStays ( FacilityStayID, ClientID, FacilityID, StartDT, EndDT, Reason, ChangeBy, ChangeDT, CreateDT )
Values (123, 101010, 30202, #4/1/14#, #4/5/14#, "Recover from surgery.", "Pat", #4/8/14#, #4/1/14#;
0
 
John KincaidAuthor Commented:
Just what I needed, thanks!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can re-insert the missing AND AutoNumber by using this example:

INSERT INTO tblEmp ( EmpID, EmpName )
SELECT 20 AS AN, "SomeData" AS EmpName;

This examples assumes the missing AutoNumber is 20 - a gap in the AutoNumber sequence.

mx
0
 
John KincaidAuthor Commented:
Flyster's answer was the first and worked.  Pat Hartman and Joe Anderson provided solutions that I appreciate as well.  Thank you all!
0
 
PatHartmanCommented:
It may have worked but I suggest that you use the Append method in the future.  Flyster's method will not work if you have relationships defined so you would need to delete them first, increasing the opportunities to mess up your data.

FYI, SQL Server and other RDBMS' also allow appending rows with existing identity columns.  For other databases though, you may have to turn on a flag at the table level that tells the engine to allow the addition of PK values.  And of course, in all cases, the append would be rejected if the key value duplicated an existing value.

PS - I hope your first step was to back up the database.
0
 
John KincaidAuthor Commented:
Thanks Pat.
0
 
EpsimsoCommented:
One of my clients had a similar problem and the solution proposed by Pat worked perfectly and has the immense advantage of simplicity.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now