Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Fast Tips > Access > Disaster Recovery! < Notes Anywhere | Save Button >
 
Disaster Recovery!
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   2 months ago

How to Fix Your Data When You Mess Up Records


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Join Now

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.

Learn More

FREE Access Beginner Level 1
Access Level 2 for $1.00

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

 

Comments for Disaster Recovery!
 
Age Subject From
2 monthsDisaster MinimizedScott Axton

 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

6/30/2022Sales Chart
6/27/2022Rounding Errors
6/24/2022Multi Combo One Field
6/23/2022Modal & Popup
6/21/2022Week of Year
6/16/2022Value List Combo
6/15/2022Weekday
6/14/2022Project Budgets
6/14/2022Find Record
6/13/2022Access Amortization Template 2.0
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
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