What is Microsoft Access?

What is Access, really? And why do so many IT people hate it?

There are a lot of mistaken beliefs about Microsoft Access out there. Despite the fact that it has been around for a couple of decades, a lot of IT people still don’t know exactly what it is. And because of their misconceptions, they often have a poor opinion of Access.

It’s a database, right?

Nope. Saying that Access is a database, is like saying that your car is an engine. Or maybe the car factory is an engine. Of course a car includes some sort of engine, but the engine isn’t the whole car or the factory. A car has a lot of other things (like steering wheel, pedals, and paint) that allow you to use the engine under the hood.

So if Access isn’t a database, what is it?

Access is a desktop application development environment. It allows you to build the car that you drive. It includes the ability to create custom forms (screens), reports, and queries. It can link, import, and export data from and to a variety of places, including Excel spreadsheets, SharePoint lists, etc. It has two programming languages to build complex business rules and logic: Access Macros and Visual Basic for Applications (VBA).

A car without an engine?

All of those design features wouldn’t be very useful without a database to store the actual data. It would be like building a car without an engine. So, from the very beginning, Microsoft included a “free” database in the box. Everyone calls that database “Access”, but it actually has its own name. In the early days of Access, the database was called JET, and in later versions, it’s called ACE. For this article, I’ll just call it ACE. An Access application is tightly integrated with this built-in ACE database, making it seem like it’s all the same thing. But it isn’t.

The ACE database included with Access is a great “starter pack” to allow developers to get up and running quickly with Access, which is one factor leading to its enormous success in the market. But as it turns out, this starter database is very capable. Access applications with ACE databases can support large, complex systems with lots of tables and records, and dozens of simultaneous users on a LAN. Far from just a starter pack, an ACE database is capable of handling many business application scenarios.

However, ACE databases have limitations when you need tighter security, 24/7 mission critical usage, remote internet sessions, lots of users, or huge numbers of records. So what do you do? You swap out the engine.

Engine Swap

“Access” is a great name for this development environment, because it allows you to access (connect to) other database engines. One of the most popular is Microsoft SQL Server. SQL Server is a powerful semi-truck turbo-diesel engine compared to an ACE 4-cylinder gas engine. Even the free Express version of SQL Server can run rings around an ACE database. An Access application can be changed from using an ACE database to a SQL Server database with only moderate effort, and the resulting forms and reports can look exactly the same. This change is often called an Access to SQL Server “migration”, because the business data is migrated from ACE over to SQL Server, and then the Access application is switched over to connect to the new SQL Server database instead. It often needs some optimization to use SQL Server data efficiently, but it’s a straightforward process.

Don’t blame the car for the engine’s limitations

Many IT pros disparage Access because they conflate the Access application and the ACE database. They see important business data floating around their network, poorly understood, and not safely backed up, so their instinct is to ban Access. Their concerns are reasonable but misplaced – they are related to the ACE database, not the Access development environment! Once they see that an Access front-end application can connect to a safe and secure SQL Server database, many of their objections to Access disappear.

Don’t blame the tool for the mechanic’s lack of skill

Another reason that Access has gotten a bad reputation is the poorly designed Access applications that seem to be everywhere. Why are there so many of them? Two reasons: Access is easily obtained and has an easy learning curve.

Access is included in most versions of Office, so it allows just about anyone to start playing with it without having to make a separate purchase. And Access comes with a variety of wizards and tools to allow non-technical people (Microsoft calls them “Information Workers”) to quickly create basic applications, so even with zero database design experience, they can start creating tables, forms, and reports.

These Access applications are often merely auto-built forms slapped on top of poorly designed, repetitive tables that are basically just like spreadsheets. These bad structures are difficult to work with over time and can cause support nightmares when the application starts getting used by more people, or when the original creator leaves the company. No wonder the IT folks hate them!

So when should we use Access? Or SQL Server? Or a web application?

Access is the lowest-cost, most flexible desktop application development environment around. An experienced developer can quickly develop a powerful, low-cost desktop application. A comparable web application (we use ASP.NET MVC) will cost at least double.

Access with an ACE database is good when:

• You have less than a few dozen users, and they all use Windows PCs.
• Your users are all on a fast LAN, or they use Remote Desktop from outside.
• Your biggest tables are under a few hundred thousand records.
• You don’t need super high security (no HIPAA, PII, PHI, etc.)
• You are okay with the whole database going home on someone’s USB drive.
• You don’t need 24/7 availability, and you can back up when the system is idle.
  
  

Access with a SQL Server database is good when:

• You have more than a few dozen users, but they’re all on Windows PCs.
• You don’t mind distributing and installing application updates to all the PCs.
• Your biggest tables are larger than hundreds of thousands of records.
• Some users need to connect remotely.
• You’d like the option for a web browser interface for some limited activities (hybrid application).
• You’d like better security, but still not for HIPAA, PII, PHI, etc.
• You need to run 24/7 or save backups while the system is being used.

A browser-based web application like ASP.NET MVC with a SQL Server database is good when:

• You have a large number of widely distributed users.
• You want everyone to have the latest version automatically, without distribution or installation.
• Many users work remotely over slower internet connections.
• Users need to use Macs or need Responsive pages for mobile devices.
• You need the highest levels of security (HIPAA, PHI) and granular permissions.
• You have some level of public or anonymous access available to external users.