Access Corruption Avoidance Tip #1: Split Your Database

Microsoft Access is an economical and easy to use database solution for small businesses. However anyone who has used the application as a shared network database knows that corruption can destroy all your hard work. While your Access database training will help, there are several tips that can reduce the chance of data corruption.

The Front End and the Back End

A good practice for a networked Access database is to split the database into two pieces. The front end contains information that users won't change such as forms and reports. The back end holds the actual data. Splitting can be done with the wizard you learned about in Access database training.

The back end should be stored on a secure and shared server. It is the piece that will need to be backed up regularly and most protected against corruption. The front end will need to be archived but since it doesn't change, even if it becomes corrupted it can easily be replaced from the archived copy.

Users can either each have a copy of the front end on individual client computer or they can share a common front end on the server. It should be write protected to minimize the chance of damage.

Speed Up Queries with Temporary Tables

Users often don't realize just how much data is read or written when they query a database. Even people fresh from Access database training are often surprised by how many transactions a nested query takes. For example, a query asking for all December sales in Ohio will first make a table of December sales then from that create a table of all Ohio sales. The problem is each read or write operation is a new opportunity for damage to the database if the system crashes or network connectivity is lost during the operation.

Storing intermediate results to temporary tables has two important benefits to the database. First, it greatly speeds up the query. Second it moves the chance of corruption to the temporary table rather than the main database. Losing temporary data means the user loses the query but can simply send the query again.

Split Out Temporary Files

Once you adopt techniques from your Access database training to use temporary tables, protect the main database further by putting those tables in a separate file. This third piece of the database should be stored on the back end server. Since this piece is the most likely to be corrupted, separating it minimizes the spread of damage to your main database tables. Damage to these temporary tables may have a short term impact on users but this piece can quickly be rebuilt without impacting the overall database.

A few basic "best practices" like these keep your data secure and minimize the chance of catastrophic loss of information. Look for other articles with tips on preventing Access database corruption.