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.
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.
I know from direct experience that reaching those limits is sometimes almost impossible. But, again, it depends on what you want to achieve.
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?
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.
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.