Cannot edit the lookup field of a linked Windows SharePoint Services list when you link the list in an Access 2003 database (826759)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Windows SharePoint Services
This article applies only to a Microsoft Access database (.mdb).

Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you create a linked table in a Microsoft Office Access 2003 database by linking a Windows SharePoint Services list from a Microsoft Windows SharePoint Services site, you may not be able to edit the lookup field in the linked table if the Windows SharePoint Services list contains one or more lookup fields

Note When you try to edit the lookup field of the linked table, you may receive the following message in the status bar:The 'FieldName' field is read-only.

CAUSE

This problem occurs because the Windows SharePoint Services list that the lookup field references is not linked with the Windows SharePoint Services list in the Access 2003 database.

WORKAROUND

To work around the problem, link the Windows SharePoint Services list from the correct Access 2003 database. To do this, follow these steps:
  1. Start Access 2003.
  2. Open the correct Access 2003 database.
  3. In the Database window, point to Get External Data on the File menu, and then click Link Tables.
  4. In the Link dialog box, click Windows SharePoint Services() in the Files of Type list.
  5. In the Site window of the Link to Windows SharePoint Services Wizard, type the URL of the correct Windows SharePoint Services site in the Site box, and then click Next.
  6. In the Select Lists window of the wizard, make sure that you click to select Retrieve IDs for lookup column, and then click the correct Windows SharePoint Services list that has a lookup field or lookup fields.
  7. Click Finish.
  8. Open the linked table in the Datasheet view.

    Notice that when you click Finish, not only is the correct Windows SharePoint Services list with the lookup field linked, but the Windows SharePoint Services list that is referenced by the lookup field is also linked. If you try to modify the lookup field of the linked table, the available list of values for the column appears. You may select one of the values and then modify the lookup field.

STATUS

This behavior is by design.

MORE INFORMATION

The lookup field of a Windows SharePoint Services list is similar to a choice field. However, the available options in the lookup field come from another list and are not a hard-coded selection of values. Therefore, without the lookup Windows SharePoint Services list, Windows SharePoint Services cannot provide the list of values for the lookup field. As a result, you may not be able to modify the contents of the lookup field.

The lookup field of a Windows SharePoint Services list references the ID field of the lookup Windows SharePoint Services list. When you link the Windows SharePoint Services list with a lookup field to a table in an Access 2003 database, only the friendly (human readable) value for the field is transferred in the linked table.

Additionally, when you link the Windows SharePoint Services list with a lookup field from the Windows SharePoint Services site, the Windows SharePoint Services list that is referenced by the lookup field is not linked in the Access 2003 database. Therefore, Windows SharePoint Services marks the lookup field as read-only to maintain the data integrity between the linked table in Access 2003 and the source Windows SharePoint Services list.

Steps to Reproduce the Behavior

  1. Create a Windows SharePoint Services list that is named testlookup1.
  2. Create another Windows SharePoint Services list that is named testlookup2 that contains a lookup field that refers to testlookup1.
  3. Open the Windows SharePoint Services list testlookup2 in the Datasheet view.
  4. Click Taskpane.
  5. In the right pane, click Create linked table in Access.
  6. In the right pane, under the Export section, click to select New Database.
  7. In the File New Database dialog box, click Create.

    Note Notice that as soon as you click Create, the resultant linked table in Access automatically opens. If you try to edit the lookup field in the linked table, you may not be able to edit the lookup field and the message that is mentioned in the "Symptoms" section of this article appears in the status bar of the current window.
Modification Type:MinorLast Reviewed:8/29/2006
Keywords:kbDatabase kbprb KB826759 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.