SQL Server vs Ms Access: what should I use?

Tweet

I remember that, when I started developing my first Intranet back in 1998, my best dream was having a database connection. I couldn't use Ms Access or SQL Server, because the company didn't want to spend money on new softwares. I remember I even tried to use txt files and an Excel sheet without great performance results. Then I convinced the company's owner to buy an Office 2000 suite and so I had the opportunity to start playing around with Ms Access.
Today, sometimes I still find people wondering whether they should use Access or take the big step and use SQL Server. Generally, SQL Server is considered much more difficult to manage, while Ms Access is sometimes believed to be only a child toy. I consider the two solutions different in terms of performance and use, but I do not think SQL Server is always the best choice.
How much?
First of all, money might be a big issue in deciding which solution is best. If you're working in house, SQL Server has an Express Edition which is basically free, while you have to buy a Ms Access licence. If you want to use a hosted solution for your web application, SQL Server services have usually higher prices, while an Ms Access connection (without ODBC) is generally free. That is already a big difference in terms of cost, however the licence price for SQL Server (not the Express Edition) compared to Ms Access's is much more expensive.

Performance?

In terms of performance, there's a big difference - as you may already know. Ms Access has different limitations while SQL Server uses hardware resources the best way. At the same time, developer can control the software usage in a deeper way with SQL Server. For instance, CPU and RAM usage, or user connection and security is highly configurable in SQL Server, while Ms Access is quite limited. What matters is the environment: SQL Server is used for big solutions, where you have many concurrent requests. At the same time Ms Access has different limitations in terms of performance.
I know from direct experience that reaching those limits is sometimes almost impossible. But, again, it depends on what you want to achieve.

Programming differences

There are other notable programming differences between the two solutions. First of all. some data types were left out from Access, while some names are different. As an example, SQL Server bit data type is called Yes/No in Access, or Currency in Access might be Money or Smallmoney in SQL Server.
There are quite big differences in functions as well. Some Access functions are more intuitive, but generally there are less possibilities in Ms Access compared to SQL Server. Said that, you might understand that the query creation process might differ a lot in the two environments. Generally there's a lot of people posting questions in tech forums, asking about Ms Access functions and the equivalent solution in SQL Server.
In terms of programming tools, there's not much differences. Personally, when I first started to use SQL Server I found some difficulties in understanding the philosophy behind it. However, believe me, it is not a big deal.

Which one should I use?

It is not really easy to answer the question. I know you won't like the answer, but... well... it depends. I do not believe that SQL Server is always the best choice - knowing that it is a much more professional solution. I manage different web sites with a Ms Access database as back end without troubles. At the same time, where I work, I developed an Intranet that has a SQL Server behind. I wouldn't change the company's web site and use a SQL Server solution. I strongly believe that it would be a waste of resources (and probably of money).
When deciding which solution is the best choice, I would advise to consider what your web application will do, what is the target, how many concurrent users it will deal with, what type of security will be needed, what kind and what ammount of data it will manage, and so on.

Conclusion

I know that this small article can't really analise in depth every single implication and difference between Ms Access and SQL Server. The choice between the two solutions can be difficult and it can determine the future of your web application - not to say its development as a start.
I just wanted to make you aware of the fact that you need to consider the two solution carefully, without jumping easily to conclusion like: "SQL Server is the best because it's a professional solution" or "Ms Access is the best because it is easier to program and it is cheaper".

Let me know what you think in the comment section below.