Microsoft Access gets an undeserved reputation as the “red-headed stepchild” of database systems. Most seasoned programmers would not touch it. Tell a database administrator at a large tech corporation that you’re using Access for your database project, and the reaction will be something like, “Awww, Access, how cute.” Well, they’re entitled to that opinion. And to be fair, if you’re the Chief Information Officer for a large corporation named after a misspelled very large number, you probably have giant server farms running large SQL Server databases, and Access, being a desktop database system, may not fit your needs. But small businesses are the perfect fit for Access databases.
But Access works great for small businesses, especially since you probably won’t have the time or money or desire to set up a SQL Server on your network and pay a full-time DBA $70,000 a year for the part-time work it would take to manage it. For you, Access can work just fine. And even if your business does have a SQL Server or Oracle database (if you don’t know what those are, don’t worry), Access is still the best tool for building what we call a “front end” or user interface (also colloquially known as an “app”) to access and work with your data.
So you just installed, or had installed, Office 365 on your work computer. You’re probably familiar with the “W” icon, the “E” and possibly the “P.” But what the heck is that “A” icon for? And what is a relational database system? Let’s break it down.
First of all, can we agree that data is vital to any business? Whether it’s customer lists, employee information, sales leads, inventory, you name it. Whether you run an eBay business out of your home or have a restaurant employing 25 people, data is EVERYTHING! With that said, to put in simple terms, Access, put one way, is an extension to Excel, which many of you are already familiar with.
Excel is great when you have single tables (worksheets) of data. If you’re more advanced, you’ll have some formulas and a few macros thrown in. But more often than not you’ll have multiple tables of related information, for example you’ll want to keep customer data and sales data in separate tables to avoid redundant information. Why keep your customers’ addresses in the same sheet with your orders, while you can keep them in a separate sheet, identify them with a customer ID, and simply use the customer ID in the orders sheet.
Of course, once this starts happening, managing all of this in Excel, while technically possible can be a nightmare. Add a third or fourth table, and forget it!
That, my fellow small business owners, is where Microsoft Access comes in and what makes it perfect for all small businesses, especially those with a lot of data. You can link tables together, thus having several tables that are automatically connected to each other. The screen shots below are from
Access, where you can link multiple tables to, in effect, nest one table within another *:
* For the database professionals reading this: Yes, I know that’s not the correct terminology, but I’m not going to muddy the waters by talking about primary and foreign keys here. “Nested tables” will suffice just fine.
And on top of that you can build forms to manage the tables automatically without having to go into the table (sheet in Excel) at all! Then you can build apps out of those forms.
I recently finished a project for a local law firm that was switching their client database from an old SQL Server (Microsoft’s pro-level database system) to a new one. Long story short, a new app needed to be
built because the old one was no longer supported. Even though the data was on a high end server on a large network, the user interface I built is still done in Access by linking the local to server tables. Again the bottom line is that even if your office does have a complicated networked database server, Access is still the ideal tool for interfacing with it. It saves end users from having to understand any of the technical details. The app does
everything for you! Well, it won’t type or click the mouse for you, but you get the idea. Below is a screen shot of a similar address book app (displayed with fictional data and with client permission).
What else can Access do? Here are some examples:
- Use it to store customer data, and run a report to find out customers that expressed interest in a product or service you’re promoting, find customers who haven’t visited in some time, or to target for future sales, promotions or marketing.
- Use it to write a simple invoicing app, or to keep track of inventory and other data.
- Use it to track sales leads and related data,or manage invoices, storing locations so that important papers can be easily found if misplaced.
There are tons more uses than I can even imagine here. The possibilities are endless: if your business involves data (and which one doesn’t?) then Access has something for you. To quote a blog post by Steve Comeau, a Senior Business Intelligence Developer at Hallam ICS,
“The reason for using Access is that it quickly fulfills business needs for many types of small-scale database solutions. It is a highly productive tool; therefore useful results can quickly be produced that help your business… It comes with everything you need to build databases that are suitable for a small business, department, workgroup, or series of projects.”
In other words, it’s a data storage system and programming language in one package, rather than having to have a separate database system and software and counting on them to reliably “talk” to each other over a busy network. An Access database can even be networked over a small group of computers. This means that each user has the program on their system but the data stored in a separate Access file. Therefore, it allows multiple users to work with it concurrently.
So in conclusion, let Big Tech have their fancy server-based highly technical database systems. They do have their place and may be preferred for larger businesses with tens of thousands of customer records. But if you’re running a small business and need a simple but powerful way to keep track of your customer, employee, or sales data (among many others), give Microsoft Access a try.
The Data Professor specializes in designing and programming Access databases. Contact us for a free consultation today and find out how we can harness the power of your data to increase sales and customer satisfaction. And please share this article with any small business owners you know. We can all harness this simple but powerful tool in more ways than we can imagine.