One of the most neglected areas of privately owned company vulnerability lies in the security of computer-based information systems. The larger corporations can afford to have adequate security – but small companies, with limited resources, most often do not.
The popular Microsoft Access has spawned many administrative systems. Nowadays Disk drives and networks are inherently stable – leading to a feeling of misplaced comfort. Few financial officers are aware that just a flicker of the power can cause a complete loss of data – and may threaten the viability of the company.
The Microsoft Access “Compact and Repair Database” facility may overcome the problems caused by a crash. Relinking the Back-End Database may also help. But often, depending upon the extent of the internal corruption, recovery may be impossible.
A major cause of data corruption
After user activity, the Front-End and Back-End Databases swell up in size. When many months have passed, these databases may grow to more than double the original size – if compaction is not regularly carried out.
And if a Microsoft Access Database has not been compacted for some time, the likelihood of an irrecoverable crash is highly likely, if not inevitable.
The Essentials
Here is a list of essential things to do to minimise the chance of data corruption and the subsequent impact, after a crash:
- Set all the Front-End Databases to automatically compact on exit
- Make a Backup of the Back-End Database on a regular basis
- Compact the Back-End Database after the Backup
- The Backup must be stored off-site
- Regularly test that the Access Database can be recovered from the Backup
Without these steps, a company will be at financial risk.
Note that the Back-End database should not be set to automatically compact on exit. However it is possible to create routine to automate the compaction of the Back-End database.
How much Downtime can you afford?
The frequency of the Backup is dependent on the cost and inconvenience of re-entering data since the last Backup. If a Backup is done daily, then on a crash, the maximum of a whole day’s work will need to be redone.
Finagle’s corollary to Murphy’s Law: Anything that can go wrong, will – and at the worst possible time
This worst case scenario (i.e. having to re-enter a complete day’s work) is most likely to occur on heavy month-end processing.
If re-entry of data is not practicable, then a conversion of the Back-End Database to SQL Server will become necessary. SQL Server will guarantee that no data will be lost. There can be no such guarantee with a Microsoft Access database where transactions are not logged.
Audit Trail
Most companies do not have the need to log every change made to an Access database. However it is essential to log some basic information on the last change made to a record. At a minimum this should be User ID, Date and Time of the change.
Of course, with SQL Server, all changes could be automatically logged using a Trigger.