There is no doubt about it; Microsoft Access is a powerful tool. It comes bundled as part of the Microsoft Office Suite. Access is a very powerful desktop database that combines a database, user interface, programming language, and querying & reporting tools into an easy to use productivity tool. To use the GEICO Insurance analogy, "It's so easy even a caveman can use it."
The sole purpose of Access was to create small office databases and to manage lists. Access is not meant to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. It was also not intended for creating line of business (LOB) applications with complex logic and business critical data storage.
Because Access is easy to use, and because it comes bundled with Office, well-meaning business owners will often create LOB Applications using Access, thinking they are saving money and doing right by their business. Unfortunately, this often means that a secretary or some non-professional programmer will create the LOB system using Access. Initially the application is created with no real design. Usually the person will not use relationships to maintain referential integrity, they use the database to maintain state by making copies of tables, and VBA or Macros are constructed without understanding encapsulation of functions or proper error handling. Over time the database becomes a Frankenstein with tables old and new bolted on to the application, large blocks of code that are not used or worse are not commented. The business grows. The database size grows. The number of concurrent users grows. All of a sudden, the Access application is mission critical to the business, but it performs poorly and will not scale with the business. Reality sets in and the business owner seeks professional help.
The Cost of Re-Engineering
Many business owners are often surprised at the price tag, the time to re-engineer the application into a scalable reliable LOB application, and migrate the business critical data that has become the life blood of the business. They conveniently forget that while the initial application only took them a week or two to develop, that it had many hours logged over the years of use as things were bolted on to the application without the proper planning, relational database design practices, or software engineering practices. They also do not understand that there is a major difference between an office secretary or a self-proclaimed “Access Guru” and a professionally trained enterprise architect or software engineer.
I once consulted with a company that paid $8,000 to have an Access database developed to track all their customer orders. This company was an infomercial-type company that sold health products. They came to me after three years of using their “professionally developed” LOB Access application. Over the years a number of Access programmers of varying skill levels had worked on the application. Everything was done on the cheap and worse, the business owner had no way to know the level or quality of what was developed. The database was almost 6GB in size and they had millions of customers; some they auto-billed on a recurring basis. An entire call center accessed this database in two 8-hour shifts. Every morning at 6am, the office secretary got into work to kick off a compress & compact of the Access database so that it would be ready by 9am when the first telemarketing shift started. The compress & compact took 2-3 hours to complete, but without it the system would go down much more frequently than it already did. Wait times for screen refreshes were unacceptable. Worse yet, this database had been utilized for more than just the LOB application. Christmas lists, mail merges, and other tables, reports, macros, and VBA code blocks were mixed into the Access application that had nothing to do with their customer order management. Nobody could tell me what was in use and what was no longer needed. The biggest pain point was that when the database was offline, the company lost anywhere from $10,000 - $20,000 an hour in orders. This did not take into account the loss productivity of the workers, data loss, or customer dissatisfaction. The cost estimate to build a new scalable database and front end application as well as to migrate the data was $125,000 including $50,000 to sift through the system and decipher the logic and migrate the data into a SQL server.
It never ceases to amaze me
what some companies will do to
save money when it comes to
software applications.
Had this company started with an application designed and developed to run on a SQL server instead of Access, they would have saved nearly $250,000 in re-engineering, lost business, and lost productivity costs. While the initial development costs would have been around $50,000, the long term benefit would have been invaluable.
The usual business driver on using Microsoft Access is often cost. Cost is always a consideration, especially in today’s economy. However, if up front cost alone is your biggest factor in choosing Access, or making any IT decision for that matter, then your thinking is both myopic and obtuse. When developing LOB applications, there are many more considerations than cost. These include but are not limited to:
- Ability to scale and grow with your business
- Security
- Time
- Accessibility by the business
- Amount of data to be stored
- Ability to integrate with other systems
- Complexity of logic
- Number of people to concurrently use the system
Know When to Get Advice
If you are considering Access for a line of business application, talk with a professional and experienced software engineering firm first so that you can make an informed decision. Experienced is a relative term. You need a company with a variety of application development experience across businesses both large and small. Too often when it comes to IT, many individuals are specialist or are focused only on their area of expertise. When all you have is a hammer, everything looks like a nail. This decision requires real broad consideration that encompasses both technology and business.
It never ceases to amaze me what some companies will do to save money when it comes to software applications. It is not limited to the Small and Medium Business (SMB) Market, even large enterprises fall into the same short sightedness. The reality is building a Line of Business Application (LOB) or worse a website using Access is like only using a screwdriver to build a house. You need the right tool for the job.
I am sure that there are a number of “Access Gurus” that will have their pocket protectors on fire ready to declare a war on me for the heresy they believe that I speak. But the reality is that I see the business when it needs to scale the monstrosity they have created using Access. Like on the television show MythBusters, you can build a sailboat with duct tape or even a block of ice; it does float and can function as a boat, but will eventually let you down when it matters most, like in high seas. But you have to ask yourself; Why? Why would you do this when there are other methods for building a boat with materials and tools that are tried and true and will produce a real boat and not a functioning Frankenstein. This holds true when using Microsoft Access to create LOB Applications.
Let me be clear; I do not ever recommend Microsoft Access for a Line of Business Application. As an alternative, I recommend SQL Server Express Edition. SQL Server Express Edition is a great entry level database that will scale with your business. What makes SQL Express an ideal solution is that it is free and is a scaled down version of SQL Server Enterprise Edition, that will grow with your business. Today a professional software engineer with modern development tools such as Visual Studio and SQL Server Express Edition, a skilled developer can develop LOB applications just as quickly and as cheaply as those built on Microsoft Access.Let me be clear; I do not ever recommend Microsoft Access for a Line of Business Application. As an alternative, I recommend SQL Server Express Edition. SQL Server Express Edition is a great entry level database that will scale with your business. What makes SQL Express an ideal solution is that it is free and is a scaled down version of SQL Server Enterprise Edition, that will scale as your business grows. Today with a professional software engineer and modern development tools such as Visual Studio and SQL Server Express Edition can develop LOB applications just as quickly and as cheaply as those built using Microsoft Access.
While Access is great for small databases, prototyping, and data manipulation, it should not be used for LOB applications. If you think you are saving your company money by using Access you should reconsider your decision and seek professional software engineering help. The cost you think you are saving in the short term can easily cost you 10 times that in the future.