I Deleted All Records in an Access Table - Hopes of Recovering from within temp tables of Database?? 1

[kjv1611]

This is really embarrassing, and I'm frankly baffled at how it happened.

I was trying to create the copy of the structure of a table containing just over 4,000 records, and it instead created the structure and data, b/c I forgot to change the selection. Well, I thought no big deal, I'll just delete all the records in this copy of the table. Well, somehow, how I have no idea, it deleted on one command (manual - not vba, query, or anything like that) - all records in the original table as well as my copy.

So now I've got a file recovery request in, and won't get that until 2pm tomorrow. Is there any way I can find the now deleted records in a temp table? I already asked everyone to back out of the database.

I have some backups, but not really recent. However, I just remembered I do at least have an export in Excel that has all the data I think, so I may be in okay shape.

So, my question is - does anyone know of an easy - maybe VBA - method of recovering deleted records?

Thanks for any suggestions/thoughts. I'll have to work on a Plan B, and C in the meantime.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

Have you closed the db already? If not do not, and do not compact repair. I would assume you have.

 

[MajP]

Sorry I thought the table was deleted, but you deleted the records in the table.

 

[MajP]

Here is a good explanation why

Answer: No, not really. Although the space used to store the deleted records remains unchanged, the actual contents of that storage are not just deleted, but duplicated in part. Typically, only about one out of every twenty records is recoverable, for technical reasons. Basically, although the undeleting is easy enough, Access/Jet copy the first deleted record on a page over the remaining deleted records on hat page. Typically there are twenty or more records per page, and hence the 95%+ data loss.

That said, if the table had been deleted, rather than just its records, it would indeed be recoverable (under limited conditions). In fact, if all tables had been deleted, they would all be recoverable. But records deleted from a table that is not deleted are indeed toast, or at least predominantly so.

 

[kjv1611]

Wow. On the surface that sounds crazy - that tables, but not records, can be recovered, but I guess it makes sense. It'd be very resource intensive by comparison to create 2 copies of each and every record than to maintain a copy of the table as an object. Thanks for replying, though. That is what I expected, but wasn't certain. I was hoping there was some method I had never heard of to get to the data. But to answer your first question - yes, I had already closed the database, not compacted and repaired, but had closed it. Good to know for future reference though - DO NOT CLOSE IF ACCIDENTALLY DELETE A TABLE. [THUMBSUP2]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

Unfortunately if you google you will see the code to restore a deleted table. This gives people some false hope, because as soon as you close the database you are done, which most people would likely do. Hopefully you have the data in the Excel file.

 

[kjv1611]

Yep, I found I have all but a few fields, and those are arguably not important. Also they are generally repetitive values. And, since I've got a data recovery coming where I can get that tomorrow around 2pm, I'll be able to populate the vast majority of that field from the backup, and likely even populate the newer records via a query since it's repetitive, and based off other data in general. So it's at least no where near as bad as it could have been.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

One last comment here for anyone reading. I do typically create backups before making major changes. This time, I was not expecting, not in a million years, the event to occur. I was merely trying to delete records that were created on accident.

Here's how it all happened:
[ol 1]
[li]I was going to delete yet another record based on one of the users' suggestions. I thought you know it'd be good to have a "deleted items" table in case we later find out - oh, maybe we shouldn't have deleted that. I figured easiest way was to create a copy of the STRUCTURE of the existing table.[/li]
[li]I hit copy, paste, gave it a name and hit [Enter] - I forgot to change the selection from structure + data to structure only.[/li]
[li]After it completed, I thought, okay, I don't need ALL the records in the new table, just the ones I'm getting from a delete request. So I proceeded to select all records (in the new table - original table was open, but in the background), right-click, and selected delete all records. It said are you sure, you're about to delete x # of records which sounded correct, and I said ok.[/li]
[li]I then pasted one row to the newly empty table (again, just one paste - one table "active", since Access, as far as I know only allows one "actively selected" table at a time.[/li]
[li]I looked right after that, and noticed both tables were now empty except for the one pasted record which needed to be deleted anyway. [angry][/li]
[li][/li]
[/ol]

So, now I've learned my lesson. EVEN when the event occurred b/c of a mistake, STILL backup before doing anything like a mass deletion!!


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Darrylles]

kj,

Yep, any test code that can remotely inconvenience me if records are lost - I duplicate the table(s) first.

5 second job to create, 5 second job to remove the duplicate(s) after.

(Only becomes automatic after you've failed 10 times as above though (slow learners us coders ain't we?)).

Darrylle ;-)




Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[kjv1611]

Darrylles,

True. Of course, you do realize this thread has sat before this for nearly 5 months, right? Well, maybe you didn't notice the date. I've done the same a time or two.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Oh by the way.. ALL Access tables aren't easily duplicated or even deleted in 5 seconds or less. Small ones are instantaneous, but large ones... well, it can be a while.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57