MS Access and the Perfect Storm
Prior to becoming a Freelance VBA Developer I made a living as a data analyst. My skill set (speciality) was using Microsoft Excel to analyze and summarize data queries from Big Data – TeraData, Oracle, Sybase, Sql Server, you name it. We were looking to identify regulatory and operational exceptions (audit speak for errors). Meet with an auditor and listen to what they want, then help them figure out what they need. Oh there is no doubt there are some clever auditors out there – they know what they want, need and will push the Data Analytics guy to see what else they can test. They are a joy to work with..
There are however, a good number business people that simply don’t have what I call “automation instinct”. Mention databases, fields, tables and they get a kind of look in their eyes like deer in a headlight at night. Surprisingly it isn’t a young or old thing either; I’m shocked to see the number of recent graduates that are not proficient at excel. If you are one of those people or think your skills are not what they should be, may I suggest you read my blog post for some suggestions.
Excel VBA got me to London, Hong Kong and a great job!
In my new life as a consultant, occasionally a client will ask me to do some Technical Auditing, which is always a welcome diversion from writing code. One such task was validating payment to a subcontractor. I learned the payments (a couple hundred grand every month) were all calculated in a Microsoft Access Database. I naively thought “cool beans” I love Access. I met with the manager, did a cursory review of the databases(2) and learned of the Perfect Storm:
♦ The creator ‘Joe’ of the Access Database had retired
♦ The reporting had grown organically – a single user added on reports as needed
♦ The back up person had been in an accident and their return to work date was unknown (no fooling).
♦ Joe had been lured back a couple days a month to run the reports and contact to him was limited to talking to the manager.
♦ The subcontractors had been paid estimated payments because no one knew how to run the reports.
♦ Documentation was non existent.
♦ I was brought in at the end of their review. So I needed to get it done very quickly.
As I recall, there were 30 or more sub queries to run. The trouble was the databases had lots of extraneous tables and queries left over from development and testing and the naming conventions were not consistent. The developer had simply failed to clean up. Put kindly it wasn’t all it could be. After many hours of effort I was able to recreate a significant portion of the payment calculations without any exceptions. I simply ran out of time. The access databases created the correct results but no one knew how they worked. I would have loved to string those queries together with some VBA into a coherent, tight, well annotated one click application. The trouble is your auditor can’t be your developer so I turned my results over to the client and moved on.
I’ve given a good deal of thought into this episode in my now occasional auditing career and although it posed quite a challenge it was a case study in what happens when you have the “perfect storm”. Without being critical here is my list of controls that were not in place.
Key Person Dependency
The day good old Joe retired there was only one person who knew how to run those reports
Failure to properly Supervise the report creation
– No detailed documentation, very limited annotation in the work
– Even without vba to string it all together the process could have been made simpler by the use of a consistent naming convention.
– Elimination of all unneeded tables and queries
– because the report had grown organically and was built incrementally it was a bit of a patchwork.
Things happen – people get sick, have accidents, retire and just move on. The question is are you prepared for the perfect storm?
I am happy to help you re-engineer that beast that your ‘Good old Joe’ created. You can contact me here. Contact me.
Raymond Mills, M.B.A., M.S. has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst. His specialty was using Excel to get Big Databases including Teradata, Oracle, Squel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.”
If you have a challenge with Excel, Access or Word and would like to speak with Ray, You can get his contact details by clicking here: Contact Me