Microsoft Access 2013 Versus SQL Server
As you can see from this tutorial, Microsoft Access is an excellent database management system that allows you to do all sorts of things - from storing data, to querying that data, to building forms to help you interact with the database.
But for all its features, Access does have its limitations too. If you do become limited by Access, you might find yourself evaluating SQL Server as a replacement DBMS. SQL Server is a much more robust and scalable solution than Access, and could be just the right solution for you. But there are also plenty of times when Access is the most appropriate solution.
MS Access
Access is more suited for desktop use with a small number of users accessing it simultaneously. One reason you might choose to use Access over SQL Server is for compatibility/sharing. You might need to email someone a copy of your database. People are more likely to have Access on their desktop computer than SQL Server. You'll generally only find SQL Server on developers' computers/servers or on production server machines.
Another reason you might use Access instead of SQL Server is money. You might already have installed Access as part of the Microsoft Office suite. Purchasing SQL Server would be an extra expense that may not be necessary - depending on your situation. SQL Server can also be quite expensive. OK SQL Server Express does come free, but the licensing for the "non-free" versions can really add up.
Another advantage of staying with MS Access is that it has less database administration requirements. Running SQL Server is a different kettle of fish to running Access.
MS SQL Server
SQL Server is a more robust database management system. SQL Server was designed to have many hundreds, or even thousands of users accessing it at any point in time. Microsoft Access on the other hand, doesn't handle this type of load very well. Data can become corrupt when too many people try to access it at the same time.
This makes SQL Server perfectly suited for database driven websites. You should avoid using Access for a database driven website - unless it has a very small amount of traffic (like you and a few of your friends). Even then, you may find yourself having to restart the web server when Access gets locked up due to too many users trying to access simultaneously.
SQL Server also contains some advanced database administration tools that enable organisations to schedule tasks, receive alerts, optimize databases, configure security accounts/roles, transfer data between other disparate sources, and much more.
MS Access & MS SQL Server Integration
It's not always a "Access vs SQL Server" question. It is also possible to integrate MS Access with SQL Server. You can use SQL Server to create your database, then use Access to create forms and interact with it. This can be done via ODBC connection.
MS Access 2013 - Web Apps
One of the new features of Access 2013 is web app integration with SQL Server. A web app is a type of database that you build in Access, then use and share with others as a SharePoint app in a web browser. When you use Access 2013 to create a web app on SharePoint, Access Services creates a SQL Server or SQL Azure database that houses all of your Access objects. As you build your Access database, corresponding tables and objects are created on a SQL Server database.
Although this is a big step forward for Access, it still doesn't solve all the problems companies face with database management. Also, Access web apps is not quite as full-featured as the Access desktop version. But it does provide an extra dimension to the traditional desktop model of earlier versions.
So Which One - Microsoft Access or SQL Server?
Both Access and SQL Server have their respective markets. Access is suitable for office use, with a small number of users connecting to the database simultaneously. SQL Server is aimed towards larger solutions with higher performance, scalability, and security requirements. Also in organizations where IT governance is practiced, SQL Server is the way to go.
Take a look at my SQL Server tutorial to get more of an idea of what SQL Server is. And also check out this SQL tutorial, which is something you'll need to become familiar with if you start using SQL Server.
There's also a tutorial for Access 2016.