I would say that SQL Server is a more high performing application than MS Access. You'll want to use SQL over Access where performance matters. You would use Access for the smaller scale stuff; say 50 users, >100 mb worth of data.
SQL Server runs as a windows service where Access is a stand alone application. SQL will cache data in memory to delive information to the users more quickly.
Access ha builtin developer functions. You can create forms, reports, and webpages for displaying your data as well as data entry. SQL has Reporting Services which can have reports generated and saved to various formats and possibly emailed.
SQL will give you access to stored procedures, views, triggers, more constraint functionality. I'm trying to think if there's any language differences and i have to say I don't know. I'm wondering what Access will do with a Create Database command. Also, what about builtin functions such as getdate() and cast()...i know you can run count() in Access.