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

In The News:

---

Bill Gates Made Data Recovery Easy

Bill gates provided a ready made option in Microsoft windows, which is very useful for data recovery.Several time we make mistakes unknowingly and we lost our data, then we need to recover the... Read More

Sea-Front or Action-gate

June 25, 2005Seagate Technologies' web site now indicates the company offers data recovery services. Further investigation shows the shipping address, the place to send your subject drive for data recovery service, actually belongs... Read More

Data Recovery: Beginners Tips

Right now you probably in a lot of mental pain, and all you're concerned about is recovering your data as quickly as possible - so we'll refrain from comments on the wisdom of... Read More

Disaster Recovery Made Easy With Online Backups

Whenever disaster strikes the most critical asset that every company should have protected from sudden loss due to fire, flooding, or theft is their data. No matter what happens to your hardware you... Read More

Data Backup Solution: Why You Need a Backup Plan in Place ? Part 2

In the first part of this article series, I talked about the three main threats to the data on your computer. I talked about the need for a data backup solution that protects... Read More

Learn How To Make Data Backup Over The Internet!

Why should you backup your data on the Internet?There are several reasons.* The backup is located at a secure place for away from your computer. * It's is easy to do. *... Read More

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!... Read More

Business Continuity and Disaster Recovery - Reducing Your Risk Profile

Like all plans, there is an ultimate goal to achieve. The goal in a business continuity plan is simply that: to continue your business in the face of a disaster or a disruption.... Read More

Comparing Data Recovery Software

Software programmers recognize the importance of data files, and thankfully, they have been able to create products that demonstrate their understanding of how often people accidentally delete or lose important data. Thanks to... Read More

Is Your Backup Good Enough?

As someone who has seen quite a few hard drive crashes over the years, I believe that performing a full backup of your computer's hard drive should be an important part of your... Read More

The Best Data Recovery Choice For You

The best defense against a loss of data is a really solid and faithful backup routine of important files to reliable media. It's also a good idea for the media to be removable... Read More

Backup Your Data Or Lose Your Life!

Oh the perils of collecting those precious photos on your PC for years, only to have your hard drive crash one day, and not have made a SINGLE BACKUP COPY of any of... Read More

Business Continuity and Disaster Recovery - The Business Continuity and Disaster Recovery Plan

Essentially, the plan addresses the who, what, where, why and when of recovery. Goal number one is to reduce the risk profile of the business. Goal two is to be well prepared so... Read More

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.These statistics are staggering. Even if the business survives the initial attack or natural disaster,... Read More

Backup Schmackup: Im Afraid, Very Afraid!

"Why should you be afraid?", I can hear you ask.I just got an email from my friend Miche who said,"My laptop is sick at the hospital. Hard Drive failure. Am praying data can... Read More

When Disaster Strikes: How Long Would Your Business Survive if Files On Your Computer Disappeared?

A Tornado? In Birmingham? In The United Kingdom? Unthinkable!Yes, in July 2005 (while I was putting finishing touches to my cave-house in Spain) my house, along with many of my neighbours' houses was... Read More

Data Recovery - You Can Get It Back!

Data recovery is a process whereby you can save data and files you accidentally deleted, had eaten by malicious software or viruses, hardware failures, corporate espionage and many more scenarios. Most people believe... Read More

Backup -- But Where To?

We all know we have to backup regularly, but those backups files can get huge. Add that to your existing your files and your notebook computer hard drive seems to have shrunk overnight.... Read More

Online Data Backups for Newbies

How long have you been doing business online? Whether you are new or an old timer you need to understand the one important truth of the e-world. That being that the largest threat... Read More

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.... Read More

Are The Words Business Continuity and Disaster Recovery Planning Rolling Off Your Lips?

It might be more fun to talk about those free online business cards that are saving the company money, or those nifty designer business checks that are helping to solidif your branding efforts... Read More

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... Read More

9 Things to Look for when Considering Data Backup Solutions

It's one thing to loose data and money because of hackers and thieves of various kinds; it's a different thing again when it's because you didn't back up your data properly or not... Read More

How to Effectively Back Up Your Data

What to backup, and what to loseThe oldest "computer expression" is that there are two kinds of computer users, those that backup their data and those that will. Often, it takes a "data... Read More

Data Backup for Beginners

It's 10pm. Do you know where your data is?Data. Small bits of information clustered together to make Word files, documents,pictures, MP3s, HTML etc. We work on our computers every single day never sparing... Read More

Data Recovery - What Not to Do!

Data recovery is a tricky thing, and if you've somehow deleted or had your important files corrupted or lost due to human error, business espionage, faulty hardware or software or any other reason;... Read More

Big Time Disaster Recovery Solutions Available for the Little Guy

There was a time - not too long ago - that Data Disaster Recovery solutions were available only in the domain of multinational corporations and big business in general. However, with the proliferation... Read More

Offsite Data Backup Not Just for Fortune 500 Companies Anymore

In today's high paced digital world there is a very high value placed on information. Not just the kind of information you read in the newspaper or your favorite trade journals, but the... Read More

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.Most companies that fail are the ones that have not... Read More