When people talk about Microsoft Access, you often hear it being referred to as a database. This is partially true, however there is a lot more to it than just storing data. Access is an application that provides the user an easy way to enter data and create a database. This is actually pretty different than say, SQL Server, which is arelational database management system (RDBMS) that just holds data but doesn’t come equipped with a user-friendly interface to enter or show data – all that stuff has to be developed.
Really, Access is a three-part tool. There is the UI with which to input data, the Database to store it, and Reports to view the data. So Access can be an amazing tool for some companies. So what are the situations where a company might choose to move from Access to a RDBMS like SQL Server, MySQL, or Oracle? Read on to find out.
How much data do you have?
Microsoft Access is a good tool for a small company with not a lot of data. But it has its limitations as the size of your data grows. Once your dataset reaches a certain level, the performance of an Access system will degrade. Once this happens, the user experience is not as good and eventually people are not going to want to use the system.
If your company lives and breathes on your data as the crux of your business and you aren’t backing up your Access database nightly, you are probably going to want to move to a RDBMS. There is no option for an auto backup on Access. Furthermore – the database is really just a flat file, so if you have a large amount of data in it, it could take a long time to copy the file every day.
Have you ever worked really hard on an Excel spreadsheet or a Word document and forgotten to save it and your computer crashes? This is basically the same as not backing up your Access files. Typically, a RDBMS has a backup on a different server and you can configure your database to run an auto backup for you. Then you never have to worry about losing pertinent data.
How accessible do you need your data?
A limitation of Access is its accessibility (no pun intended). Access is usually only on your desktop computer or on a network drive, and can only be accessed by a few users. The more users that use the Access database, the more problems you have. There is a web-based Access solution now, but it still has the limitations of being a flat file.
There are some that would argue the point saying you could create a customized web interface to your Access application, but this isn’t really the best architecture. Technically – I could create a web interface to almost anything but that doesn’t mean I should!
With an RDBMS, you can set up multiple permissions and roles for different levels of access. Access doesn’t really offer this kind of flexibility in user roles. Which also means that it is harder to restrict access to the dataset, and lots of people can get in there and mess with the data which can cause your company some problems.
Additionally, a RDBMS can run multiple queries at the same time, and you can configure your interface to show the reports alongside the data input to easily make changes on the fly and report on those. You can also have multiple people in the system at one time using it without any noticeable performance decrease. Where Access can bog down with 2 users, a RDBMS can have hundreds.
How big is your company?
Let’s use the analogy of school years. If your company is in its infancy or even Elementary School, Microsoft Access is going to be a great solution for you. A startup or a small company with less than ten people using Access can really benefit from the solution. However, once you start heading in middle school, high school and especially college, you really ought to move to a RDBMS. Trying to manage that amount of growth with Access is going to be difficult.
Access is an inexpensive one-time fee. So small companies really can do well using it. A SQL Server solution requires a license and on top of that, you either have to get hosting from somewhere which is an annual cost, or buy your own server which is not an inexpensive or easy endeavor. Even if you go with an open source solution like MySQL, you still have to have hosting.
Also, you have to have someone that can develop a web interface to the data so that your users can, well, use it! Access comes with an interface and the ability to run reports right off the bat, even though it isn’t terribly customized it can be the right solution for some.
We have been working on all kinds of database development for years, so we can help you understand if it is the right time to move on to a relational database. Give us a call for more info!