MS Access Risk Management

I was an officer in the US Army for five years after college.  As you might imagine, managing risk is a big part of that job.  I had a fair amount of formal and on-the-job training with risk management techniques.

The first step in the process of managing risk is proper risk evaluation.  That starts with building a risk matrix and assigning possible outcomes.  

Risk Assessment Matrix

The Army risk matrix has two axes: Severity and Probability.  An unlikely event with negligible adverse impact would be considered low risk.  On the other hand, an event with catastrophic consequences that is very likely to occur would carry extremely high risk.

Risk mitigation

After identifying and classifying risks, the next step in the process is to develop controls to mitigate those risks.  The effort required to implement each control should be proportional to the level of risk for a given event.  For example, one would go to great lengths to mitigate an Extremely High Risk event.  A Low Risk event might require no mitigating control beyond acknowledging that the risk exists.

MS Access risk management

Many an IT director loathes Microsoft Access.  Many a professional Access developer loathes the caricature of Microsoft Access as an inherently flawed development platform.  Who is right?  

As it turns out, they're both right!  To explain the seeming disconnect, let's apply the concepts of risk management to Access application development.

The Access risk matrix

For the "probability" axis, we'll clarify that to mean the probability of a critical error occurring in the application.  It's difficult to predict the occurrence of any single error in a software application.  But, we can assess the likelihood of such an error by examining the developer's programming practices.  A well-designed application that follows best practices will generate fewer critical errors than one created haphazardly by an inexperienced developer.

The GOOD, BETTER, and BEST definitions are based on the checklists I put together for evaluating Access applications.  If you contrast the Access risk matrix closely with the Army version, you will notice a difference beyond the changes to the row and column headings.  I assign greater risk to cells B-II, B-III, and C-I.  That just felt right to me based on my experience.

Also, I left Access applications out of column E entirely.  Access is a more robust development platform than most give it credit for, but it does have its limitations.

Applying the matrix in real life

Access has a very low barrier to entry compared to almost every other desktop development environment.  Someone with no background in programming is unlikely to fire up Visual Studio and start cranking out a useful business application.  But that sort of thing happens all the time with Access.

Let's say a power user has to compile a monthly report that pulls data from five different Excel spreadsheets.  This process takes her four hours to do manually.  She realizes that if she imports the data from those Excel spreadsheets into Access, she can run a saved query that returns the information she needs.  This allows her to complete her work in two hours instead of four.  

If this is her first foray into Access, she is likely to produce a BASIC Access application.  That is, one that does not meet my minimal standards for a GOOD Access application.  This puts us in Column A, where we can expect Frequent critical errors to the application.  However, if we assume the impact on the company is limited to the two-hour monthly time savings, then a failure of the program would be negligible (Row IV).  Using the risk matrix, we would rate the application a Medium risk.

The disconnect

Professional Access developers operate in Column C.  The best ones operate in Column D.  These Access developers also know and respect the limits of an Access application.  Using a robust back-end database, such as SQL Server, a well-designed Access application could go toe-to-toe with any desktop-based ERP system.  But not even I am crazy enough to recommend using Access to automate the cooling rods at a nuclear power plant.

Most IT directors, though, spend their time dealing with Access applications that live in Columns A and B.  And while most of those applications begin their lives in Row IV, they often make their way up to Rows III, II, or even I.  Since the Risk from A-I through B-II is Extremely High, you can see why IT directors feel the way they do about Access.

What to do about it

For Access developers, the obvious mitigation strategy is to adopt software development best practices to move from column A towards column D on the matrix.  For IT directors, the key is to identify and track the Access applications within their networks.  When home-grown applications start moving up from row IV towards row I, assess the quality of those applications and make plans to address problems before the risk gets too high.

What not to do about it

In response to this reality, some IT directors ban Access outright.  I think that's misguided. In many cases, those would-be Access applications will still get created.  They'll just get cobbled together in an even-more-fragile Excel workbook held together by duct tape and a thousand VLOOKUPs.  In other cases, those Access applications will never get created at all and the company will pay the opportunity costs of lost productivity.

As for those Access applications that inevitably move up the matrix from row IV to row I, think of those as proofs of concept.  For every ten or twenty Access apps that see the light of day in row IV, only one or two will become business critical.  For those that do, though, your employees will have successfully identified the need for a significant software investment.  Without that Access proving ground, you might invest a bunch of resources in a software project that addresses the wrong needs.

Image by Clarence Alford from Pixabay