A look at the kind of custom software MS Access is best suited for.

I have encountered a lot of misconceptions about database applications built with Microsoft Access. When does it makes sense to use Access instead of some other database software? Here is some of what I have learned in the last few years of working with Access.

Access as a development tool

While Access can serve as an end user tool for working with data - in the same way that Excel is an end user tool for working with numbers, my interest is in Access as a development tool. Professional "turn-key" custom software is what I'm talking about here. MS Access is my preferred tool for projects that fall into the Access sweet spot.

The sweet spot

The sweet spot for MS Access is a size and type of application where the benefits of Access as a development tool are maximized and it's limitations have the least impact. Within the sweet spot you get remarkably rapid development, which means low cost. Put another way, very good ROI. A well designed, professionally implemented Access application will boost productivity, reduce errors and generally make life better - all at a cost far below most other development tools. As long as you're in the sweet spot.

Strategic solutions

This is the key point - MS Access is a strategic solution, not an enterprise solution. What we're talking about here are workgroup applications, those used by a small to midsize number of users working with small to midsize chunks of data.

Size matters

What does "small to midsize" mean? Generally speaking it means up to 40 users and data tables of 500,000 rows or less. These are just 'ball park' numbers because a lot depends on how many users are on at the same time, the type of processing that's done, and many other factors. At the extremes I have seen Access applications successfully handling 100 users - and - I have seen Access ground into the dust by 10 users with very heavy processing of big data.

Client server

While Access can be used to develop a fine front-end for a client server database like Sybase, SQL Server or Oracle, it may not be the best way to go. Many of the features that make for very rapid development are lost and it's limitations start to hurt you. In many cases the scalability - and additional overhead - of a true client server database is not really needed. If it is, you're probably moving out of the sweet spot. This is where I start to consider other tools.

Web development

In two words - not much. In my experience the Access data engine simply can't handle the load of most web applications. It can however, be the right tool for a company intranet site or a web site with limited traffic.

Where do you find Access applications?

Everywhere. In large corporate environments, small businesses and non-profits. The Access sweet spot happens to include a range of applications that there is great demand for.

Large Companies / Investment Banks

In enterprise environments Access applications are used to "fill in the blanks". Group level tasks that are not handled by enterprise systems. Process tracking, workflow and reporting applications are common. I have developed applications to produce reports that gather data from several disparate domains and display it in a format to meet specific requirements. As well as applications to work with processes-specific information that doesn't quite fit into the design of existing enterprise systems. Or to handle a new process until an enterprise system can be implemented.


The other end of the scale are non-profit environments. Many non-profit organizations need to manage interaction with donors and volunteers, to plan events, send mass mailings etc. In most organizations an Access application is a good fit because a lot of functionality can be provided at a low cost.

Small Business

And in between are small business environments. In this world Access applications can sometimes handle the kind of line functions that are reserved for enterprise systems in multi-national companies. There are businesses out there happily chugging along, running their entire business on Access applications that I built for them. And - there is also a need for strategic solutions in the small business world.