Export a table or query to a SharePoint site

Applies to: Access for Microsoft 365, Access 2019, SharePoint Online, Access 2016, Access 2013, Access 2010, Access 2007, SharePoint Server 2019, SharePoint Server 2016, SharePoint Server 2013, SharePoint Server 2010.

Anytime you need to temporarily or permanently relocate some of your Access data to a SharePoint site, you can export it to the site from your Access database. After you export data, Access forms a copy of the specified table or query database object, and stores the copy as a list. It is vital to remember that the exported list will not display changes made to the source table or query after the export operation.

In this article

Common scenarios for exporting data to a SharePoint site

Export a table or query to a SharePoint site

What else should I know about exporting?

How Windows SharePoint Services data types map to Access data types

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 to grips with using Windows SharePoint Services, and you realise that it will be easier for everyone if some of your database tables also exist as SharePoint lists. Whenever you want to share data with your team, working with SharePoint lists can be less tedious than working inside a database. Lists can even be simpler for novice users to browse and edit. In addition, you can optimise of multiple SharePoint features, like 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 a continuous basis, but the data is presently stored in Access. To see and edit the newest 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 create daily or weekly status reports, and you want to post the results to one of your sites at frequent periods.

This topic presents the steps for exporting data to SharePoint as a list. For help about linking to a SharePoint list from Access, see Import from or link data to a SharePoint list.

Export a table or query to a SharePoint site

The most fundamental 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 restart the export operation without having to supply 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

  1. Sift out 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.

  1. Establish the SharePoint site where you want to design the list. A genuine 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
  1. Check 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 produces 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 requested to set a new name for the new list.

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

  1. Inspect the fields in the source table or query. The following table goes into more detail about how certain elements are exported, and if you must take further action in specific cases.
ElementResolution
Fields and recordsAll fields and records in the table or query are exported, including fields hidden in the datasheet. Filter settings are ignored during the export operation.
AttachmentsIf 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 valuesDisplay 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 fieldsThe 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 fieldsOLE Object fields are ignored during the export operation.
  1. If the source Access database is still closed, access it, and then progress to the following set of steps.

Export the data

  1. On the External Data tab, in the Export group, press the More button to drop down a series of options and then pick SharePoint List.
  1. The Export – SharePoint Site export wizard appears.Specify a SharePoint site to export your Access table or query.
  2. In the Specify a SharePoint site box, state the address of the destination site.
  1. In the Specify a name for the new list box, type 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, entitle it with a new name.
  1. Optionally, input a description for the new list in the Description box, and then click the Open the list when finished checkbox.
  1. Press OK to start the export process.
  1. Access formulates a list on the SharePoint site, and then presents the status of the operation on the last page of the wizard. Once the export operation ends, you can shut down he wizard or save your export steps as a specification. Also, during the operation, SharePoint chooses the appropriate data type for each column, based on the corresponding source field. To view 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.

What else should I know about exporting?

  • For information on how to change a specification name, delete specifications, or update the names of source files in specifications, see the article Manage Data Tasks.

How Windows SharePoint Services data types map to Access data types

The following table shows how Access identifies the data type of the columns in the exported list when you export a table or query.

Access data typeWindows SharePoint Services data typeDefault field property settingsNotes
TextSingle line of textColumn Name     Mirrors the Field Name setting in Access.

Description     Mirrors the Description setting in Access.

Required     Mirrors the Required setting in Access.

Maximum number of characters     Mirrors the Field Size setting in Access.

Default Value     Mirrors the Default Value setting in Access if it is not an expression. Otherwise, blank.

Add to Default View     Yes
Memo/Long TextMultiple lines of textColumn Name     Mirrors the Field Name setting in Access.

Description     Mirrors the Description setting in Access.

Required     Mirrors the Required setting in Access.

Number of Lines to Display     5

Add to Default View     Yes
NumberNumberColumn Name     Mirrors the Field Name setting in Access.

Description     Mirrors the Description setting in Access.

Required     Mirrors the Required setting in Access.

Min     Blank

Max     Blank The following aspects below illustrate how the Number of decimal places property is set according to the Decimal Places setting in Access.
Access setting    Windows SharePoint Services setting    AutoAutomatic0-50-56-155
Default Value     Mirrors the Default Value setting in Access if it is not an expression. Otherwise, blank.
Add to Default View     Yes
Show as percentage     Yes if the Format property is set to Percentage.
Date/TimeDate or TimeColumn Name     Mirrors the Field Name setting in Access
Description     Mirrors the Description setting in Access
Required     Mirrors the Required setting in Access
Date and time format     Set to Date Only if the Format property is set to Short Date. Otherwise, set to Date & Time.
Calendar Type     Set to Hijri if the Use Hijri option is checked. Otherwise, set to Gregorian.

The following features illustrate how the Default Value property is set according to the Default Value setting in Access.
Access setting    Windows SharePoint Services setting    
=Date() Today’s Date
Field set to a specific date Field set to a specific date

Add to Default View     Yes
CurrencyCurrencyColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Required     Mirrors the Required setting in Access.
Min     Blank
Max     Blank

The following elements present how the Number of decimal places property is set according to the Decimal Places setting in Access.

Access setting    
Windows SharePoint Services setting    
Auto – Automatic

0-5 – 0-5

6-15 – 5

Default Value     Mirrors the Default Value setting in Access if it is not an expression. Otherwise, blank.
Add to Default View     Yes
Currency Type     Mirrors the Format setting in Access.
AutoNumberNumberColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Required     Mirrors the Required setting in Access.
Min     Blank
Max     Blank
Number of decimal places     Automatic
Add to Default View     Yes
AutoNumber where the Field Size property is set to Replication IDSingle line of textColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Required     Mirrors the Required setting in Access.
Maximum number of characters     38
Default Value     BlankAdd to Default View     Yes
Yes/NoYes/NoColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Default Value     Mirrors the Default Value setting in Access if it is not an expression. Otherwise, blank.
Add to Default View     Yes
OLE ObjectThe field is not exported.
HyperlinkHyperlinkColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Required     Mirrors the Required setting in Access.
Format URL as     Hyperlink
Add to Default View     Yes
AttachmentAttachmentColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Required     Mirrors the Required setting in Access.
Add to Default View     Yes
Multivalued fieldsChoiceColumn Name     Mirrors the Field Name setting in Access.
Description     Mirrors the Description setting in Access.
Required     Mirrors the Required setting in Access.
Add to Default View     Yes

Top of Page

Leave a Reply

%d bloggers like this: