Microsoft Access Database Audit Check-list
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 Hard 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 thus threaten the viability of a 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 not be possible.
And if recovery is possible, the process may take an unacceptable amount of time.
A major cause of data corruption
After user activity, the Front-End and Back-End Databases will swell up in size – ALWAYS! After some 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 compressed for some time, the likelihood of an irrecoverable crash is highly likely, if not inevitable.
An Essential Access Audit Checklist
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. There will be problems as the first user exits. However when all users are off the system, it is possible to create a routine to automate the compaction of the Back-End database.
How much Downtime can a Company 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.
See the page on Converting an Access Database to Microsoft SQL Server
Create an Access Audit Trail log
Most companies do not need to log every change made to an Access database. However it is essential to log some basic information, like the last change made to a record. At a minimum this should be User, Date and Time of any change.
Besides the security aspects of an Audit Trail, the Trail will be found to be useful in tracking down programming bugs and user mistakes.