Monday, September 21, 2020

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.

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

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 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

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

  2. 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

  3. 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.

  4. 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.

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

Export the data

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

  2. The Export - SharePoint Site export wizard opens.

    Specify a SharePoint site to export your Access table or query.

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

  4. 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.

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

  6. Click OK to start the export process.

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

What else should I know about exporting?

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 type

Windows SharePoint Services data type

Default field property settings

Notes

Text

Single line of text

Column 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 Text

Multiple lines of text

Column 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

Number

Number

Column 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 table illustrates 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

Show as percentage     Yes if the Format property is set to Percentage.

Date/Time

Date or Time

Column 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 table illustrates 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

Currency

Currency

Column 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 table illustrates 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.

AutoNumber

Number

Column 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 ID

Single line of text

Column 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     Blank

Add to Default View     Yes

Yes/No

Yes/No

Column 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 Object

The field is not exported

Hyperlink

Hyperlink

Column 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

Attachment

Attachment

Column 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 fields

Choice

Column 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

No comments:

Post a Comment