Microsoft KB Archive/926524

Knowledge Base

The data of the primary key column is not in the Data View of a list in Windows SharePoint Services 3.0 when you export a table in Access 2007

Article ID: 926524

Article Last Modified on 11/16/2007

---

APPLIES TO

• Microsoft Office Access 2007
• Microsoft Office SharePoint Designer 2007
• Microsoft Windows SharePoint Services 3.0

---

SYMPTOMS

You export a table from Microsoft Office Access 2007 to a list in Microsoft Windows SharePoint Services 3.0. When you do this, the original data of the primary key column is not in the Data View of the list.

This issue occurs when the data type of the primary key column of the Access 2007 table is one of the following data types:

• Number
• AutoNumber

CAUSE

This issue occurs because Access 2007 exports the data of the primary key column to a new hidden _ID column. By default, the _ID column is not added to the Data View of the SharePoint list.

RESOLUTION

To resolve this issue, add the hidden _ID column to the Data View of the SharePoint list. To do this, follow these steps:

1. Connect to the Windows SharePoint Services 3.0 Web site that contains the exported table.
2. In the View All Site Content pane, click Lists.
3. Click the SharePoint list that contains the hidden _ID column.
4. Click Settings, and then click List Settings.
5. In the Views section, click All Items.
6. In the Columns section, click to select the Display check box of the _ID column, and then click OK.

MORE INFORMATION

When Access 2007 exports the primary key column data of a table to a SharePoint list, a new primary key column is created. The new column is assigned the name "ID" and the AutoNumber data type. The original primary key column is assigned the name "_ID," and is not assigned the AutoNumber data type.

When the table is exported, each record is assigned a new unique key value in the ID column of the new SharePoint list. The original data of the primary key column is moved to the hidden _ID column. Therefore, the export process can be confusing, because the primary key column names are switched and because the original primary key column data is hidden.

For more information about how to export a table or a query to a SharePoint site, visit the following Microsoft Office Online Web site:

http://office.microsoft.com/en-us/access/HA012303141033.aspx

For more information about how to add, to set, to change, or to remove the primary key, visit the following Microsoft Office Online Web site:

http://office.microsoft.com/en-us/access/HA100140991033.aspx?pid=CH100645681033

For more information about how to modify the columns in a Data View, visit the following Microsoft Office Online Web site:

http://office.microsoft.com/en-us/sharepointdesigner/HA100978911033.aspx?pid=CH100667641033

Additional query words: AC2007 ACC2007 Access2007

Keywords: kbtshoot kbexport kbdatabase kbtable kbprb kbexpertiseinter KB926524

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.