Las diferencias son varias:
Access es ideal para una BBDD con poca carga de registros (15Mb-20Mb), pocos usuarios accediendo a la vez (unos 5) y habilidad para consultas SQL.
SQL Server es para grandes BBDD, varios usuarios conectados a la vez y... Posibilidad de crear procedimientos almacenados, tablas vista y mayor control sobre los datos (Access es muy básico en ésto).
En principio, yo utilizo Access.