|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Disaster Recovery! By Richard Rost 
4 years ago
How to Fix Your Data When You Mess Up Records In this video, I'm going to show you how to fix your database when you accidentally mess up a whole bunch of records by running an incredibly stupid update query. Yeah, I goofed. I made an oops while updating my database this morning. Fortunately, I had good backups. This video will explain what I did, what happened, and how I fixed it. Learn from my mistakes! Links
Suggested Courses
What the #@¢& Happened?I was working on my database that runs my business. My memberships are processed automatically every night, but once in a while I get a request from a customer to send them their next class right away. So I have a button in my database that does this with one click. I wrote the code for this button probably around 10 years ago. It used a lot of commands to open forms and move between various fields and records. OpenForm, GotoControl, GotoRecord, and other commands would create a new order on the open order form, set a field, move to the next record, and so on. That's how I preferred doing it at the time, and it works, but once in a while it can glitch. So I rewrote the whole thing using SQL statements and recordset loops, like you properly should. When it came time to update the totals for the order, I made a classic mistake. Can you see what it is? This code is supposed to update the merchandise total, adjustments (discount), and order total for the current order. See the mistake? See it now? I forgot my WHERE clause. So when I ran this to test it for the first time, the first statement took forever. So long that I killed Access because I thought the database had locked up and I had an endless loop somewhere. Nope. The SQL statement was busy updating every order in the table to the same price. Oops! I went in to check out my order table, and yep. Wouldn't you know it. Every order had its MerchandiseTotal field set to $5.99. I killed Access before it changed the other fields, but still. The damage is done. Now, fortunately I keep very good backups. If you've been watching my videos for any length of time, you know I preach backup, backup, backup, and then backup again. If you want to learn more about automatically backing up your database daily, go watch my free TechHelp video on backups. I'll include a link you can click on in the description down below the video. https://599cd.com/Backup Checking my backup folder, the backup routine ran at 4:10am this morning, as it does every morning. So now, all I have to do is restore the OrderT table from my last good backup, and then copy over the records that have been added since that backup was made. It looks like the last OrderID in the backup table is 229714. Everything after that came in after the database file was backed up. So, I need to copy over 229715 and later. However, there's a problem. I can't just copy these orders over from table to table because there are related records in the OrderDetailT that depend on these OrderIDs. If I just copy them, I'll get assigned all new OrderIDs. So, in order to copy them over, I'll have to use an Append Query. This will copy over the records and keep the same IDs from the source table to the destination table, assuming there are no duplicates. I have a whole video on how to do this. Again, I'll put a link down below you can click on. https://599cd.com/RestoreAutoNumber And if you need to learn about Append Queries, here's another free video. https://599cd.com/Append So the first step was to restore the good backup of the OrderT. Fortunately that was the only table that was affected. Next, delete all of the records from the corrupted database file that are already in the good backup. So basically delete everything before OrderID 229715 because those are in the good backup. Import the corrupted OrderT into the database file that you restored from backup. We'll call it NewOrderT. If you don't know how to import a table, I cover that in Access Expert 21. It's pretty straightforward. Now we'll create a Delete Query to delete all of the records with an OrderID less than 229715 which is all of the orders that are in the good backup table. If you don't know how to use a Delete Query, I cover that in Access Expert 13. Now the NewOrderT has only the new orders that came in since the backup. Now it's time to move those over to the OrderT, but again, we can't just copy and paste them because of the AutoNumbers, so we have to set up an append query just like I mentioned earlier. Now we're left with a good table of all the orders that came in before the backup this morning, and a table with all of the new orders that came in since that point, but they have the wrong dollar amounts. But at least at this point, we're ready for the append query. So it's time to join them. Append all of the records from NewOrderT into OrderT. Run the query. Now check the results. Open up the OrderT. You can see that you have all of the orders now in one table. Everything from 229714 and before is correct because those records were from your backup. Everything from 229715 and after has been copied in from the corrupted table. You have the correct IDs so your relationships to the other tables are OK, however you still have the problem of having to manually correct those merchandise totals. Unfortunately, there's not much you can do about that. Some manual data entry may be required. That's why it's important to keep frequent good backups. Fortunately for me, whenever an order is processed, my system emails a copy of the invoice to the customer. That invoice includes most of the relevant order data: customer name, email, courses purchased, order total, etc. It doesn't include everything (like their credit card number) but it's enough to where I can recreate the order in my system in case something like this goes wrong. I use Gmail, so everything is backed up, forever. I love Gmail. I also keep a backup log of every order that's submitted on my web server, just in case. Again, another way I can recreate an order if needed. Again, this log doesn't hold credit card data, but the orders have already been processed, so that's not a problem. So there you go. There is how I managed my terribly stupid update query goof. That's what happened, how I fixed it, and how you can recover from pretty much any disaster if you make good, frequent backups.
Keywordsaccess 2021, access 2016, access 2019, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, disaster recovery, restore from backups, backups, restore, replacing autonumbers, append query, Disaster Recovery Plan, Backup and Disaster Recovery, Resiliency and continuity, Protect your data
IntroIn this video, we'll talk about disaster recovery in Microsoft Access and go through how to fix a database after accidentally updating the wrong records due to a missing WHERE clause in an update query. I'll cover the importance of keeping regular backups, show you how to restore corrupted tables, copy data from a damaged table to a backup table while preserving autonumbers using an append query, and discuss some extra ways to recover lost order details. This Fast Tip focuses on practical steps you can take to recover your data and avoid major headaches.TranscriptWelcome to another Fast Tip video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.In today's tip, we're going to talk about disaster recovery - how to fix your data when, like me, you accidentally mess up a bunch of records in your Microsoft Access Database. First, some background. I was working on my database that runs my whole business. My memberships are processed automatically every night, but once in a while I get a request from a student to send them their next class right away. I have a button in my database that does this with one click. I click the button, they get their next class. I wrote the code for this button when I first started doing memberships years ago. I used a lot of commands to open forms and move between various fields and records: open form, go to control, go to record, the cursor bounces around, and other commands. You'd create a new order, it would open the order form, set the field value, move to the next record, and so on. This is how I preferred doing it at the time and it worked, but now I realize it can glitch. I tend to not like stuff now that involves opening forms and setting values. It's good for beginners to learn how to do that, but there are better ways. Just this morning, I rewrote the whole thing - the whole subroutine - using SQL statements and recordset loops like you should, to do it properly. When it came time to update the totals for the order at the very end, I made a classic mistake. Can you see what it is? If you know SQL, you'll recognize it right away. This code is supposed to update the merchandise total, the adjustments or the discounts (because remember, it's good at discount), and the order total for the current order. Do you see the mistake yet? If you know what this does, you'll get it. Keep in mind this is for the current customer that I'm making an order for. I forgot the WHERE clause. Classic mistake. It's supposed to update the order table and set the merchandise total equal to whatever the merchandise total value is, the total of the order, WHERE the order ID equals whatever the new order ID is - just the one order, not all of them. When I ran this for the first time, the first statement took forever. It locked up Access, so I had to kill the Access process. I thought the database maybe had an endless loop or something. Nope. The SQL statement was busy updating every order in the table. I overwrote years worth of orders to the same price. Oops. There are a couple hundred thousand orders in there. I'm just sitting here waiting, like, why should it take that long? What's going on? And there you go. You can see it. I went to check out the order table and wouldn't you know it, every order had its merchandise total field set to 599. I killed Access before it changed the other fields, but still, the damage was done. It changed most of the orders in that table to 599. Fortunately, I keep very good backups. If you've been watching my videos for any length of time, you know that I preach backup, backup, backup, and then backup again. In fact, I've got multiple videos on the subject, including a couple of backup templates. If you want to learn more about automatically backing up your database daily, go watch my free TechHelp video on backups. I'll include a link you can click on in the description down below the video. Click on that when you're done watching this and learn all about backups and what I think you should set up on your system. Checking my backup folder, the last backup routine ran this morning at 4:10 AM. That's when I have it programmed to run every morning at 4:10. There's almost never a time that I'm working at 4 AM, so that's a good time. Sometimes I am, but that's okay. Basically, all I have to do now, logically, is take the last good order table. Fortunately, that's the only table that was affected. I have to take the last good order table from my backup at 4:10 this morning and restore that, and then copy over any new orders from the bad table that have been made since then. If I compare the two order tables side by side, on the left you'll see the corrupted one where all of the orders have been changed to 599, and on the right you'll see my last good backup, and it stops at order ID 229714. You can see on the left and right, they match up, and then after that, the ones on the bottom there are all the new ones that came in this morning. The timestamps are off a little bit because my server is not in the same time zone as me, so the timestamps are off. Basically, I need to copy over to the old table 229715 and later. However, there's a slight problem. I can't just copy those orders over from the order table because there are related records in the order detail table, and a couple other tables too, that depend on those order IDs. Those order IDs are linked to other tables. If I just copy those records, if I just open up the table, select the records, and copy and paste, what's going to happen is those are going to be all assigned new order IDs because they're autonumbers. When you paste records into a table, that happens. In order to copy them over, I have to use an append query as a trick. This will copy over the records and keep the same IDs from the source table when they're copied over to the destination table, assuming there's no duplicates, of course. You have to eliminate the duplicates. I have a whole video on how to do this. Again, I'll put a link down below you can click on. It's my restore autonumber video. This is pretty much the only time that I'll recommend you do this trick, if you've got some backups you need to restore because people ask me all the time. They're like, well, I deleted a record and now there's a gap in my autonumber sequence. Yeah, that's fine. It doesn't matter. Autonumbers are not for you. I have a whole separate video called Autonumbers Are Not For You. You shouldn't care what they are, but if you're restoring a backup table and you need to keep those same autonumbers, you can use this trick. So go watch that if you want to learn how to do that. And if you've never used an append query before, or you don't even know what an append query is, go watch that video too. The first step was to restore a good copy of the backup order table. That's the good data. Fortunately, that was the only table that was affected. Next, I have to delete the records from the corrupted database file that are already in the good backup. Basically, we're going to delete everything from 229714 and before because we already have those records in the backup. Delete all of that stuff. We're going to import the corrupted order table - the bad stuff - into the database file where we restored our backup. We'll call the corrupted one newOrderT. If you don't know how to import a table from another Access database, I cover that in my Access Expert 21 class. It's pretty straightforward though. Next, we'll create a delete query to delete all of the records with an order ID less than 229715, which is all of the orders that are already in the good backup table. Again, if you don't know how to use a delete query, I cover that in Access Expert 13. Now the new order table after the delete query has only the new orders that came in since the backup. It's time now to move those over to the order table. But again, we can't just copy and paste them because of the autonumbers. I have to set up an append query, like I mentioned earlier. We've got a good table with all of the orders that came in before the backup - that's in the good table, the OrderT. And we've got a table with all of the new orders that came in since that point, but they have the wrong dollar amounts. At least at this point, we're ready to join them together with an append query. We're going to make that append query append all the records from newOrderT into OrderT and we're going to run that query. This is what I covered in that other video that I recommended, the Restore Deleted Autonumbers. When you're done running the append query, check the results. Open up your order table and you can see that you have all of the orders now in one table. Everything from 229714 and before is correct because those records were from our backup. Everything from 229715 and after has been copied in from the corrupted table. You have the correct IDs, so your relationships to the other tables are okay. However, you still have the problem of having to manually correct those merchandise totals, since they're all still set to 599. Unfortunately, there's not much you can do about that. Some manual data entry may be required. That's why it's important to keep frequent good backups. Fortunately for me, I've got a couple of different backups in addition to that. Whenever an order is processed, my system emails a copy of the invoice to the customer. That invoice includes most of the relevant order data: the customer's name, email address, the courses they purchased, the order total, etc. It doesn't include everything like the credit card number is not in there, but it's enough where I can recreate the order in my system in case something like this happens or something goes wrong. I also use Gmail so everything is backed up forever. I love Gmail. I also keep a backup log of every order that's submitted on my web server, just in case. Again, it's one more way I can recreate an order if I need to. Again, this log doesn't hold credit card data, but the orders have already been processed, so that's not a problem. There you go. That's how I managed my terribly stupid update query goof, what happened, how I fixed it, and how you can recover from pretty much any disaster if you make good, frequent backups. Check out my backup video if you haven't watched it already. I'm Richard Rost from AccessLearningZone.com with today's Fast Tip. I hope you learned something and thanks for watching. We'll see you next time. QuizQ1. What was the primary cause of Richard Rost's data disaster in his Microsoft Access Database?A. He forgot to include a WHERE clause in his SQL UPDATE statement B. He deleted the entire order table by accident C. He imported data without mapping the fields correctly D. He performed a manual data entry mistake Q2. Why is omitting the WHERE clause in an UPDATE statement problematic? A. It prevents records from being deleted B. It updates every record in the table instead of just the intended ones C. It stops the execution of the query D. It creates duplicate records Q3. What critical habit does Richard emphasize throughout the video to protect against data disasters? A. Always write detailed documentation B. Keep frequent and reliable backups C. Use only manual data entry methods D. Avoid using queries completely Q4. After noticing all merchandise totals were set to 599, what did Richard identify as his only affected table? A. Membership table B. Customer table C. Order table D. Inventory table Q5. What was Richard's strategy for recovering the affected order table? A. Re-enter all orders by hand B. Restore the last good order table from backup and append new records C. Delete all data and start over D. Export the corrupted table to Excel and fix it there Q6. Why can't you simply copy and paste records involving autonumbers in Access without issues? A. Access will prevent any copying or pasting B. Copying creates new autonumbers, disrupting relationships with related tables C. Copying deletes the original records D. The pasted records become locked and unusable Q7. What is the proper solution Richard used to retain the same order IDs when restoring records? A. Manually editing the order ID field B. Using an append query to copy records and preserve autonumbers C. Exporting and re-importing via Excel D. Renaming the table before importing Q8. When using an append query to restore data, what is a crucial step to avoid problems? A. Making sure there are no duplicate IDs B. Ignoring related tables C. Disabling all relationships D. Converting all data to strings Q9. After successfully restoring the correct records, what remained to be done regarding the corrupted data? A. Replacing all customer emails B. Manually correcting the merchandise totals for new orders C. Deleting the entire table structure D. Generating a new set of order IDs Q10. Besides backups, what additional methods did Richard use to help reconstruct lost orders? A. Periodic cloud snapshots B. Automated email invoices and backup logs on his web server C. Printing every invoice on paper D. Keeping physical CDs with database copies Q11. What does Richard say about worrying over gaps in autonumber fields when not restoring from backups? A. It is crucial to fix any gaps immediately B. Gaps should be ignored, they do not matter for autonumbers C. Gaps can damage database performance D. Autonumber fields determine sorting order Answers: 1-A; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-A; 9-B; 10-B; 11-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from the Access Learning Zone focuses on disaster recovery in Microsoft Access and what to do if you accidentally corrupt your data. I want to walk you through a real situation I faced in my own business database, explain what went wrong, and show you the strategy I used for recovery.Let me give you some context. I have a database that runs my company and manages memberships. Typically, everything is processed automatically each night. Occasionally, though, I get a request from a member who needs their next class sent out immediately. Years ago, I wrote some VBA code behind a button that would handle this task with one click. The code would open forms, move through fields and records, create an order, and set the necessary field values. At the time, this approach worked and was common for beginners, but now I realize this workflow can be unreliable. This morning, I decided to rewrite the entire subroutine to do things properly, using SQL statements and recordset loops without jumping between forms and controls. This method is more robust and less error-prone. However, when I reached the part of the code to update the order totals, I made a critical mistake: I forgot to include a WHERE clause in my SQL UPDATE statement. If you're familiar with SQL, you probably see the problem. The statement was supposed to update only the current order's totals, but without the WHERE clause, it updated the totals for every single order in the table. I have hundreds of thousands of orders in my system, so Access became unresponsive quickly. I realized something was wrong, terminated Access, and discovered that most of the orders in the table had their merchandise totals set to 599. Thankfully, my commitment to regular backups paid off. If you've seen my previous videos, you know I constantly emphasize the importance of making frequent backups and even have template solutions available. I back up my database every day at 4:10 AM—an hour when I'm rarely working, so it works for me. Fixing the issue involved restoring the affected table from my most recent backup. Since only the order table was changed by the erroneous update, I just needed to grab the copy from that morning's backup, then bring in any new records added since then. Looking at both tables side by side, it was clear where the problem started and which new records had come in after the backup. There's an important complication here. I couldn't just copy-paste the new records into the restored backup table. Autonumber fields like OrderID would be reassigned new values by Access, breaking relationships with other tables like Order Details. To maintain those original OrderIDs, I used an append query, which can retain the original ID values as long as there are no duplicates. So, my process involved several steps: - I restored the last good backup of OrderT, the orders table. - I deleted from the corrupted table all records that were already present in the backup (anything up to OrderID 229714). - I imported the corrupted orders table into the database, renaming it as newOrderT for clarity. - I used a delete query on newOrderT to remove all records with OrderIDs that were already restored. - With only new orders remaining, I created an append query to move these records from newOrderT into OrderT, making sure the original OrderIDs were preserved. By checking the results, I confirmed that all historic data was accurate and that new orders since the last backup were present. Of course, any new records copied over still had incorrect merchandise totals and would need to be fixed manually. This kind of data correction is tedious but sometimes unavoidable if there is no other backup source. Fortunately, my precautionary habits made this much easier. Besides my regular database backups, my system emails a copy of each invoice to the customer, which contains all essential order details (except sensitive information like credit card data). Additionally, a backup log is kept on my server for every order processed, giving me multiple places to verify and recover data if needed. This is a perfect example of why making regular, reliable backups should be a non-negotiable habit. By following these steps, you can recover from even significant disasters like a mass update error. If you want to learn more about automatic backups or restoring autonumber fields using append queries, I have dedicated videos on those topics. Just visit my website for in-depth, step-by-step demonstrations. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListDisaster recovery in Microsoft Access databasesIdentifying and understanding the missing WHERE clause error in SQL UPDATE statements Recognizing the consequences of accidental mass record updates Restoring tables from Access database backups Comparing current and backup tables to identify affected records Handling related records and preserving relationships in Access Using append queries to maintain original autonumbers when restoring data Deleting duplicated or corrupted records using delete queries Importing tables from another Access database file Manual correction of residual data issues after restoration Utilizing external sources such as invoice emails and web server logs for additional data recovery |
||||||||||||||||
|
| |||
| Keywords: FastTips Access disaster recovery, restore from backups, backups, restore, replacing autonumbers, append query, Disaster Recovery Plan, Backup and Disaster Recovery, Resiliency and continuity, Protect your data PermaLink Disaster Recovery for Microsoft Access |
