Linking an Access 2007 Table to a SharePoint List


Data were copied in the previous two sections so that the same data could be stored in both an Access 2007 database and on a list on a SharePoint site. However, no data synchronization between these two data locations occurred. If you do not want to maintain two copies of that data but do need to refer to the data within Access 2007, then Access 2007 provides methods of accessing external data that are physically located outside an Access 2007 database. The easiest way to externally reference a SharePoint list is to use linked tables, which were known as attached tables prior to Microsoft Access 95. A linked table stores only a connection to the SharePoint list. You should use linking rather than importing if the data are maintained by either a user or a separate application on the SharePoint Web site.

In this exercise, you will link a table to a SharePoint list.

USE the image from book ExpImpWideWorldImporters.accdb database located in the Documents\Microsoft Press\SBS_WSSv3\Chapter 13 folder. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise. This exercise uses the exportFurniturePrices SharePoint list that you created in the first exercise of this chapter. You can also use your own list if you wish.

BE SURE TO start Access 2007 before beginning this exercise.

OPEN the image from book ExpImpWideWorldImporters.accdb database.

1. In the ExpImpWideWorldImporters: database window, click the External Data tab.

2. In the Import group, click SharePoint List. The Get External Data – SharePoint Site dialog box appears.

3. In the Specify a SharePoint site area, choose http://wideworldimporters.

Troubleshooting  

If the URL for the Wide World Importers Web site does not appear, type the URL in the textbox.

4. Check that the Link to the data source by creating a linked table option is selected and then click Next.

The Choose the SharePoint lists you want to link to page of the Get External Data – SharePoint site dialog box is displayed.

image from book

5. Select the check box to the right of exportFurniturePrices.

6. Click OK.

The Get External Data – SharePoint site dialog box closes. Access 2007 now has two linked tables: exportFurniturePrices1 and User Information List. The exportFurniturePrices SharePoint list contains columns named Created By and Modified By. These lookup columns point to the User Information List.

7. Right-click the linked exportFurniturePrices1 table, and then select SharePoint List

image from book

A context-sensitive menu is displayed that provides a quick way to manage the SharePoint list from within Access 2007.

8. Under All Tables, in the exportFurniturePrices1 group, double-click exportFurniturePrices1.

Access 2007 opens the linked exportFurniturePrices1 table in Datasheet view.

image from book

9. Click the Datasheet tab.

10. In the SharePoint List group, click Open Default View. image from book

Internet Explorer opens and displays the exportFurniturePrices list in All Items view.

11. Switch back to Access 2007. In the exportFurniturePrices1 Datasheet view, click the cell in the first row under the Furniture Name column, and type Base Unit.

12. Click the cell in the second row under the Furniture Name column.

Important  

By moving to another row, Access 2007 automatically synchronizes changes to the SharePoint list.

13. Switch back to Internet Explorer, click the Refresh button, and then verify that the first row has been modified. image from book

14. Click the New button. The exportFurniturePrices: New Item page is displayed. image from book

15. In the StockNo textbox, type W0033, and then click OK.

The W0033 item is added to the bottom of the exportFurniturePrices list.

16. Switch back to Access 2007. On the Datasheet tab, in the SharePoint List group, click Refresh List. Alternatively, you can press image from book and verify that the W0033 item has been added to the bottom of the table. image from book

CLOSE the image from book ExpImpWideWorldImporters.accdb database and any open browser windows.




Microsoft Windows Sharepoint Services Version 3. 0 Step by Step
Microsoft Windows Sharepoint Services Version 3. 0 Step by Step
ISBN: 735623635
EAN: N/A
Year: 2004
Pages: 201

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net