Updated 3/18/2011 with instructions for correcting time-zone errors starting at Figure 7.
Chapter 23 of my Microsoft Access 2010 In Depth book, “Sharing Web Databases with SharePoint Server 2010,” includes a “Signing Up for and Testing a Trial Access Hosting Account” section. After the book published, AccessHosting.com upgraded their shared hosting plan in January 2011 with the following new features:
- Support for English, Spanish, German, and French language packs on shared hosting plans …
- Support for the OData service entry point into all Access Web Databases (see my What is OData and Why Should I Care? post of 3/12/2011.)
- A new and improved control panel for self-service administration of user account passwords
- Support for permissive handling of all file types including PDF
- Support for SharePoint Designer outside of Access Web Databases
I was interested in giving SharePoint’s Open Data Protocol (OData) Service a try, so I requested and received an upgrade to my Access Hosting test account. To simplify the initial testing of OData representation of data delivered by SharePoint Server’s Access Services, I downloaded the Northwind Traders Web Database template from Office Online to create a local NorthwindWebDatabase.accdb (NWWDB) Access 2010 database.
NWWDB is an extensively revised version of the classic Northwind.mdb, which has been Access’s sample database file since v1.0’s “Cirrus” beta. To simplify browser-based data entry and editing, NWWDB uses Access 2010’s new Navigation (tab) control to choose data entry pages. For example, here’s a screen capture of the Products List page:
SharePoint lists don’t support relationships, so Category is a lookup field that lets you select from a list populated by the Categories table. Similarly the Supplier lookup field is populated by the Suppliers table.
Clicking the File tab’s Info link opens the Information page:
Clicking the Publish to Access Services button runs the Compatibility Checker and opens the Save & Publish page:
Typing the Server URL (http://oakleaf.accesshosters.com for the updated shared hosting servers) generates the full URL for uploading to a specific site (http://oakleaf.accesshoster.com/NorthwindTraders.)
Clicking the Publish to Access Services button connects to your account at AccessHosting’s Website and display the Windows Security dialog:
Typing an administrative password and clicking OK starts the upload process, which displays a progress dialog for each table, form and report:
After a couple of minutes or more, depending on your upload bandwidth, the following success dialog opens:
Click OK to dismiss the dialog, open IE and navigate to the full URL (http://oakleaf.accesshoster.com/NorthwindTraders for this example) to display the default Dashboard form for entering new orders. (If you didn’t mark the Save My Credentials check box in the login dialog above, you’ll need to log in.)
If you local time zone isn’t Eastern, which is SharePoint’s default and the location of Access Hosting (Delaware), you receive a warning at the top of the screen:
Click the More Information link, which leads to a generic Fix a Technical Problem page, close the page, click the Options menu, choose Site Permissions to open the Permission Tools ribbon, open the Site Actions menu, and choose Site Settings to open the Site Settings page and click the Site Administration group‘s Regional Setting link:
In the Regional Settings page, select your Time Zone from the list:
Scroll down and click OK to save your change.
Return to the Dashboard and click the Products tab to open SharePoint’s version of Figure 1:
Pasta is obviously the wrong category for Chai (according to Wikipedia, A beverage made with black teas, steamed milk and sweet spices based loosely on Indian recipes), so open the Category field’s lookup list and select Beverages:
Click the selection button of another record in the leftmost column of the grid to save the Category change.
Display NorthwindTraders Content as OData in IE9
Navigate to http://accountname.accesshoster.com/sitename/_vti_bin/listdata.svc (http://oakleaf.accesshoster.com/NorthwindTraders/_vti_bin/listdata.svc for this example) to display the chosen site’s lists available as OData collections:
Note: The “vti” element of the_vti_bin folder name is an abbreviation for Vermeer Technologies, Inc., the original developers of the FrontPage Web designer for Internet Explorer. Microsoft purchased the FrontPage code in January 1966 and then discontinued the product in 2006 in favor of Microsoft SharePoint Designer and Microsoft Expression Web.
Viewing the contents of collections requires turning off IE9’s Feed Reading view, which is enabled by default. To do this, click the Tools icon, choose Internet Options to open the dialog of the same name, click the Content tab and Feeds and Web Slice Setting button to open the dialog of the same name, and clear the Turn On Feed Reading View check box:
Click OK twice to close the dialogs and save your changes.
Reopen IE and navigate to http://sitename.accesshoster.com/sitename/_vti_bin/listdata.svc/listname (http://oakleaf.accesshoster.com/NorthwindTraders/_vti_bin/listdata.svc/Products for this example) to display the content of collections exposed by SharePoint 2010. To display a member of the collection by its ordinal, add the number as a suffix enclosed in parentheses (http://oakleaf.accesshoster.com/NorthwindTraders/_vti_bin/listdata.svc/Products(1) for this example):
Note: Item names contained in OData query expressions, such as Products in the preceding URL example, are case-sensitive. The Edm type prefix is an abbreviation for Entity Data Model, which describes data types compatible with the ADO.NET Team’s Entity Framework v4.1.
See my What is OData and Why Should I Care? post of 3/12/2011 (updated 3/17/2011) for more information about OData and manipulating OData-encoded lists with Microsoft Power Pivot for Excel 2010.