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

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

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

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.

Click to expand...

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

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.

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

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

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.

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

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