Backup and data preservation
First things first. Find out whoever pushed the power button. SLAP that person.
OK, next lesson. Based on churn rate and data criticality to your business, you must take precautions in your design to allow for a couple of safety factors. Ask yourself these questions:
How much of a data loss can I tolerate? If you are like most people, your first answer would be "NONE."
OK, then how much are you willing to do to achieve that goal? HERE is where the reall work comes into play. Suppose you DO lose data. How much time are you willing to spend, and how much extra drive space are you willing to spend, to minimize your loss after a catastrophe?
Normally, you do things like say, "If I lose 1 day of work I lose 100 records." (I.e. gauge your record churn rate.) Then you figure out how to store the data needed to recover those 100 records as part of your processing recovery project.
Next, you ask "Is there a time of day/week I can dedicate to protecting myself from data loss such as this?" (I.e. gauge your database's usage profile by time.) Then figure out what you can do in the window of opportunity that you can set aside to protect your own butt from future repetitions of the wild button-jabber.
In general, there is a phrase that computer professionals learn VERY fast. Just like the realtors sell houses based on location, location, location, you will save your own a$$ based on backup, backup, backup.
It matters not whether you backup by copying to another disk, copying to another folder on the same disk, or making a CD-ROM copy, or sending it across your network to another computer with the free space you need. Tape backup? Whatever you've got. Just USE it. Don't go out and buy ARCServe or Legato or some other pretty-fancy and then let it sit on the shelf collecting dust.
What the backup step does is it establishes an upper limit to your data loss. Daily backup = lose no more than one day of data. Weekly backup = lose no more than one week of data. Longer than weekly = look for new job sooner than the next data loss.
Next, see if there is a way that when you perform your transactions against your database that you can copy, move, or otherwise preserve the input data that would allow you to perform a recovery of the day's transactions. Here is the facet that makes this work reasonably. Once you do the backup safely, you no longer need to preserve the input data for recovery to the current point. Some folks keep the transactions by keeping the input files that are the source of the data. I.e. don't delete it just because you have transacted it. Keep it as a separate file until the next backup cycle. THEN sh|t-can it. Another approach would be to periodically export the records required to some sort of recoverable file. Then have a way to import the records back later. By being in a separate, closed file, you reduce the odds that they get trashed when your database gets trashed. Some combination of the above will protect you absolutely as well as it is possible.
One approach might be to open a file using VBA and write records you can play back by reading later. Close the file after updates are finished. Using the file system object (which you can look up using Access Help), you could determine whether the file exists and could then choose whether to open the file for write (create a new one) or append (keep the old one but augment it.)
This is how you protect yourself long-term. Think safety. Think "How much does my business depend on this data set?" Then do as much as it is worth to keep your business running despite the occasional bump in the road.
