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
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
-
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
-
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.
-
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.
What else should I know about exporting?
-
For information on how to save the details of your export into a specification that you can reuse later, see the article Save the details of an import or export operation as a specification.
-
For information on how to run saved export specifications, see the article Run a saved import or export operation.
-
For information on how to schedule specifications to run at specific times, see the article Schedule an import or export operation.
-
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 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.
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.
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.
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 |
No comments:
Post a Comment