SharePoint 2010 Lists’ OData Content Created by Access Services is Incompatible with ADO.NET Data Services

Updated 3/21/2011 with Pablo Castro’s reply of 3/21/2011 to my thread about the problem in the OData Mailing List (see end of post).

Updated 3/20/2011 with added introductory background material and links to related information.

Introduction

Microsoft SharePoint has gained a substantial share of the market for content management and collaboration tools. Sales of SharePoint 2007 exceeded US$1 billion and Mary Jo Foley reported in her What Is Microsoft's Next Billion-Dollar Business? article of 9/1/2010 for Redmond Magazine that “SharePoint is well on its way to becoming Microsoft's first new $2 billion business.”

SharePoint 2010 is likely to become a data source for a wider range of Microsoft .NET, Windows Phone 7 and other OS client applications because it it exposes list collections in OData format. My Access Web Databases on AccessHosting.com: What is OData and Why Should I Care? post of 3/16/2011 explains the significance of OData-formatted information and how to manipulate it with PowerPivot for Excel.

Microsoft Access 2010 has the capability to easily and quickly publish conventional *.accdb databases to Web Databases, which run under SharePoint 2010’s Access Services. Access Services stores relational tables in SharePoint lists, displays forms in SharePoint Web pages, and prints reports with the assistance of SQL Server Reporting Services.

Visual Studio LightSwitch Beta 2 is a rapid application delivery (RAD) framework for SilverLight Windows and Web database clients that targets user and targets developer populations similar to those of Access. LightSwitch accepts OData, SQL Server, and SQL Azure as a data sources. PowerPivot for Excel runs on OData from SharePoint, and OData’s the only data format offered by Microsoft’s Windows Azure Marketplace DataMarket.

Windows Phone 7 has an OData software development kit (SDK), Android phones can process OData with the Java SDK, and there’s an OData SDK for iPhone’s Object C language. (See J. C. Cimetiere’s OData interoperability with .NET, Java, PHP, iPhone and more post of 3/16/2011 to the Interoperability @ Microsoft blog.) OData is likely to become a leading format in the rapidly growing mobile data market.

Problem Discovery

While running tests with SharePoint 2010’s OData producer as a data source for Visual Studio 2010 and LightSwitch Beta 2 client applications, I encountered the following error when attempting create a Visual Studio LightSwitch Beta 2 Service Reference from an Access application published to SharePoint 2010:

1. I started a LightSwitch project in Visual Studio and selected SharePoint as the Data Source Type:

2. I clicked Next to Enter Connection Information for a SharePoint site created from an Access 2010 project:

The NorthwindTraders site was created by running the Northwind Traders Web Database template from Office Online to create an Access *.accdb database and then publishing it to Access Services in a hosted SharePoint 2010 instance at AccessHosting.com.

3. When I clicked Next, I received the following error message:

The problem relates to the OData client rejection of two leading underscores in the Name property of a column in all lists generated by Access tables. However, the Microsoft SQL Server Data Portability specification for Simple Identifier provides the following specification:

2.2.4 SimpleIdentifier

The SimpleIdentifier attribute specifies an identifier that conforms to the rules for the String data type as specified in [XMLSCHEMA2].

The following is the XML schema definition of the SimpleIdentifier attribute.

<xs:simpleType name="TSimpleIdentifier">
  <xs:restriction base="xs:string"/>
</xs:simpleType>

The XML Schema Character Range for Char is: 

Char
   ::=  
#x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
/* any Unicode character, excluding the surrogate blocks, FFFE, and FFFF. */

The preceding specifications don’t appear to me to specify a pattern that precludes two leading underscores.

You can’t edit the column name of a list linked to Access in the SharePoint UI or with SharePoint Designer 2010. The field is hidden by default in Access Datasheet view. You can unhide the field, but Datasheet views of tables linked to SharePoint lists don’t have a Design mode:

To change the __OldID column name to OldID, you must disconnect the Access front-end from the Access Services version to enable modifying the list designs in the UI, edit 15 lists for this example, and then reconnect Office Access to Access Services. This is, to be generous, a daunting task.

After you perform the preceding tasks for the NorthwindTraders example, the Choose Your SharePoint Items dialog appears as expected:

Following is the corrected Product entity displayed in VS LightSwitch Beta 2:

So far, I haven’t found any side effects from removing the two underscores, other than the OldID column now appears by default in Access Datasheet view.

Tests with a simple VS 2010 C# Console application exhibit the same problem.

It’s interesting that PowerPivot for Excel doesn’t exhibit a similar problem. See the “Browsing OData-formatted Web Database Content in PowerPivot for Excel” section of my Access Web Databases on AccessHosting.com: What is OData and Why Should I Care? post.

Conclusion

The pattern applied to the TSimpleIdentifier Entity Data Model data type that precludes leading underscores should be removed in a later EDM v4.1 CTP because the rule is arbitrary. Changes to Access Services to eliminate the leading underscore and change the method for making columns with such names hidden by default in Access would need to wait for Office Service Pack 2.

Update 3/22/2011 Pablo Castro’s reply of 3/21/2011 to my thread about the problem the OData Mailing List:

Date: Mon, 21 Mar 2011 05:54:35 +0000

From: Pablo Castro <Pablo.Castro@MICROSOFT.COM>

Subject: Re: ADO.NET Data Services Client Considers Field Names with Leading Underscores Invalid

I pinged the SharePoint folks to see if this is a known issue and whether there are any workarounds. The short story is that there is a mismatch of rules between SharePoint names and EDM names, so the adapter between the two needs to deal with these things. If I hear from them I'll get back to this thread with whatever I learn.

-pablo

Pablo is a Microsoft Software Architect and OData’s initial Program Manager and Technical Lead. If Pablo replies, I’ll post an update.

Full Disclosure: I’m a contributing editor for Visual Studio Magazine, a sister publication to Redmond Magazine, both published by 1105 Media.

Technorati Tags: Microsoft Access 2010,Access 2010,Access Web Databases,Web Databases,Microsoft SharePoint 2010,SharePoint 2010,SharePoint,Access Services,Entity Data Mode 4.1tity Data Mode 4.1,EDM 4.1,Visual Studio LightSwitch Beta 2,VS LightSwitch Beta 2,LightSwitch Beta 2,LightSwitch