Export a table or query to a SharePoint site
If members of your department or workgroup use Windows SharePoint Services 2.0 or later to communicate and collaborate with each other, you might encounter scenarios where some of the data in your Access databases must be made available on one or more SharePoint sites.
When you need to temporarily or permanently move some of your Microsoft Office Access 2007 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.
If you are not familiar with Windows SharePoint Services and want to learn more, visit the Windows SharePoint Services Home page. Follow the link in the See Also section of this article.
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 Windows SharePoint Services 3.0 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. If you have Microsoft Office Outlook 2007 installed, you can streamline the export operation to post the query results to your site at regular intervals.
This topic presents the steps for exporting data to Windows SharePoint Services as a list. For help about linking to a SharePoint list from Access, see Import from or link 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 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.
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:// 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:
http://adatum/AnalysisTeam
-
Ensure that you have the necessary permissions to create a list on a 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 imported, 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 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 SharePoint List.
The Export Wizard starts.
-
In the Specify a SharePoint site box, enter the address of the destination site.
-
In the Specify the name of the new SharePoint 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, Windows SharePoint Services 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.
Save the specification
-
On the final page of the wizard, select the Save Export Steps check box.
A set of additional controls appears.
-
In the Save as box, type a name for the export specification.
Optionally, type a description in the Description box.
-
If you want to perform the operation at fixed intervals, such as weekly or monthly, select the Create Outlook Task check box.
This creates an Office Outlook 2007 task that lets you run the specification at a later date.
-
Click Save Export.
Configure the Outlook task
If you selected the Create Outlook Task check box in the previous procedure, Access starts Outlook and displays a new task. Use the following steps to configure the task.
Note: If Outlook is not installed, Access displays an error message. If Outlook is not configured properly, the Outlook Startup Wizard begins. Follow the instructions in the wizard to configure Outlook.
-
In the Export-Name-Task window in Outlook, review and modify the task settings, such as Due date and Reminder.
To make the task recur, click Recurrence. The following figure shows the task scheduler with some typical settings:
For information about scheduling Outlook tasks, see the article Schedule an import or export operation.
-
When you finish with the settings, click Save and Close.
Run a saved task
-
In the Outlook Navigation Pane, click Tasks, and then double-click the task that you want to run.
-
On the Task tab, in the Microsoft Office Access group, click Run Export
. -
Navigate to the SharePoint site, and then open the newly created list.
Ensure that all of the fields and records were successfully exported and that there were no errors.
Windows SharePoint Services 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 next section.
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 | 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