The possibility of corruption in an Access database is something which we must be aware of, and take preventive action against. Corruption prevention practice is more of a development mindset rather than any specific task or set of tasks that we can perform to reduce the risk. The practice comes with understanding of some fundemental risks for the corruption, and working to reduce those risks to a minimum.
Over the years, there has been a great many articles written and posted in various websites, blogs and discussions on the topic. The purpose of this article is to provide a place to find these resources, rather than a rewrite of all the work that has been done by many others. This article will cover some basics on what to look out for and how to handle it, but mostly it will provide an area where the great many existing resources can be referenced.
What is Corruption?
Corruption, for our purposes, is an abnormality within a file that keeps the file from functioning properly. File corruption can affect any portion of a file, and Access being a file based system is subject to such problems. There may be file format problems, which prevents us from opening an Access database, there may be problems with the definitions of objects in the file, which can cause problems when trying to use Forms or Reports (or any other object), or there can be corruption within the database records stored in the file, which causes problems with the data that we entered into the database.
Understanding Causes & Risks
It is important to understand that in the greater majority of cases, the actual cause for corruption is not known, and that many of the practices which are advised are nothing more than what the collective experience of the Access user base has determined to be a means to reduce the risk. In other words, doing "A" instead of "B" may be accepted as a corruption prevention practice, but this does not mean that doing "B" in itself is an actual cause of corruption, nor does it neccessarily mean that doing "A" is actually a risk reducer... because we are rarely aware of the actual cause, it is difficult to pinpoint specific rules as to how we should approach the risk reduction. With this said, we should realize that some "corruption prevention practices" may not have any real effect on the possibility of corruption, but may just be that it has been noticed less under one particular scenario over another.
File Corruption vs. Data Integrity
Another distinction which must be made is with file corruption itself versus data integrity as a result of poor database design. In either case, one can liken it to "corruption", based on the fact that in either case we may have data not behaving as expected, yet there is a fundamental difference in the fact that file corruption is an problem within the file, whereas data integrity as a result of poor database design is usually a problem that we've created ourselves, and thus we have a much more direct control over it. Some consider data integrity problems due to poor normalization a form of corruption, where others view only the file corruption as actual corruption. For either school of thought, it bears mention that the final result can be the same: unusable data that didn't do what we wanted or expected. For the purposes of this article, the focus will be on the file corruption end of things rather than on the normalization end of things. For further information on normalization, please refer to the Normalization subcategory of the Articles Table of Contents.
File Format Problems
File format corruption occurs when something goes wrong with the file structure itself, rather than any particular object within the file. One of the more common behaviors of file format corruption is error messages when trying to open the file, often indicating that the file format is incorrect. File format problems are particularly prelevant when attempting to open a file in it's non-native Access version (e.g. - opening an mdb file in Access 2007, or opening an accdb file created in 2007 with 2010, or vice versa).
Object corruption within the Access database is generally the most common form of corruption. Forms, Reports and Modules are particularly susceptable to corruption and may behave in ranges from subtle oddities to crashing Access. It is important to note that even at the smallest signs of corruption, we treat the object as a time bomb and get the problem fixed as soon as possible.
Data corruption is a situation where the data stored within tables becomes corrupt due to causes beyond our immediate control (i.e. - excluding data anomalies created by non-normalized data models). A common sign of data corruption is the presence of asian characters within records.
Implementing diligent corruption prevention practices is easily the most important step we can take with regards to all types of corruption. The importance of not allowing corruption to happen in the first place is second to none. In many cases, if for some reason the file or objects do become corrupt, it is often too late for a complete reversal by the time the signs start to show, meaning that the possibility of have to rework parts of the project is high. This can be as simple as copy and pasting code from an old module into a text editor, then into a new module, or as devastating as having to rebuild entire portions of a complex application.
Preventive practices vary greatly from developer to developer. There is not a single list of hard and fast rules to follow, but rather, depending on the developer's general practices and approach towards development, certain prevention practices apply more strongly than they might for others. It is important to make the distinction that everyone works differently, and the individual developement styles must be paired with the possible corruption risk factors per developer in order to implement the most practical set of preventive practices.
The single most important practice that can used to prevent significant loss of data or work is a regular backup schedule. Regular backups do not necessarily prevent corruption from happening, but nevertheless they should be the first tool in a developer's toolkit. There are a number of fairly standardized backup schedules (primarily developed for server drive backups) that not only provide recent copies but also maintain older copies without overwriting.
It is important to use a backup system which maintains older copies as well as very recent copies. In severe cases of corruption, it may be required to go back a month or two in order to find a clean copy of a particular problem object. If the backup system simply overwrites yesterdays backup with no retention of older copies, corruption may be irreversible, forcing us to create objects from scratch.
|This page has been accessed 749 times. This page was last modified 08:57, 10 October 2012 by SteveH2508. Contributions by Jack Leach and BananaRepublic Disclaimers|