Microsoft Access is one of the lesser-known Microsoft 365 applications. Often overshadowed by its more familiar relatives — MS Word, Excel, Outlook, and even PowerPoint — Access is nonetheless a useful, powerful tool that can be leveraged to solve a wide variety of problems.
MS Access is a database management system that combines:
- a relational database engine,
- a graphical user interface,
- a reporting tool,
- macro capabilities,
- and a software development language (VBA)
into a single, user-friendly Rapid Application Development (RAD) environment. Access’ ease of use, versatility, and power make it an asset for teams building software to address business issues — think process improvement or process automation — but it isn’t the right fit for every situation.
Here are the pros and cons of MS Access to help you decide whether it makes sense for your scenario.
Pros of MS Access
When they first see what Access can do, most users are amazed by its capabilities, its ease of use, and the sophistication of the programs that can be built with it. Using MS Access database templates, form wizards, and macros, applications can be built in days rather than months.
Use-case versatility
Access is an extremely versatile system that can be leveraged for a wide range of use cases, including:
- simple office inventory,
- custom CRM,
- sales order management,
- advanced multi-user ERP database applications.
Flexible data storage
By default, MS Access stores data in its proprietary file format, but it also offers the flexibility to attach to a wide variety of data sources, such as:
- Microsoft SQL Server
- Microsoft Excel spreadsheets
- XML, TXT, and CSV files
- Dbase databases
- Outlook data files
In general, any data source available via an ODBC connection can be reached from MS Access.
Complete application development
Full-featured applications typically require different tools for:
- database design and development (tables, queries, and so on),
- user interface (UI) design,
- and a reporting system.
Access gives developers and users alike a single solution for building the complete function set of an application.
Multiple programming options
- Macros: For novice users, Access offers a drag-and-drop interface that adds functionality to an application quickly, with no coding required.
- Visual Basic for Applications: As with other Microsoft Office applications, Access harnesses the power of VBA for more advanced program development.
Cons of MS Access
For all its power and versatility, Access is not the answer for every application.
As an application’s scale and complexity grow, or its requirements expand beyond the limits of what Access can do, other system architectures or platforms may be required. Some of the reasons MS Access may not be the best option include:
Scalability
- Database: MS Access database files are limited to 2GB. As you add users and accumulate more data, you may simply outgrow MS Access.
- Users and performance: Microsoft Access is a desktop tool, so it can’t be horizontally scaled across multiple servers and throttled to handle heavy load the way modern cloud-based web applications can.
Older platform
Although some newer versions of Access include web-style development features, MS Access doesn’t support many modern web architectures — such as APIs, webhooks, and microservices — or modern UI libraries like JavaScript, jQuery, Bootstrap, and CSS.
Connectivity limitations
Unlike modern web applications that are accessible from just about anywhere, MS Access applications require a VPN or RDP (Remote Desktop Protocol) connection to use.
Alternatives to Microsoft Access
Access is a robust development tool whose built-in capabilities can, in many cases, each be replaced by a dedicated solution. As a result, the alternatives to Access are nearly unlimited, as different databases, UIs, programming languages, and reporting tools are layered into an application.
A few example alternatives:
Mobile responsive customer portal
A web portal offering data access from a desktop or mobile device, built on a services-oriented architecture that makes third-party software integration easier:
- Database: MS SQL or MySQL
- Business logic / backend: C#.NET API
- Front end: React.JS, Blazor, or Angular
- Hosting: Azure or AWS
On-prem operations MVC app
A mission-critical business application running on an in-house web server, used by the staff of a growing nationwide organization to perform daily business tasks:
- Database: MS SQL or MySQL
- Architecture: MVC (Model-View-Controller)
- Reporting platform: Crystal Reports
Data warehouse driven BI dashboard
A quasi-real-time management reporting tool displaying sales, production, inventory, and accounting metrics across the company for a variety of regions, departments, and dates:
- Database: Azure SQL Service
- Reporting platform: PowerBI
What about MS Excel?
Because Access is one of the lesser-known applications in the Office suite, many users overlook its capabilities and try to use the more popular MS Excel to collect, store, and report on data.
Excel is a very powerful tool for data manipulation, but more often than not it ends up improperly forced to act as a database system rather than a spreadsheet application. In other words, Excel should not be used as an alternative to Access.
Converting / migrating from MS Access to MS SQL
If you have an Access-based application running today and you’re considering migrating to an MS SQL application, our guide on how to migrate from Access to SQL Server outlines the major steps of a migration. It also offers suggestions to help alleviate issues with data corruption, size limitations, and performance. You’ll find it linked in the reading list below, and our MS Access Conversion page covers how we approach these projects.
MS Access Conversion Case Studies
Modernizing Business-critical Legacy Software
Legacy Software Update – Support, then full rewrite – Auditing Software
Custom Business Process Automation Software – Time Tracking and Reporting
You can browse these and other projects on our case studies page.
Next Steps
If converting your MS Access application makes sense for your business, check out our MS Access Conversion page — and feel free to reach out if you’d like to discuss it further.
Interested in learning more? Check out these articles:
- Microsoft Access Database Corruption – Why it happens and when to repair vs replace
- How to Migrate an MS Access Database to an SQL Server Database
- Custom software can be a competitive advantage for small businesses, but not always
- Why is custom business software expensive?
- When to Rewrite your software
- Why do I need a data warehouse?
You can find these and more in our articles section.