×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread700-1732060

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

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

(OP)
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

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

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

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

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

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

Here is a good explanation why

Quote:

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.

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

(OP)
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

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

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.

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

(OP)
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. blush

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

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

(OP)
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:
  1. 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.
  2. I hit copy, paste, gave it a name and hit [Enter] - I forgot to change the selection from structure + data to structure only.
  3. 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.
  4. 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.
  5. 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]
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

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

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 winky smile


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

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

(OP)
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

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close