5 Simple Tips to Prevent MS Access Database Corruption
It's important to understand that any time an Access client disconnects unexpectedly, it may set a "corruption flag" in the .mdb file indicating that the database is in a corrupt state (regardless of whether any data has actually been corrupted or not). Any user who attempts to open the database while this flag is set will receive a message, and the database will have to be repaired before it can be used. If the users don't have permission to perform the repair, or don't know how to perform the repair, there may be significant downtime before the database is available again. This can result in a loss of productivity as well as extreme frustration for the users. Following the 5 tips below will minimize your odds of data loss from corruption in your Access databases. 1) Split your database. The single most important thing you can do to prevent corruption in MS Access databases is to split the database into a "front-end" and a "back-end". The front-end contains all of the forms, queries, and reports, while the back-end contains only the data tables. A copy of the front-end is placed on each user's computer, and the back-end with the table data is stored in a shared folder on the network. How does this prevent corruption? Well, consider the amount of information that must make it across your network to your computer each time you open a form or report. If one bit of that information doesn't make it intact, you risk corrupting your database. Alternatively, if the forms, queries and reports are all stored on your local computer, then the only bit of information that needs to traverse the network is the actual table data. By reducing the amount of data you need to move back and forth across the network, you significantly reduce the chances of corrupting your database. If you're having corruption problems with an Access database on a network drive, splitting the database is the single most important thing you can do to stop it. 2) Don't hold connections open. This one applies to both programmers and users. If you're a programmer, make sure you close your connections as soon as possible after using them. Leaving the connections open will allow more opportunities for an "unexpected" dropped connection. The only time you may want to leave a connection open longer than required would when it's used inside a loop. For such a case, open the connection at the beginning of the loop, and then close it after the loop is completed. Just make sure it gets closed for all cases (including exceptions). If you're using a Microsoft Access database or application, be sure to close it when you're finished. Again, leaving the application open provides the opportunity for corruption if a network connection is lost. Remind users to always close the application before going home, as nightly backup jobs may fail or cause corruption in the shared file if there are open connections. 3) Exit the database correctly. Always close the database or application correctly. Ctrl-Alt-Delete/End Task can wreak havoc on Access databases. Whenever possible, complete your tasks, then close the application using the File - Exit menu option or alternative Exit option provided by the application. 4) Don't skimp on hardware. Remember that the corruption flag can be set from the slightest packet loss between your computer and the database file. MS Access has sometimes been called "the canary in the coal mine". It has gained this reputation from being the first application to "die" when there's the slightest hint of trouble on your network. Just like the slightest presence of gas caused the canary to die, the slightest presence of network problems and packet loss can kill your Access applications. Make sure you're not using the cheap built-in NICs that come with some PCs. Instead, use brand name network cards. The same goes for cheap hubs. Whenever possible, match good brands of equipment throughout
your network. 5) Compact and repair regularly. Performing the built-in compact and repair function regularly is recommended to prevent corruption and improve performance. Consider automating this function with a utility to compact and repair all of your databases nightly or during the weekend. Error messages to look out for - the following error messages may signal database corruption: "The database 'databasename.mdb' needs to be repaired or isn't a Microsoft Access database file." "Record(s) can't be read, no read permissions on 'databasename.mdb'" "Unexpected Error 35012" "Unrecognized database format 'databasename.mdb'." "'databasename.mdb' isn't an index in this table. Look in the Indexes collection of the TableDef object to determine the valid index names." "The Microsoft Jet database engine could not find the object 'databases'. Make sure the object exists and that you spell its name and path name correctly." "The database has been placed in a state by user '' on machine '' that prevents it from being opened or locked" "Disk Error -- Reserved error (-1601)" "The database has been placed in an unexpected state." "Record(s) cannot be read; no read permission on 'MSysObjects'" "Record(s) cannot be read; no read permission on 'MSysACEs'." "The Microsoft Jet database engine cannot find the input table or query 'MSysAccessObjects'. Make sure it exists and that its name is spelled correctly." Conclusion: While you may never be able to prevent all Microsoft Access database corruption, you should be able to stop 98% of the problems before they occur by following these 5 simple tips. Follow these tips and implement a prudent automated backup schedule to minimize your odds of significant data loss. Kevin Sparks is a technical writer for Kaizen Software Solutions, the producer of Digital DBA, an automated MS Access monitoring, backup, and compact/repair utility. For more information, visit their website at http://www.kzsoftware.com/products/digitaldba
Related News
|
 |
