Simple Databases, and When It’s Time for Custom Software
In our custom software consulting work, we see a lot of companies using Excel or Access as their primary database. Both of these options have their pro’s and con’s.
More importantly, what we also often see is little to no strategy around what kind of database makes the most sense for a given business need. Developing an understanding of what the need is and then tailoring the solution to that will help ensure that your business has a database that scales for the long term.
To this end, I’ll cover a few reasons why Excel and Access may apply for your business, in addition to when it may be time to pursue a custom software solution that is more robust.
Excel as a Database
If your database is relatively small, Excel can serve as a suitable house for your data. One particular benefit to Excel is that Microsoft’s Visual Basic programming language provides a fairly straight forward way for technical minded non-programmers to create customized spreadsheets on their own.
As a result of this ability though, the size of your database is not the only mitigating factor in deciding whether Excel is a good fit for your needs. Over time, as more tabs, custom code, and equations get added to the spreadsheet, we have seen comparatively small databases turn into 500 mb Excel files! At this size, the spreadsheet is slow to open and slow to process data, making your work more difficult.
Beyond this, Excel may require a level of complexity in queries to get to the same answer that would only require a single line code in a more traditional database. Once these multiple queries have been run, they also are not automatically stored somewhere for later review, a function in Excel commonly known as the “Filter” tool.
For the long term, complex Excel spreadsheets can also be difficult to unravel once the original creator has left the company. This can result in hours of wasted time puzzling through code attempting to understand what was originally intended.
Access as a Database
Access has more traditionally been used as a database by companies that aren’t ready to make the jump to a more custom database solution. It is a natural choice for storing larger amounts of data than would be feasible in Excel.
In addition, unlike Excel, Access can re-store data based on a query for later review.
Users of Access do run into some limitations that should be considered before choosing this solution. For starters, Access’ tool for writing custom-code is less robust and less well developed than Excel’s VB package. This is becoming easier for those people using Access Services in SharePoint 2013– read this article for more on that!
Access SQL queries also are not very good at performing more than a simple math equation. Depending on the complexity of your data, this can leave you limited in terms of what kind of processing you can perform on your data after you have retrieved it.
Access, queries must be manually re-run after the input data has changed, whereas in Excel, formulas that call up specific data are automatically updated each time the data changes. This can be an issue if another program is pulling this data without querying the database to see if anything has changed.
Finally, for a growing company, as multiple users attempt to use or change Access at the same time, the program tends to lock up and become slow. This simply is not acceptable for many fast paced industries, such as oil and gas, where access to information is key.
Custom Software and Your Reporting Capabilities
Depending on the complexity and amount of your company’s information, either Excel or Access can be feasible solutions for your company.
If your data sets are not small and simple, and your company has been limping along with a slow, confusing spreadsheet, it may be time to consider switching to a more robust solution. There are a range of SQL Server options that your company can implement, either on your own or with the help of a custom software consultant.
For more on how custom software consultants like Entrance can help your company develop a stream lined and well-thought out database, check out this blog.
Or see how switching from Access to a self-service system in SharePoint helped one client save time and improve the flow of information