Journey to Access: Part 1
Many of the best Microsoft Access applications are written by people with no formal background in software development. Your origin story starts here.
Many of the best Microsoft Access applications are written by people with no formal background in software development. Why is that? It's usually because the writer is the intended user. Necessity is the mother of invention, as they say. Perhaps this even describes you.
Let's consider a common scenario. You're working in some kind of office setting. You're using a Windows computer to manage...something. Anything, really. Maybe you're managing building permits and licensing for a local government. The details don't matter. The concepts are similar for many jobs. For the purposes of this article, though, I'll use this example so I'm not just talking in generalities.
When you first started working at this new job, there was some kind of process in place to complete the work. Maybe you got lucky and your predecessor left behind a spiral-bound notebook with handwritten instructions for how to do the job. Perhaps the instructions looked something like this:
Issuing blank permit requests
When someone calls to request a building permit, enter a new line in the "Permit Requests.xlsx" file. Include the date, their name, contact information, site address, and a description of the project. Assign it the next available permit number. Then open the "Blank Permit.docx" file. Print it out. Write the permit number on the printed document. IMPORTANT: Do not make any changes to the Word file. If you do it accidentally, make sure you DO NOT save it. It took Ron 3 hours to fix it that time that I accidentally deleted all the contact information lines.
Receiving permit requests
When a completed permit comes in, enter the date it was received in the "Permit Requests.xlsx" file. Scan the file and save it in the \Permit Requests\ folder. Then send it to Jane's office for approval.
Permit denials
If Jane's office denies the permit, enter the reason in the "Permit Requests.xlsx" file. Then open the "Permit Denial.docx" file. (IMPORTANT: Save a copy of the file in the \Permit Denials\ folder before making any changes!) Enter the permit number in the upper left corner. Replace the mailing address with the requester's address. Enter the reason for denial in the body of the letter. Print and mail the letter to the requester. Record the mail date in the "Permit Requests.xlsx" file under the "Deny Letter Sent" column.
And on and on and on. We're not too far into the job duties and we've already come across one sprawling Excel file and two Word documents. There's also a folder with a bunch of haphazardly named files for Permit Denial letters and another folder full of scanned files. Hopefully, there's some sort of standard naming convention so that you can tie the scans and the permit denials back to the Excel permit requests workbook.
If this already sounds inefficient to you, just wait until you get to the five pages of handwritten notes about the steps required to compile the quarterly building permit reports. (Spoiler alert: That one involves lots of copying and pasting.)
So you come along, look at this sprawling mess, and think, "There has to be a better way." You're no programmer, but you're comfortable learning new skills. You start by adding some data validation to that big Excel file. You standardize the reasons for denials so that it's easier to compile those quarterly reports. You add some conditional formatting to highlight requested permits that never got returned.
Then you take on those Word documents. You realize they're not actually documents, they're templates. And you happen to notice that Word has a "document template" file type. If you save those documents as templates, that makes it less likely they will get accidentally changed. You've only been there a couple of months, and you're already improving things.
Now that you've got a taste for this new efficiency, you want more. You go back to that Excel file. You've gone through the quarterly report process twice. You followed all five pages of instructions to the letter. Lots of copying and pasting. Same exact steps. Every three months. Not so often that you can memorize every single step. But just often enough that it annoys you to have to jump through all those hoops.
You've been reading up on Excel and some of its advanced features. Your third time through the process you decide to try this "Record Macro" thing. You hit the record button, run through all five pages of instructions, then hit the stop button. Did it do anything? It's hard to say, so you delete the worksheet tab you just created and try running your macro. HOLY HELL IT WORKED! Three hours of manual steps and it just ran in under a second. FREAKING MAGIC!
Now you can't wait until the next quarter to run your shiny new macro. Goodbye three hours of manual steps, hello single-button click!
(We'll end this article now, while you're enjoying your euphoric high. We'll pick up next time when you *actually try running* your shiny new macro for the next quarterly report. And those three hours you plan on saving? Well, we'll just have to wait and see how that goes...)
Image by Gerd Altmann from Pixabay
