Techno World Inc - The Best Technical Encyclopedia Online!

5 Ways to Avoid Microsoft Access Database Corruption

There is a possibility of having a MDB file marked as corrupt even if there is no actual corruption of data, and it is important for Access users to realize this. Whoever tries to access the database while there is a corruption alert will be made aware about it, and the database will need to be repaired before anyone can open it. When users do not have the required permission to repair the database, or have no experience repairing a database, there will be a considerable amount of delay in operations because the database is not functional. When there is delay in operations, objectives will not be accomplished and deadlines will not be met, causing low morale in the working environment.

So, how do you avoid the catastrophic effects of database corruption in your workplace? Here are 5 tips to follow that will help lessen the occurrence of corruption in your database.


1) Use only good quality hardware.

Using cheap hardware for your network connections can cost you even more. Keep in mind that the slightest problem with your network connectivity can affect your database and cause an error or corruption alert. Make it a point to use good quality hardware such as brand name network cards. Don't settle for less.


2) Close the database application properly.

Always exit the database properly. Use the File->Exit or other methods of exiting provided by Access. Do not ever use the Task Manager via CTRL+ALT+DEL to exit your Access database as it can cause errors or corruption in your database.


3) Don't maintain network connection unnecessarily.

It doesn't matter if you're a user or a programmer. You should always close your connections in the right manner as soon as possible when you're finished with your work on the database. This will lessen the chances of unanticipated dropped connection. If your connection is used inside a loop, open the connection at the start of the loop, and then close it when the loop is finished.


4) Separate your database into a front-end and back-end.

One of the most important things to do in avoiding errors and corruption in your Access database is to split it into a front-end and a back-end. The front-end stores most of your database objects such as forms, queries, and reports. A copy of this should be stored in each user's computer. As for the back-end, your tables should be stored in a shared folder there on the network. Since forms, queries, and reports are mostly used individually, it is reasonable to put them in local workstations, rather than put them in a shared folder in the network. It is also reasonable to store your tables in the back-end for the network to share, because they are the sources for queries, forms, and reports. The tables contain the only data that need to be shared. This way, there is less data going through the network, minimizing the chances of errors and corruption.


5) Don't forget to use Compact and Repair.

There is a Compact and Repair function in Access, and using this on a regular basis is highly recommended to avoid corruption and enhance the performance of your application. Apply this function to all of your databases during times when there is least or no activity concerning your databases.

Also be familiar with these error messages. These may signal database corruption:

" Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience.
" Unrecognized Database Format
" Unexpected Error 35012
" 'filename.mdb' isn't an index in this table. Look in the Indexes collection of the TableDef object to determine the valid index names.
" "Disk Error -- Reserved error (-1601)"
" "The database has been placed in an unexpected state."
" "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:
Following these 5 simple tips will save you a lot of frustration from the effects of a corrupted database. Though these methods will not prevent all Access database corruption, applying these methods will stop most problems before they happen. Just always remember to backup your data just in case, and perform good maintenance to your network hardware.