PowerPivot for Excel is an free Excel Add-in that allows you to easily access data from diverse data sources, easily mashup data within Excel, process large volumes of data (millions to hundreds of millions of rows), use native Excel capabilities to analyze the data. With PowerPivot for SharePoint – which requires SQL Server 2008 R2 installed with SharePoint 2010 – users can publish their PowerPivot Workbooks to SharePoint in the PowerPivot Gallery. They are easily accessed and rendered via Excel Services; data is refreshed via a pre-determined schedule. Additionally – Reporting Services reports can be rendered as ATOM data feeds to PowerPivot for Excel. Providing an efficient means of delivering data in Excel for analysis.
In other words, Excel provides a very powerful front end to allow users to download large amounts of data from the database and process that data into interactive charts and office based applications.
Access doesn’t have any specific functions with SQL Server 2008 R2. Access can still be used as a front end for a SQL Server database.