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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just what I needed, thanks!
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
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
ASKER
Flyster's answer was the first and worked. Pat Hartman and Joe Anderson provided solutions that I appreciate as well. Thank you all!
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.
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.
ASKER
Thanks Pat.
One of my clients had a similar problem and the solution proposed by Pat worked perfectly and has the immense advantage of simplicity.
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.FacilityS
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#;