You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
OData is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. There are already several OData producers like: IBM WebSphere, Microsoft SQL Azure, SQL Server Reporting Services; and live services like: Netflix or DBpedia among others. SharePoint 2010, is an OData provider as well and this enables Access Services as an OData provider as well. The following walkthrough shows how to extract data using OData from a published Access Northwind web template and consume it using Microsoft PowerPivot for Excel 2010.
Publishing Northwind
First, instantiate the Northwind web database and publish it to SharePoint.
Accessing OData
Access Services 2010 stores its data as SharePoint lists; therefore, in order to retrieve tables through OData we’ll need to follow the same recommendations that apply for SharePoint lists. There are a couple blog posts with more details on SharePoint and OData here and here. For our Northwind application, the main OData entry point is located on http://server/Northwind/_vti_bin/listdata.svc. This entry points describes all tables provided by the OData service, for instance, in order to retrieve the Employees table through OData, we would use http://server/Northwind/_vti_bin/listdata.svc/Employees. Additional OData functionality is described in the OData developers page.
Consuming OData
One of the applications that consumes OData, is Microsoft PowerPivot for Excel 2010. In order to import data from Northwind into PowerPivot we can follow these steps:
a) From the PowerPivot ribbon, “Get External Data” section, select “From Data Feeds”.
b) Enter the OData entry point, for this scenario: http://server/Northwind/_vti_bin/listdata.svc
c) Select the desired Northwind tables in the “Table Import Wizard”.
d) Finish the wizard to retrieve the data from Access Services.
The Northwind data should be now imported in PowerPivot and ready to be used from Excel.
Comments: (2) Collapse
I'm having a problem creating a drop down combo box. It its referencing the last data that is entered and changes all drop down combo box in the database.
Ryan, it's very cool that one can get to an Access Services app's data via odata. In the post you said the data is imported to excel; is the data really pulled into the excel file? Can one query the odata feed just like a regular web service from excel? Or is the import required? What about an Access 2010 client tapping an odata source? odata is very flexible; it'd be ideal of Access/Excel could consume those resources without needing to pull down all of the data. I am curious too, what protocol does Access use when it talks to sharepoint? Is it odbc or something more modern?
Comments: (loading) Collapse