Monday 23 July 2012

DATA CORRUPTION - Avoiding & Recovering (Part 1)

People who use MS Access as a part of their day to day job, sometime find themselves in a situation where they look at the screen with a 'nervy peek' thinking all is gone. This happens when they try to open the file and the records are corrupted, thus not allowing to open the file in the first place or may lead to inconsistency of large volumes of data which still poses a threat. Well if you are reading this, I am just trying to let you know not all is gone. You might still have a fighting chance to save your data. 

Before going into the details of how to recover your file from a corrupted state let us think a step ahead. Have you every heard about the expression "Prevention is better than Cure"?? Exactly, if you do not mess about with data which is highly sensitive; you might not even end up here. So let us see some basic steps of how to properly do things to avoid such disaster. 

Avoiding Data corruption

# Create a Front End/Back End of Access
Creating Front end (FE) and Back end (BE) might seem very complicated and time consuming and at times even very terrified. But fear not. Splitting the DB into FE and BE is very common and is always helpful in making Form usage smooth and allows multiple users access more easier and efficient. The splitting process though not very hard, I will describe in a new post. Splitting is just organizing what goes where. When you split your DB, the FE contains all your Forms, Queries, Macros and Modules. The BE will contain purely your tables. Thus a link will be created between the Forms to interact with the data which is commonly used by other people. This not only solves for the issue of concurrent users but avoid the data being corrupted because of faulty coding; that crashes on open and or trying to compile enormous amount of data. 
This is what we wish to achieve once we have split the DB
Let me tell you one another great thing of splitting a DB; you can now have two access files each for up to 2GB . Thus you can have more data in the BE and also you can have more functionality that can be added to front end to make sure data stays safe and secure. 

# Avoid MEMO and Multi-valued Fields
Multi-valued fields like attachments can be stored for each and every record alongside of the other data unlike the BLOB data type in SQL, which stores them in a separate table. Sometime it feels appropriate to have some files like pictures or PDF's relating to a particular record along with the record for referencing. Well it sure is, we do not want to have opening Mrs. Jane's ultrasound, under Mr. Smith's kidney scan. Utmost care should be taken for using such fields. As this is one major area of corruption. This not only takes a lot of space, which bloats the DB, but also if not properly taken care can lead to corruption. The best way to avoid it very rarely use them. If there is a need for such fields; let us follow the example of SQL. Create a separate table and have a one-to-one relationship with the master records, thus if in case a corruption occurs it will at least leave the master records intact. 

# Avoid Storing Calculated Data or Lookup's on table level
Calculated data does not mean that you should not save values that are calculated by VBA on a FE.
Example maybe using a form to auto fill Name of the day on a field. 
This however is calculated does not have any problem in saving. When the word 'calculated' is used it means data depending on other fields. 
Good example would be the field 'Amount to pay', which is obtained by using multiplying 'Quantity' and 'Price'. 
Just in case; one fine day you decide to delete Quantity as it was just a number that you wanted to make sure is there but now not required. All your data inside 'Amount' will be automatically lost thus leaving the fields value to be set as #?Name, as it depends on the 'Quantity' field. 
Similarly Lookup at table level is again an evil thing to do. 
Example: If you have a field 'Employee name' looking up on 'Employee' table for values. 
If Jon has left the company and you remove his name from the Employee table the field now references some value that does not live in the table anymore. Thus again leads to Corruption. Lookup's in FE is the best way to use. The Tables that you use should only contain data that are being entered. This way you can be rest assured that data can never be corrupted.

Please carry on reading to find ways to Recover your Corrupted file.

2 comments:

  1. HI Paul this is Prad from Accessforum UK..remember? Just wanted to pop a question how will Access split database be possible for multiuser environment coz Access actually locks the record while updating any record by one user. Although you've actually helped me in answering the related question i've asked in the forum by 'immediate saving' after fetching an id ...remember! but how it will be possible normally ??

    pls reply meprad@gmail.com

    ReplyDelete
    Replies
    1. Hey Prad, In Access you have something called as 'Pessimistic Lock' and 'Optimistic lock'.. Research on those two.. When the same data is accessed by more than one person, normally only one gets write access, while the other gets only read only.. MS Access seems to do this gently and swiftly.. Immediate saving, will/might not work at all times.. You will find some good resources to help you with that..

      Delete