Need to restore deleted records in autonumbered db

[gerrisp]

I am self taught access user... not a programmer. I have an autonumbered database in which 6 or so records were inadvertantly deleted today. I need to restore those numbers that are in different places in the database. I am thinking I can run an append query to add the numbers back in sequential order, then re-enter the data manually for each deleted record. Is there an easy way to do this?

[June7]

You cannot recover lost Autonumbers with an Append query. That is the nature of Autonumber field. The appended records will be assigned a new number by the Autonumber. Can you set up Relationships with data integrity conditions to prevent this in the future?

If you must recover those numbers, there is a process, but it is complex and risky. How many related records in how many tables are affected? Might be easier to manually fix them with search & replace.

[gerrisp]

It is 6 records in one table... how would I search and replace?

[boblarson]

If you are using an Autonumber and you care that they are in order then you are not using the autonumber correctly. An Autonumber should only be used if you

1. Do not care about the order.
2. Do not care if it goes into the negatives (not always positive).
3. It has no meaning other than being used as a unique identifier.
4. Do not care if it starts going random.

Otherwise, you should use your own method of creating numbers (do a search on DMAX + 1 to get some ideas).

As to how to restore these in the current table, you can do this:

1. Copy the table and, when pasting, select Structure Only.
2. Go into that table and create a new field as NUMBER / Long Integer.
3. Create an append query to append the data from the old table to the new and append the autonumber field to the new number/long integer field. The new table will not yet have an autonumber field.
4. Then you need to go into that new table and enter the 6 records that are no longer in the original table. Make sure to type in the appropriate number for them in the new field.
5. Then you run a delete query to delete ALL of the records from the original table.
6. Do a Compact And Repair which will reset the now empty original table.
7. Once that is done, create an append query (sort on the new field and set that field to append into the autonumber field on the originial table and set all of the other fields to their appropriate field to append to.
8. Run that append query and it should be back in business.

[gerrisp]

Thanks Bob - I will try this... re: autonumber field - we use it to have 4 or so people able to assign unique, sequential numbers to newly opened "jobs". I use auto number so that 2 people don't assign the same (sequential) number as we all "open jobs" at the same time. I don't understand why someone would use an autonumber field if they didn't care about order... what is an alternative? If you don't have time to answer, I will check out your suggestion to do a search on DMAX.

Thanks

[boblarson]

Thanks Bob - I will try this... re: autonumber field - we use it to have 4 or so people able to assign unique, sequential numbers to newly opened "jobs".

The problem with that is that autonumbers are ONLY supposed to guarantee that you get a unique number. While the default is set to give you an incremented number, it doesn't mean that will always work like that.

I use auto number so that 2 people don't assign the same (sequential) number as we all "open jobs" at the same time.

So, if someone cancels then the autonumber is lost forever.

I don't understand why someone would use an autonumber field if they didn't care about order

Because mostly it is just used as a "behind-the-scenes" surrogate key so that a record has a UNIQUE value to identify it with. They are commonly misused as something that people see and that is okay as long as it doesn't matter what it is or in what order it is. It just needs to be unique.

... what is an alternative? If you don't have time to answer, I will check out your suggestion to do a search on DMAX.

You would probably want to use a combination of the DMax+1 and a single table, single record for being able to lock that number for a specific user (I will try to find a link to something that explains that more) so that you don't end up with two people trying to use the same values. You would want to assign it at the very last moment before the record is saved and not at the beginning where the autonumber does.

[gerrisp]

Because mostly it is just used as a "behind-the-scenes" surrogate key so that a record has a UNIQUE value to identify it with. They are commonly misused as something that people see and that is okay as long as it doesn't matter what it is or in what order it is. It just needs to be unique

.

That's exactly how we use it... and you're right - it doesn't matter to me that my numbers are sequential just that they are unique. However if the autonumber suddenly skipped ahead 1000 records, that would be annoying... is that possible?

I will be interested to see the DMAX +1 alternative, but hope it's in laymen's lanquage!

[boblarson]

It is completely within the Autonumber's ability to suddenly skip a bunch of numbers. That's why we don't want to rely on it for incremental numbers. This is an issue which has plagued Access from the beginning (or at least from Access 97 and on that I know of). That can be a problem since it usually takes a catastrophic event for someone who isn't really experienced with Access to learn. It would be nice if we could catch all about that, especially those with not enough experience, BEFORE they started using it for the wrong purposes. We (MVP's) have suggested some changes to the help file entry for Autonumbers, but only time will tell if we get any results.

[June7]

My approach is to generate the unique identifier and immediately save the record, then open form filtered to this new record so user can enter other data. I do this because they need that unique ID for notation on submitted documents. Review this thread http://forums.aspfree.com/microsoft-...ta-403208.html

[boblarson]

My approach is to generate the unique identifier and immediately save the record, then open form filtered to this new record so user can enter other data.

That's fine - but what if they want to cancel the record? Isn't the number gone at that point? If gaps are okay, then no problem. If gaps aren't okay, then "potential problem", no?

[June7]

I have process to allow user to cancel data entry but the number is reused for the next sample login. Also have a process to VOID a record (sample was logged in but decided not to produce a report) which is documented. Every number is accounted for.

[gerrisp]

1. Copy the table and, when pasting, select Structure Only.
2. Go into that table and create a new field as NUMBER / Long Integer.
3. Create an append query to append the data from the old table to the new and append the autonumber field to the new number/long integer field. The new table will not yet have an autonumber field.
4. Then you need to go into that new table and enter the 6 records that are no longer in the original table. Make sure to type in the appropriate number for them in the new field.
5. Then you run a delete query to delete ALL of the records from the original table.
6. Do a Compact And Repair which will reset the now empty original table.
7. Once that is done, create an append query (sort on the new field and set that field to append into the autonumber field on the originial table and set all of the other fields to their appropriate field to append to.
8. Run that append query and it should be back in business.

Bob - In step 7... am I to create another append query or run the append query I created in step #3?

[boblarson]

Sorry, it is two different. One is to move data to a new table and the second (step 7) is to move it back.

[June7]

Didn't I say complex and risky?!

[boblarson]

Didn't I say complex and risky?!

Yep, you sure did.