Did you ever have one of those days where you swear you are living a Twilight Zone episode? Well today is definitely one of those days for me, lots of cosmic energy floating around me today for some reason or another.
It must be my grandmother who passed away when I was in High School. She probably knows how depressed I’ve been lately and is trying to cheer me up for the holidays, and now on top of that I’m a sick little pup as well. I have such a horrible sinus infection which is starting to trickle down into my lungs and I feel like total crap, so I’m making myself a huge pot of my homemade chicken noodle soup which is usually a two day process for me; it is SO incredibly delicious that there are some people who swear it will cure anything. It stinks when you don’t feel well and you have no one to take care of you, it’s just not the same thing babying yourself. I just want to crawl back in bed and sleep for a week but as you know WORK comes first, so here I am sitting at my desk in front of my trusty laptop because I want to tell all of you something.
I decided to do a very quick post on this because it really is my ULTIMATE PET PEEVE and something quite frankly I thought I was done with forever once I left the corporate world, but as usual…….. Ahh, the stuff that finds its way across my desk! I just signed another new client, a lawyer who contacted my company looking for a very basic software interface to manage his client files. I know this is ironic right because I keep saying I’m done signing anymore offline clients, but who can say no to such easy money? Software and Databases my specialty.
I received his specs for the project and soon realized that he wanted this masterful “interface” all within Excel, because like a lot of you it is something he is very familiar with and comfortable using; which is usually not a problem at all except when trying to substitute it for a true RDBMS. So I’m just going to say it right now once and for all, Excel is NOT a Database, Excel is NOT a Database, EXCEL IS NOT A DATABASE!
But sometimes in order to please a client you have to think outside the box and find a creative way to do things they way they want it done, even if it means it is not the correct or most efficient way to do it. The fact is, he does not want a database even though that’s exactly what he needs, he wants it all in Excel so if my company won’t do it for him someone else will. And as someone fluent in VBA I know it can be done to meet his needs today and probably well into a year or two from now. Will it eventually lead to problems, yes, are there major concerns I have especially regarding security, yes, and I’ll make sure to bring all of those to his attention before we get started.
So I decided my company would create this for him because when you create proprietary software and applications for a company to use in-house that’s recurring income not just a one shot deal. Who will he turn to if things go wrong, if he need something added, if and when he finally decides that he does need something more powerful on the backend like a database, he’ll call the same company who created what he’s currently using in the first place. It’s just good practice, we know the code because we wrote it, we know his business, we know his data, we know his company’s business rules and how to incorporate that new expanded flow into it; so as usual I’m looking at the bigger picture here.
So why do so many people use Excel as a database? For starters it comes standard with every version of Microsoft Office, Access however does not. You need at least the Pro version of Office in order to get MS Access. Secondly it’s easy to use, not many really know how to use Access properly because it requires a knowledge of data structures and a much higher level of technical processing. Excel is a spreadsheet application, Access is a relational database and although both are used to store and manage sets of data, there are quite a few differences between the two, specifically how they process, manipulate, use and return that data.
Excel stores data in cells which are then stored in rows and columns, and is best used for data analysis and other complex calculations and advanced formulas, charts, graphs and pivot tables. Access like all relational databases stores its data in tables and divides the information up into small logical segments stored into separate tables that link to each other (relate) through the use of Primary and Foreign Keys, referred to as Data Normalization. Access is used for data management and the automation of common tasks and event. It’s able to handle much larger quantities of data than excel, perform complex queries or filters which make it a more powerful choice for data mining, and is also capable of displaying data subsets & structuring. Not to mention it supports data access for multiple users at once, which as you know is priceless. We’ve all been there right, one team of people sharing ONE corporate spreadsheet file and you try to open it so you can update it with the new information that your boss wants PRONTO only to get that famous “FILE IN USE” message, “do you want to open a read only copy”? And then to find out on top of it the person who has the file open on their locked computer has just left for an hour and a half lunch and isn’t even working on it.
In a perfect world companies would learn to harness the power of making all the Microsoft Office Tools work together, similar to a Global Database Application I created for Novartis Pharmaceuticals Corp. where I worked for 5.5 years. I wanted to write it in VB my favorite language, but just like my new client, they wanted Excel and Access. So I created an entire automated Access database with an automated Excel front end, which also included a high end reporting system based on a select query which would display the info in an Access Report, an Excel Spreadsheet, or a Word Document, it even created entire Power Point presentations on the fly.
Creative solutions to complex problems leads to a new and unique flow of logic. In the end it really is what you determine to be best for your particular needs even if that happens to be Excel……..Please just don’t refer to it as a DATABASE!