"A Table should have a Primary Key" error message when you try to import an ODBC table into an Access 2010 Web database on a SharePoint 2010 site


Symptoms


Consider the following scenario:
  • You publish a Microsoft Access 2010 Web database to the Access Services on a Microsoft SharePoint 2010 site.
  • You try to import a table from an ODBC database into a table in the Web database.
In this scenario, you receive the following error message:

Web compatibility errors were found during import. The import was not completed. For a list of issues encountered during import, click the following link: Web Compatibility Issues.
When you click Web Compatibility Issues, the Web Compatibility Issues table in the Web database opens. The table contains the following error:

A Table should have a Primary Key and it should be a number with field size 'long' to be compatible with the Web.
However, the ODBC table can be imported into an Access 2010 database on a client computer, even though the ODBC table has a Primary Key field that contains a numeric data type.

Cause


This issue occurs because of a known problem when you try to import an ODBC table into an Access 2010 Web database. When Access 2010 imports an ODBC table, the Primary Key index is not imported. In this situation, only the data in the Primary Key field is imported.

Workaround


To work around this issue, follow these steps:
  1. Start Access 2010.
  2. Create a new database.
  3. Import the ODBC table into this database.
  4. Open the table in the Design view.
  5. Set the ID field in the Data type column to Number
  6. On the General tab of the Field properties column, set Field size to Long Integer.
  7. Set the ID field as the primary key.
  8. Open the Access Web database on the SharePoint 2010 site.
  9. On the External Data tab, click Access.
  10. Use the Import wizard to import the table from the Access client into the Access Web database.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.