| HN Mirror

Until the Access database grows beyond the understanding of the business types and they chuck it over the wall to IT for support. I worked at a company which was 5 or 7 years down the road from a management initiative to "decentralize" IT and back to "recentralized" IT. They'd had some kind of traveling Microsoft Access Training road show come by with trailers and pop-up tents to teach all the business units how to make their own Access databases.

This was before my time there, but I can imagine things were productive at first - the databases would have been simple and well-targeted to their business needs. However they were created without understanding of any database theory such as normalization or what makes a good key. Their authors had not been burned (yet) by the horrors that battle-scar devs, such as time zones, server moves, and decisions by the business to renumber identifiers one might naively choose as keys.

The quality of the database designs varied from bad to decent, but eventually most of the databases came to one fate: the original author left or got promoted, and their replacement knew how to add to the database but not how it worked. He/she came on board long after the Access Training Road Show had left, so hadn't had that training either.

A few business units noticed a sister unit had data they needed, and found it mutually beneficial to smush their databases together into one amalgamation. Lacking an understanding of both database theory and each other's database designs, much less their own, they simply built views, on top of views, on top of views. The resulting queries resembled a coral reef growing out of a subduction fault.

In due time the problems became too large for them to manage. New versions of Access would break things they couldn't fix. It bears noting that even if the databases had remained simple (which they didn't), the technology upon which they were built was emphatically not simple, which is itself a vulnerability. So the business had all these medium-sized systems they didn't understand running on a technology they didn't understand all catching on fire at once. It was the technical debt equivalent of having 100 balloon loans suddenly come due in the same 18 months after 7 years of low payments.

So then it became our problem (in IT). After multiple renumberings, correlating what physical location a store number (primary key) referred to required knowing or guessing the date when the row was created, so it could be looked up an historical cross-reference table. Comparing date/times required knowing the location the row referred to. And every business unit had developed its own jargon, re-using trendy words they heard other business units saying but using the words to refer to other things - so you could never really be sure you knew what an identifier really meant. Because of the mergings you might have one database where table "Accounts" refers to corporate accounts, but "Accounts2" refers to user log-ins.

The Microsoft Access program has versions. The Microsoft Access database format also has versions. This leads to a matrix of combinations you have to keep in your head as to which version of the file they have versus which version of the program they're using, and whether it will be bug-compatible or beneficial to upgrade one or the other or if that's even possible with the combination and database code they have. Don't even get me started on reports generated by Access...