Journey to Access: Part 3
A morass of Word documents and Excel workbooks--the invasive plant species of the Windows world--overgrowing your file system like so much digital kudzu.
In Part 1 and Part 2 of this series, I wrote about one employee's mission to rid his new job of mundane and inefficient tasks. That employee? You.
Jumanji
The story is a familiar one for many. An office job filled with haphazardly documented manual tasks. A morass of Word documents and Excel workbooks--the invasive plant species of the Windows world--overgrowing your file system like so much digital kudzu. Into this jungle you wander, your only guide a tattered spiral-bound notebook filled with years of hand-written notes for how to navigate the sprawl.
But you're not like others who have come before you. You're not satisfied with the status quo. You know there's a better way. You don't have any kind of background in programming, but you're smart and you don't mind learning new things. In fact, you relish the challenge. You would happily spend three hours to automate a five-minute task. You have a programmer's mindset.
Your more "normal" colleagues don't understand this. It seems illogical. But you instinctively understand the concept of technological investment. Three hours to save five minutes? Of course that's dumb...if you're only going to do that task once. But what if it's a daily task?
The ROI of automation
To make the math easy, let's say you run that task 20 times a month (roughly once every business day). Your three-hour investment pays off in under two months. By the end of year one, you've netted a time savings of 17 hours. That's more than a 5x ROI! And the three-hour investment was a one-time cost. By the end of year two, you're up to 37 hours saved. That's a lifetime ROI of more than 12x. And it continues to build from there.
Let's get back to our scenario now. At the end of Part 2, you had successfully "written" your first bit of code. One could be pedantic, and point out that you were actually editing existing code, rather than writing new code from scratch. Of course, editing code is the more common activity for most programmers. So that doesn't make you any less a programmer.
And, really, the details didn't matter. The truly important point is that you proved to yourself that you can do this. You can write code. It's not (always) some dark magic voodoo that you can't possibly understand. At its core, programming comprises one of three activities: sequence, selection, or iteration.
Becoming a Word and Excel power user
Over the next several months, you completely transform the documents and workbooks you use to do your job. You automate everything. Maybe more than necessary, if you're being honest with yourself. After all, just because you can do something, doesn't mean you should.
From those humble beginnings, you've moved beyond simply recording and editing macros. You've started writing your own VBA code from scratch. You've also started using the more advanced features of Word and Excel that go criminally unused throughout offices the world over.
Gone are the days of copying and pasting Word documents to create new letters. You long ago set up multiple mail merges that automatically pull information in from your Excel tracking workbooks. You solved the problem of pulling correct report data in Excel by using dynamic named ranges.
It's official. You are a Microsoft Office power user now. Your colleagues have taken notice. They still think you're a weirdo, of course. But now you're the most popular person at work. With all the extra time you've saved yourself, you start automating everyone else's work for them, too.
And why wouldn't you? You enjoy helping people. It bothers you at a visceral level to see things done inefficiently. Plus, you enjoy the work. To recap:
(do what you love)
+ (fix things that annoy you)
+ (help people)
-----------------------------
(no-brainer)Something's still missing
But the more you help other people, the more you start to feel like there's still a void. Something is missing that could tie everything together. Everyone has their own set of Excel workbooks that they are using to track their own piece of the bigger puzzle.
There's some overlap among employees, so you set up a shared workbook for them. This blows their minds since the previous process involved lots of emailing of said workbook back and forth. It's a much better solution than what they were doing, but it's still not perfect.
Yes, the more you help others, the more you realize that everyone's jobs are interconnected. The whole department would benefit from a system that gets everyone on the same page. If a local construction company updates their address, why should four different employees have to update that address in seven different workbooks? What if only some of those workbooks get updated? How would your office know which ones had the right information?
Back to Google you go. It takes you awhile to figure out how to even search for a solution, because "relational database management system" isn't the first thing that pops into most people's heads. But eventually you get there. And now that you know what to search for, you find lots of information. It turns out there are many RDBMS's out there: SQL Server, MySQL, PostgreSQL, SQLite, Microsoft Access, etc.
Many of the articles you read that mention Access do so only in passing and only then to warn you that it's not "a real database" and to stay far away from it. You decide these other people are obviously far smarter than you, so you try all the other options. But none of them is easy to install and integrate with Word and Excel. You read article after article about how to optimize each one; how to load-balance over multiple servers to handle extreme concurrency; and how to implement bulletproof security.
Microsoft Access: The missing piece
But all you care about is how to make your RDBMS work with those Word and Excel files that are piling up all around you. Every time you search for information on that, Microsoft Access keeps popping up. Finally, you give in and try Access. It turns out it's already installed, as it comes bundled with Office 365. You open it up, click "Create new database," give it a name, and you're off to the races.
You reassure yourself that some day you will come back to those other databases (don't worry, you really will). But for now, you just need something that works. For what you want to do--in your specific situation--that's exactly what Microsoft Access is. It is something that just works. And for now, that's all that matters.
Image by Sasin Tipchai from Pixabay
UPDATE [2021-07-30]: Fixed broken link to part 1 at top of article.
