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.
# 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.
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.
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.
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:
- Create a shortcut on the location of the file.
- 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"
- Followed by the path to your corrupted file "D:\Work Related\OFFICE\Interface.accdb"
- Followed by the word /decompile
- Thus your entire shortcut should look like..
No comments:
Post a Comment