To Access or not to Access

It was hard to resist the pun.......

So my significant other threw me a challenge the other day, she has a a few huge excel sheets that she needed to compare and find the updates in the new versions and seems like this is something she and her team ends up doing on a monthly basis. As expected its time consuming and tedious. Her ask was simple, can you import them in access and write a SQL to do the comparison? Of course, it was a trivial thing, import the files, run an outer join and viola....

Then I got to thinking, why not make her life a little easier and automate the whole process. The challenges here - to start off - is MS Access the right tool? The deep buried developer in me started protesting immediately...

"Seriously? Access? Access is not even a complete DB? Are you nuts?"

The more serious part of my brain kicked in...why not? It's a tool she is familiar with, if things change in the future its something she can look under the hood and maybe change things if needed, keep it simple we are not building the next LinkedIn here and last but not the least does not need any more investment on her part (and my time is free as far as this discussion was concerned).

That discussion done I took a serious look at MSAccess in its current form in Office 365 and found a decent set of tools under the hood. VBA and form capabilities in Access have come a long way and it does allow you to write a decent bit of code and has the power of Visual Basic Scripting available. And if you take the time to look at the Microsoft documentation, they do have fairly large number of examples published. And a little bit of Googling on StackExchange brings up a lot of knowledge base.

So with that I set about creating a simple form for her which

1. Copies over the current data to previous month.
2. Imports the data given a csv file.
3. Compares it with previous months data and displays the updates.

So overall a couple of days of fact finding (code hunting if you will), I have a bunch of nifty buttons for her to click and get the updates she needs. Along the way I learned quite a few things that I would not have guessed Access could do and also learned that CScript the new scripting tool available on windows which can do a lot more things including open access and run scripts within it. I will detail all of these (along with code snippets) in a later post but for now here is an image of the final product.

Still waiting for the review from the person I built this for so 😥 🤞......