what's the difference between MS Access and MS SQL? is it
that MS Access has limitations? if so, what are they? is
there a limit to how much data MS Access can handle?
This is apples and oranges as Access is an application development tool that
is used to manipulate and work with data stored in databases. SQL Server is
a database.
What's confusing you is that Access by default uses the Jet database engine
which is so tightly integrated with the application environment that
(uninformed) people consider them one and the same.
Your question would have been more properly phrased as "What is the
difference between Jet and SQL Server?" The answer to that question is that
Jet is file-based rather than server-based. Server-based solutions can
scale much higher both in terms of data and concurrent users, offer much
tighter security, and are far less prone to data corruption than file-based
systems.
One can use "Access" and store the data for the application in any database
that provides a driver that allows Access to connect to it. SQL Server is
one such database so the question "Should I use Access or SQL Server?"
represents a false choice as you can easily use both.