|
|
RELATED ARTICLES
Do I Really Need To Backup?
Okay, computers a machine, right? Okay, maybe not yours. You
have this special relationship, but that a subject for
another article. Machines break. Software gets fouled up.
People make errors. These things happen, and for those
reasons alone, you need to back up all your critical data,
redundantly, in a couple of different locations.
Backup 101 - How To Pick The Right Backup Software
Vision Backup
http://www.deprice.com/visionbackupenterprise.htm
Business Continuity and Disaster Recovery - Risk Analysis and Control
In the risk evaluation phase, there are a number of key areas that must be covered. One of the most important is to understand probable threats. In an ideal world, which most of us have noticed does not exist, we would identify and protect ourselves against all threats to ensure that our business continues to survive. Obviously, we are constrained by other factors such as budgets, time and priorities and need to apply cost benefit analysis to ensure we are protecting the most critical business functions.
Business Continuity and Disaster Recovery - Business Impact Analysis
Business impact analysis is a critical part of the business continuity planning process. This step quantifies data and gets into the real world issue of potential losses that can negatively impact your business. It is used to understand the most important impacts and how to best protect your people, processes, data, communications, assets and the organization's goodwill and reputation.
Hard Drive Crash? The Essential Data Recovery Report
Your worst nightmare just became a horrifying reality. You keep hearing that little voice in your head mockingly shout "you should have backed that stuff up" The voice keeps echoing throughout your head as you perform a quick inventory all of the important information that you just lost?..your client database, a years worth of e-mail, your entire inventory database, even your family photos.
Disaster Recovery- Managing the Risk
Helping your business survive in the event of an unforeseen disaster: Most business failures are not from bad business but from bad business practices.
Read This Now: Its Not If You Will Lose Your Data, Its When Will You Lose Your Data
With Adware, SpyWare, E-Mail Viruses, Java Script Viruses and Hardware failures It isn't a matter of If you will loose your Data it is a matter of When you will loose your Data! By spending a few extra minutes now using a Simple Back Up Strategy, you can save yourself big headaches later.
Protect Yourself Before Your Hard Drive Crashes
Almost everyone who has used a computer has experienced a hard drive problem at some point. Are you prepared to lose your data? If your hard drive crashes right now, do you have an action plan to follow?
Backing Up Personal Computers
Backup on business computers is typically not the user's concern unless it's a small business. A business should have a policy in place for managing backups especially due to Sarbanes - Oxyley.
Learn How To Make Data Backup Over The Internet!
Why should you backup your data on the Internet?
3 Ways Computers Can Hurt Your Ministry - Part 1 - Ineffective Data Backups
Our computers have become almost indispensable ministry tools. What would you do if the worst happened and you had to function without your computers? Would your ministry survive?
How to Effectively Back Up Your Data
What to backup, and what to lose
The Importance of Data Backups
All computers and their components are subject to failure. In fact sooner or later every business will be confronted with some type of computer failure. The largest threat to businesses during a failure will be some type of data loss or data corruption. While the causes of data loss and data corruption vary, not all are easily fixed.
Computer Data Recovery Options
Losing files on your computer can be a frightening experience if the files are of importance to you personally or professionally. Computer data recovery techniques include the most simple of operations, like simply retrieving an accidentally deleted file, to very complex file retrieval procedures that only professionals should attempt to do.
Backup Schmackup: Im Afraid, Very Afraid!
"Why should you be afraid?", I can hear you ask.
Computer Data Backup - Data Backup Solution Will Give You A Peace Of Mind
Imagine that you have been working on a file for the entire day and there was a power surge that caused your computer to 'black out'. It would have been a frustrating experience for us as we had put in a lot of time and effort into it. I am sure that many of us would have experienced data loss in some form or another and have learnt to be wiser to backup our data to avoid any form of data loss.
Want To Beat The Odds? Disaster Recovery Planning is Essential
Statistics show two out of five businesses go out of business within five years of experiencing a major disaster.
Sea-Front or Action-gate
June 25, 2005
I Lost It! Simple Guide To Recovering Corrupted Images And Files
WinUndelete
http://www.deprice.com/winundelete.htm
Data Backup Solution: Why You Need a Backup Plan in Place ? Part 1
Is a data backup solution part of your computer plans? Do you have a way to protect yourself if something goes wrong with your computer or data? Computers are a useful part of our lives but we don't always protect ourselves from their loss be it fire, theft or hardware failure.
|