Last week i got a question if they could use a access datasource for PowerPivot and be able to automaticly refresh it. The answer is yes.
I uploaded a Access file to a SharePoint gallery. Openend PowerPivot and started to add the access datasource. The trick is to not use the default URL to the access file but use the SharePoint WebDAV.
When adding the datasource click the browse button and go to your Shared Documents folder and select the access file there. SharePoint will make this kind of connection:
“\sp2010DavWWWRootShared DocumentsNwind.accdb”
When you use this as source you can also schedule a refresh in the PowerPivot Gallery.
Update: Dave Wickert wrote a great blog post on potential trouble concerning refresh of a access database, check it out here: http://powerpivotgeek.com/2010/04/02/working-with-the-2010-office-ace-provider/
Since my server has desktop experience turned on i did not have this problem.