Microsoft Access Audit Checklist

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.

Neville Silverman, based in Sydney Australia, has been a Visual Basic programmer, Microsoft Access programmer and Database design specialist for many years.

He has created numerous Microsoft Access databases, SQL Server Databases and Microsoft Visual Basic systems for clients. He develops and supports software systems for the small to medium sized business. Administrative systems are custom built to fit company requirements – software solutions that are cost effective, efficient and user-friendly.

Leave a Reply

Your email address will not be published. Required fields are marked *