$Account.OrganizationName
The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
September 2011

What To Do When A Hurricane Strikes Your Database

In August 2011, a tropical depression formed off the coast of West Africa. It made its way east toward the United States and eventually became a category 3 hurricane named Irene. When it first threatened the U.S., it looked like it was heading toward Florida, including the Florida Keys. At that time, I was on a vacation in South Florida traveling from Orlando to St. Petersburg to the Everglades and ending in Key West. As it turns out, Irene became a hurricane while I was in the Everglades. I began monitoring it very closely and saw that it had Key West in its projected path. Evacuating Key West during an emergency can be a real challenge since there's only one road out, which quickly becomes clogged.

When I saw that Irene was veering east of Florida and was going to spare the Keys, I was relieved. I was able to complete my vacation and fly safely back to New England. As luck would have it, rather than getting the fury of Irene at the beginning of its hurricane cycle. I experienced it at the end as it was winding down from a hurricane to a tropical storm.

Irene came through our part of Massachusetts with damaging winds and torrential rains. Unfortunately, some of those winds got to a neighbor's tree which crashed through my yard, blocked my driveway, and destroyed my mailbox.



Luckily, no one was hurt. It was a bit inconvenient getting out of our driveway for a bit, but within 24 hours the tree had been cleared and I had replaced the mailbox.

What happens when something of similar potential catastrophe hits your database? You might be going along with everything normal when, with no warning, your database starts acting strangely and stops cooperating with you. It could be that your database is completely corrupt, or it could be that some portion of it is sick. If you're lucky, like the mailbox destroyed by hurricane Irene, you may be able to recover with minimal loss.

In this month's Wizard, we'll take a look at what you can do when something bad strikes - work to recovery with minimal loss.

In this Issue
  • Tip of the Month - Backup Your Data Regularly
  • Steps to Resolution
  • Conclusion
  • Trap of the Month - Don't Assume That Your Backups Are Valid

  • Steps to Resolution

    The way this particular problem presents itself is that, when things are going just fine and with no warning, the database becomes unhappy and throws cryptic error messages every time you try to open a certain form. The first thing to try is closing and reopening Access. Every once in a while, this will solve your problem and it's joyous when it does.

    When that doesn't work, your next best bet is to compact and repair the database. See the January 2008 Wizard for the steps to try this solution.

    If that doesn't work take the next easiest step and recompile the database. See the February 2010 issue for this process.

    If your problem still persists then you have something that is significantly wrong. All is not lost, however, and in many cases you can get everything back except perhaps for one form or report. This next step is fairly drastic, but it is the thing to do when you have tried the prior approaches. The process is to essentially rebuild the database by importing all of the objects into a clean new database. To do this, take the following steps.

    1. Close your database.
    2. Open Access and start a blank database.
    3. Import all your objects from your old database into your new database.

      In Access 2007/2010, you do this by clicking on the external data ribbon, click on the Access icon, make sure that all tables, queries, forms, reports, macros, and modules are selected. Enter the name of (or browse to) your old database.Then select each object on each of the tabs that is presented to you. Click OK.

      In earlier versions, the process is quite similar. In the database window, click on tables. On the main menu at the top of the screen, select get external data, and browse to your database. For each of the tabs in the dialog box, select all objects and then click OK.

    At this point, Access will begin to import all the objects from your old database. If you have one or more objects that are corrupt, you will get an error message. For instance, let's say one form has become totally destroyed. The process will stop and tell you that it is unable to import that particular object. In this case, you've identified your offending object. Click OK and allow the import of the remaining objects into the new database.

    You will now have everything else in place other than the offending form, which you will essentially have to rebuild. All is not lost, however, because in many cases you can cut and paste the code from the old form into the new form. If you choose that route make sure that you immediately decompile and then recompile to make sure that your code is clean.

    If you get this far, you'll have dodged a bullet and, although you have some work to do, you will be able to get back to where you were once you have rebuilt the corrupt form.


    Conclusion

    Every once in a while, calamity strikes. If you are lucky, it will be like hurricane Irene and my mailbox - a minor inconvenience that will cost you some time and money but you'll be able to get back to where you were before you had the problem. If, unfortunately, things are more corrupt than a single form or report then it's time to go to your backup file. You do have one of those, don't you?


    Trap of the Month - Don't Assume That Your Backups Are Valid

    Several times in the past I've written about the importance of backups. When you need one it's a lifesaver. Many users, however, make the mistake of not testing their backups. They think that there are safe because they are getting reports that say that their backups are taking place.

    I had one unfortunate case where a customer had their hard drive stop working. They went to restore the backup and found that the backup procedure was faulty. Luckily for them, I had a recent copy of their data file that I had been working on and was able to restore them to 95% of where they were.

    The way that you avoid problems such as this is to periodically test the integrity of your backups. Take your database, rename it, and then see if you can restore your last backup. If you can, give yourself a pat on the back because you have a process that works. If you can't restore, you've learned a valuable lesson. You know that you have to fix your backup routine because something is not right. If you discover this in your testing, be happy that you learned it during testing, rather than when you really needed it.


    Tip of the Month - Backup Your Data Regularly

    Having a backup of your database and other computer files is not an option - it's a requirement. If you treat it as anything other than required, at some point you'll be very unhappy because something will happen from which you will not be able to recover.

    The options for backups range from the simple and mindless to the extremely complicated and expensive. At the very least, you should be backing up your data every single day, ideally in a way that doesn't require you to take any action.

    If you rely on remembering to do something, there will come a point when you get busy or something will interfere with you during the backup and it won't take place.

    It is much better to set up a system that will take care of it for you automatically. The major choice you have to make is whether to back up locally or to the cloud. If you back up locally and do get hit with something like a hurricane, flood, or fire, your backup may also be destroyed. So if you do backup locally make sure you have an off-site copy. External hard drives have become very cheap so this is not an expensive proposition. Periodically make a backup to an external drive and give it to a friend who lives in another town and then just swap out generations of that disc.

    If you choose to back up to the cloud, there are a number services that can use. You'll never be vulnerable to a hurricane striking your house. The real danger is your backup company may go out of business. The other issue with cloud backups pertains to their backup method.

    Typically after the first complete backup, their process is to backup only those files that have changed since the last backup. With databases this presents a problem. Any time you make a change, the entire database needs to be backed up. This results in a lot of network traffic, especially if you use multiple databases in your work.

    In either case, the important point is to make sure that you are backing up your database so that when Hurricane Irene or one of her siblings comes to your neighborhood, you know that you can recover from a lost database.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services



    Join our mailing list!


    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886