Tuesday 24 July 2012

DATA CORRUPTION - Avoiding & Recovering (Part 2)

In my previous post I explained to you on what are the various ways you can avoid corruption in the first place so that you are not here. If you are here, well not all is lost. We might be able to do something about this. So keep reading to help yourself out of this mess. 

Recovering from Data corruption

There are only few ways which you can try before giving up on your data, that is the main reason you should make sure that you have them in the right format in the right place at all times. If in case you have a corrupted database the first you can do is: 

# Compact and Repair your DB
Whenever you add new records to the DB, the JET engine allocates the set of memory to the record. Let us assume each row in our DB takes up to 1MB of space (should have been all 255 columns.. LOL). You add 5 records to an empty DB, the size of the file would now be 5 MB. So the next day you decide I only need 3 rows, so you delete two rows from the table. Now if you check the size of the file it will still be 5 MB. Access is intelligent but not a genius, though you have deleted two records the allocated space is still there as wasted space. Unlike other high sophisticated programming language, Access does not have the concept of Garbage collection. So, if you keep doing this over and over and over again you will have only 500 records but your file size might be 1 GB. This not only bloats the DB, it also looses its tendency to properly index and store data, which again leads to inconsistent data. Please use the inbuilt functionality of 'Compact & Repair' to clear out all wasted space and also for smooth functioning of the DB. 



'Compact & Repair' can be accessed from DB Tools

There is a small disadvantage for using the compact and repair nothing too hard. Just make sure that you are the only person who has access to the file at the instance when you perform a manual compact and repair. Specially when you are trying to do a compact and repair on the Back end file of a split DB. This is because, when you perform compact and repair, access opens the file creates a copy and to the copy it tries to optimize the wasted space, reorganization of Auto numbers and then if successful deletes the original and leaves the new compacted and fresh version in its place. Thus we do not want anyone performing a deletion of the file, whilst Ben is still updating a record. This by its own will lead into a corruption. Access also prompts you to perform a Compact & Repair now and then but responsibility lies with you to perform such activity. You can automate this process on close by going into Access Options --> Current Database --> Compact on Close.


# Decompiling your DB 
Compact and Repair solves majority of the Data Corruption that has occurred through orphaned data or incorrect information that has been entered into the tables by people. However sometimes there is a possibility the code that has been written could also cause the error. This is either by unintentional piece of code (or) incorrect syntax (or) not properly closing Sub/Function. This however does not mainly affect the backend, but will however stop you from accessing the records through the Form. Before decompiling the Database, it is VERY IMPORTANT that you make a backup, as sometime this may make situation so much worse that it was before. Hence Decompiling should be sought out as a last resort. 

When you are trying to decompile, you are requesting the Debugger to do the reverse of a compilation, in simple terms you are asking it to leave stuff as it is in the coding region. This will avoid the compiler jumping into conclusion of whose fault it is, thus allowing us to find out where the error might be. The steps for achieving this is very simple and easy. If your Access has hung up and wants to restart, let it run its course; as Access tries to reclaim your data by creating a BACKUP. Which then you can work on to check for errors.
Just copy the file to another location, and try to open the file from there - This might not be official or one standard method, but at times have worked for me thus avoiding the usage of decompiling. 
Steps for Decompiling:
  1. Create a shortcut on the location of the file.
  2. In the path first create a path to the location of the MS Access EXE file normally as,  "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
  3. Followed by the path to your corrupted file  "D:\Work Related\OFFICE\Interface.accdb"
  4. Followed by the word  /decompile
  5. Thus your entire shortcut should look like..
    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"  "D:\Work Related\OFFICE\Interface.accdb"  /decompile


Now click Next, you will have a shotcut, now double click on the Access icon you will be able to see your interface, now quickly press ALT+F11 to open the Code Browser to browse for any errors. Try compiling, if there are any errors it will be highlighted for you to correct. If not then whatever reason the file did not open would now have been resolved and will be working now. 

I have now finally covered the topic of 'How to Avoid corruption and Recovering' from them. These are the ones that I had learnt in my experience and usage with Access. There might be still so many ways. Knowledge is an ocean, what I know is only a raindrop. Thank you for reading this post. Please feel free to share and comment your thoughts.

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.