Export a table or query to a SharePoint site

When you need to temporarily or permanently move some of your Access data to a SharePoint site, you can export it to the site from your Access database. When you export data, Access creates a copy of the selected table or query database object, and stores the copy as a list. It is important to remember that the exported list will not reflect changes made to the source table or query after the export operation.

Common scenarios for exporting data to a SharePoint site

You export a table or query to a SharePoint site for a variety of reasons, including:

You are just getting started with using Windows SharePoint Services, and you realize that it will be easier for everyone if some of your database tables also exist as SharePoint lists. When you want to share data with your team, working with SharePoint lists can be easier than working inside a database. Lists can also be easier for novice users to browse and edit. In addition, you can take advantage of several SharePoint features, such as receiving a notification when a list has been modified, assigning and coordinating tasks associated with a list, and working offline.

You want to share data between Access and a SharePoint site on an ongoing basis, but the data is currently stored in Access. To view and edit the latest data, either by using Access or from the SharePoint site, you should first export the data as a list, and then link to it from the Access database.

You use queries in an Access database to generate daily or weekly status reports, and you want to post the results to one of your sites at regular intervals.

Export a table or query to a SharePoint site

The easiest way to export data to a SharePoint site is to run the Export - SharePoint Site Wizard. After you run the wizard, you can save your settings — the information that you provided when you ran the wizard — as an export specification. You can then rerun the export operation without having to provide the input again. The steps in this section explain how to prepare for the export, export your data, and save your settings as a specification.

Prepare the operation

Locate the database containing the table or query that you want to export.

When you export a query, the rows and columns in the query results are exported as list items and columns. You cannot export a form or report to SharePoint.

Note: You can export only one object at a time.

Identify the SharePoint site where you want to create the list.

A valid site address starts with http:// or https:// followed by the name of the server, and ends with the path to the specific site on the server. For example, the following is a valid address:

https://contoso/AnalysisTeam

Ensure that you have the necessary permissions to create a list on the SharePoint site. Contact the server administrator if you are unsure about permissions.

The export operation creates a new list that has the same name as the source object from Access. If the SharePoint site already has a list with that name, you are prompted to specify a different name for the new list.

Note: You can neither overwrite nor append data to an existing list.

Review the fields in the source table or query.

The following table explains how certain elements are exported, and whether you need to take additional action in specific cases.

Element

Resolution

Fields and records

All fields and records in the table or query are exported, including fields hidden in the datasheet. Filter settings are ignored during the export operation.

Attachments

If the source object has more than one attachment column, you must remove all but one attachment column. This is because a SharePoint list can support only one attachment column. If the source object contains more than one such column, Access displays a message prompting you to remove all but one attachment column before starting the operation. To work around this, you can copy any additional attachment columns to other Access objects, and then export them to other SharePoint lists.

Lookup fields that have single or multiple values

Display values in single-valued lookup fields are exported as drop-down menu Choice fields in the SharePoint list. If the source field supports multiple values, a Choice field that allows multiple selections is created in the SharePoint list.

Note: A Choice field in a SharePoint list can consist of no more than a single column. If the source lookup field contains multiple columns, the values in all of the columns will be combined into a single column.

Calculated query fields

The results in calculated columns are copied to a field whose data type depends on the data type of the calculated result. The expression behind the results is not copied.

OLE Object fields

OLE Object fields are ignored during the export operation.

If the source Access database is not already open, open it, and then go to the next set of steps.

Export the data

The location of the Export - SharePoint Site Wizard differs slightly depending upon your version of Access. Choose the steps that match your Access version:

If you're using Access 2016, Access 2013 or Access 2010, on the External Data tab, in the Export group, click the More button to drop down a list of options and then click SharePoint List.

If you're using Access 2007, on the External Data tab, in the Export group, click SharePoint List.

The Export - SharePoint Site export wizard opens.

In the Specify a SharePoint site box, enter the address of the destination site.

In the Specify a name for the new list box, enter a name for the new list.

If the source object in your database already has the same name as a list on the SharePoint site, specify a different name.

Optionally, enter a description for the new list in the Description box, and then select the Open the list when finished check box.

Click OK to start the export process.

Access creates a list on the SharePoint site, and then displays the status of the operation on the last page of the wizard. When the export operation ends, you can close the wizard or save your export steps as a specification.

Also, during the operation, SharePoint selects the right data type for each column, based on the corresponding source field. To see a list of how Access and Windows SharePoint Services data types map to each other when you export data, and which field settings are exported for each data type, see the section How Windows SharePoint Services data types map to Access data types, later in this article.