This article looks at some of the differences between Microsoft Access and SQL Server.

Both Access and SQL Server are developed by Microsoft. Both are relational database management systems. And both have a large presence in organisations across the world.

But between Access and SQL Server, there are some significant differences.

A Technical Comparison

First of all, there’s the technical side. SQL Server has much higher specifications than Access.

For example:

• Database size: Access databases can have a maximum size of 2 gigabytes (minus the space needed for system objects). SQL Server databases can have a maximum size a maximum size of 524,272 terabytes with a maximum data file size of 16 terabytes.
• Simultaneous users: An Access 2016 database has a limit of 255 concurrent users, whereas each instance of SQL Server 2016 allows a maximum of 32,767 simultaneous user connections.
• Number of objects: Access can have a maximum of 32,768 objects per database. SQL Server can have up to 2,147,483,647.

For any medium to large business, this makes SQL Server the obvious choice for enterprise level solutions and other mission-critical applications. Medium to large organisations can have some pretty large requirements when it comes to storing data. They can also have a lot of people trying to access the database at the same time. So at that level, SQL Server will win every time.

I Need a Database… QUICK!

While SQL Sever is a much more powerful system than Access, there are many cases where it makes more sense to use Access instead of SQL Server.

There are times where a more modest database might be all that is required. In these cases, Access will often fit the bill perfectly. SQL Server would be overkill.

One thing that makes Access attractive to individuals and small business is that a relatively non-technical person can whip up a database in no time using Access.

Many business professionals and subject matter experts know how to use Access but have no idea where to start with SQL Server.

Even if they have no Access skills, the learning barrier is much smaller than with SQL Server. In many cases, Access is already installed on their computer (via the Microsoft Office suite of products) and so they can just fire it up and fumble their way through.

So such individuals can create an Access database themselves without any outside help. They can do this to keep track of projects, or for inventory or bookkeeping purposes, etc. If they had to use SQL Server, they’d have to pay someone else to build the application.

Features

Access includes features that make it easy for non-technical users to perform tasks (such as its forms, its wizards, it’s macros, etc).

Again, this makes Access the ideal choice for those who don’t have the time, money, or inclination to use an enterprise level RDBMS.

A non technical person can not only create a database, but they can also build an interface that allows them (or others) to enter data and run reports. And it can be done using wizards to guide them through the process. To build such an interface with SQL Server would require another technology, not to mention the skills related that technology.

On the other hand, SQL Sever includes advanced features that aren’t available in Access (such as stored procedures, more granular security features, etc). For those with the skill set, features such as these are often viewed as crucial elements in developing any database solution.

It’s either Access… or back to Excel

Access is not intended to be an enterprise level solution. Its purpose is to fill the gap between using an Excel spreadsheet and building an elaborate SQL Server driven application.

Access is a step up from Excel as far as storing and retrieving structured data. Many times it’s a decision between;

• Keeping data in the Excel spreadsheets, and trying to reorganise them as the data grows  or
• Whipping together an Access database and storing it in there.

If Access wasn’t an option, it would be back to the spreadsheet.

Also, in the corporate world and government departments, many administrators, managers, assistants, and clerks use Access without any help from IT. If they were to get IT involved every time they needed a database, they probably wouldn’t bother and just resort to Excel.

Scalability

SQL Server has much better scalability than Access.

Over time, businesses can grow. Sometimes very quickly. All of a sudden their data storage requirements go through the roof. Their database is approaching 2 gigabytes in size… only problem… It’s an Access database!

They also have more users and they’re getting funny errors due to having too many people using the database at the same time.

Now they have to rethink (and re-implement) their whole database setup… fast.

If they’d used SQL Server, there wouldn’t be an issue.

Sometimes, starting out with an Access based solution makes sense. Then if the database requirements grow over time, upgrade to a more sophisticated system like SQL Server.

There are other times where a system like SQL Server should be used right from the start. Either the requirements, or the projected growth, dictate that a more robust solution needs to be in place right from the start. It can be quite time consuming and costly to switch database systems at a later date, so starting with the right solution is usually better.

However, you don’t always know how the requirements are going to grow. In many cases, there will never be a need to upgrade from Access. Often a project might start off small, as a side project – perhaps even by a junior staff member of a an organisation. In many cases the project will either stay small or disappear altogether.

Every now and then, such a project might grow to the point where it has become a top priority for the department or even the whole organisation. In this case, a decision might be made to upgrade the database to an enterprise level solution.

However, if an organisation already has the skills and equipment, it’s usually better to use SQL Server whenever possible, even if you don’t anticipate a lot of growth.