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

• Backup: https://599cd.com/Backup
• Restore AutoNumber: https://599cd.com/RestoreAutoNumber
• Append Queries: https://599cd.com/Append
• AutoNumbers Are Not For You: https://599cd.com/AANFY

Suggested Courses

• Import Table: https://599cd.com/ACX21
• Delete Query: https://599cd.com/ACX13

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.

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

Keywords

access 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