Adding records with old ids that were generated us

Question

I have an access database. In this I have a table which had an auto number field that created the ids. I somehow deleted these ids in the table. Now that I have deleted these records, I need a same sequence to continue (I want the deleted ids back in the table), but I am not able to do so because the auto number feature deleted the ids forever. I tried changing the field datatype to just number and entering the ids manually, but it won't change because the database gives me a warning saying that it is related to other features and I need to delete them first, something to do with relations. Please help me figure out a way to get the old ids in the table. The database is pretty complex, it doesn't just have one table, it has a lot of forms and reports and they are all intertwined. HELP!

Answer 1

Access will allow you to execute an INSERT statement which adds a row with an unused autonumber value.

INSERT INTO MyTable (auto_num_field, text_field)
VALUES (27, 'foo');

However if Access objects due to a defined relationship, you need to first drop the relationship, add the data, do whatever else is needed to satisfy the relationship constraint, and finally re-create the relationship.

OTOH, if you've already changed the field's data type from autonumber to something else, and you don't have a backup copy of the table, this could be even more challenging. We'd need more information to figure out a solution.