Exporting Data from an Access 2007 Database to a SharePoint List

Exporting Data from an Access 2007 Database to a SharePoint List

Access 2007 allows you to export a table or other database objects to a number of formats such as an external file, Lotus 1-2-3, Paradox or dBase database, Office Excel 2007 workbook, Microsoft Office Word 2007 Rich Text Format (RTF) file, text file, eXtensible Markup Language (XML) document, Open DataBase Connectivity (ODBC) data source, or HyperText Markup Language (HTML) document. Beginning with Microsoft Office Access 2003, you can also export a table to a SharePoint site where a new list is created.

In the following exercise, you will export a table from within an Access 2007 database into a SharePoint site by creating a new SharePoint list.

USE the  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.

BE SURE TO start Access 2007 before beginning this exercise.

OPEN the  ExpImpWideworldImporters.accdb database.

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

2. In the Export group, click SharePoint List. The Export – SharePoint Site dialog box is displayed.

Troubleshooting  

If the SharePoint List link is not active, under All Tables, click the FurniturePrices: Table.

3. In the Specify 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. In the Specify a name for the new list textbox, type exportFurniturePrices.

5. Leave the Open the list when finished check box selected.

6. Click OK. A dialog box opens displaying the progress of the import.

7. Internet Explorer displays the newly created list, exportFurniturePrices, in All Items view.

Tip 

If you mistype the Web site name in the Site textbox, Access 2007 displays a warning dialog box stating that it can’t find the Web site. If this occurs, verify the Web site address and try again.

8. Return to the Save Export Steps page of the Export – SharePoint Site dialog box in Access 2007.

9. Select the Save export steps check box. The Save As and Description textboxes as well as the Create an Outlook Task area appear.

10. Click the Save Export button. The Export – SharePoint Site dialog box closes.

A Move to SharePoint Site Issues table appears in the Access 2007 navigation pane that details aspects of the data that couldn’t be applied to the SharePoint list.

To export the data to a SharePoint list, Access queries the Access table for data, which it then copies to the SharePoint list. By saving the export query, you can now repeat the above steps without using the wizard. Your saved exports can be found under the External Data tab in the Export group. Similarly, you can save your export as a Microsoft Office Outlook 2007 task, which you can then configure to remind you to run the export query.

CLOSE the  ExpImpWideWorldImporters.accdb database and close the browser. Choose No if prompted to save changes.