Auditing Your Microsoft Access Database: Tools And Data Processing

Auditing Your Microsoft Access Database: Tools And Data Processing

I’ve talked about the importance of auditing your Microsoft Access database before and it amazes me how developers and power users take this not seriously enough and cut corners.

There are two areas that can be considered the action of carryout some sort of audit to your database application which have been mentioned this is the past.

Auditing Your Microsoft Access Database

Auditing Your Microsoft Access Database

However, I found the following article which also mentions a few of the key areas to auditing and this may provide some insight to your own data and application management - take a look! 

Auditing Your Microsoft Access Database: Tools And Data Processing

Here’s the Ezine article…

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.

Optimising Access Database systems is his speciality. He has extended the useful life of many an Access Database system, avoiding the effort and cost of an SQL Server upgrade.

Website: http://www.nevvb.com.au
Email:  Article Source: http://EzineArticles.com/?expert=Neville_Silverman

Auditing Your Microsoft Access Database: My Thoughts

Some good points are made here but what about how to create a trigger in your Access table? This can be done using the later versions of Access 2010 or 2013 where data macros were introduced for the first time emulating a low level version of SQL Server triggers.

I wrote an eBook early last year on this new very subject in my updated eBook called “How To Use Microsoft Access Macros & Automate Your Application“.
Check out my Preview of the Access Macros eBook.

One point I would be cautious about regarding the ‘Compact & Repair on close‘ feature as with earlier versions of Access (and probably still the case), would not automate and enable this feature as it will take more processing memory and has been prone to corrupt your database and crash when working with general functionality in particular combined with VBA which defeats the very reason why you audit a database in the first place; to avoid corruption!

Finally, I have actually created a data auditing utility called How to Use and Re-Design Your Very Own Microsoft Access Database Audit Tool Utility which may be of interest to you as it gives you full control on how to manage data triggers especially if you haven’t the time to learn Access data macros or are using an earlier version which doesn’t support it.

Simply download the product and plug it in!

This entry was posted in Database Theory, MS Access, Utilities and tagged , , , , . Bookmark the permalink.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>