You can synchronize data between a SharePoint 2010 list and Office Access 2010, and keep both sets of data up-to-date. For example you may want to maintain sales campaign tracking information in a SharePoint list so it’s easy to collaborate with others on your team, but also do periodic reporting, querying, and bulk changes by using Access 2010.
In this article
Introduction to synchronizing a SharePoint list with Access
You can synchronize data between a SharePoint 2010 list and Access 2010, and keep both sets of data up-to-date. For example, you may want to maintain sales campaign tracking information in a SharePoint list so it’s easy to collaborate with others on your team, but also do periodic reporting, querying, and bulk changes by using Access 2010.
There are three ways to synchronize data between a SharePoint list and Access 2010. Whichever way you choose, this synchronization is two-way, or bi-directional. Changes made in Access to the list data are uploaded to the SharePoint server, and changes made on the server to the SharePoint list are downloaded to Access on your computer.
To synchronize a SharePoint list, you must have Access 2010 installed on your client computer and you must have Contribute permission to the list.
Find more information about using SharePoint 2010 with Access 2010 in the See Also section and in the Access 2010 Help system.
Note: You cannot synchronize a SharePoint external list with Access 2010.
Synchronizing list data by using the Open with Access command
You can synchronize the data in a SharePoint list with Access 2010 by using the Open with Access command in the list ribbon. This command creates an Access table linked to the SharePoint list, and a supplementary UserInfo table that contains additional information, such as user names, accounts, and e-mail addresses.
Each time you open the SharePoint list or Access linked table, you see the latest data. As the SharePoint list continues to be updated, you can also manually refresh the Access linked table with the latest list changes. As the Access linked table continues to be updated, you can also manually refresh the SharePoint list data with the latest Access changes.
Synchronizing list data by using the Track this List command in Datasheet view
You can also synchronize a SharePoint list from Datasheet view by using the Track this List command available in the Datasheet view Task Pane, which also creates a linked table in Access. Using additional commands in the Task Pane, you can also synchronize the list data and do the following:
-
Report with Access.
-
Export to Access.
For more information, see the Datasheet view Help system (At the bottom of Datasheet view, click the Help link.)
Synchronizing list data by using the SharePoint List command from Access
From Access 2010, you can also link a table to an existing SharePoint List by using the SharePoint List command in the Import & Link group on the External Data tab.
You can also create an empty SharePoint list from Access. In the ribbon, on the Create Tab, in the Tables group, click SharePoint lists, and then click one of the following: Contacts, Tasks, Issues, Events, or Custom.
Find more information about using Access in the Access 2010 Help system.
Advantages of linking an Access table to a SharePoint list
Whichever way you choose to synchronize a SharePoint list, Access 2010 creates a linked table that reflects the structure and contents of the SharePoint list. Access selects the right data type for each field that corresponds to the list column. These linked tables then act like any Access table, and so you can also create queries, forms, reports, macros, or code to work with the data in Access 2010.
Lookup data
Access also automatically creates linked tables for all lookup lists (unless the lookup lists are already linked to the database). If the lookup lists contain columns that look up other lists, those lists are also included in the linking operation, so that the lookup list of every linked table has a corresponding linked table in the database. Access also creates relationships between these linked tables.
Structural changes
As is the case with other types of linked tables, you cannot add, delete, or modify the fields in a linked table while working in Access. If you want to make structural changes, such as removing or changing a column, you must open the list on the SharePoint site. From Access you can get to the SharePoint List settings page to change the list structure. Right-click the Access table in the Navigation Pane, point to More Options, and then click Modify Columns and Settings.
Structural changes made to a SharePoint list are not automatically reflected in a linked table. To update a linked table by applying the latest list structure, right-click the Access table in the Navigation Pane, point to More Options, and then click Refresh List.
Performance
Because the data from linked SharePoint lists is automatically synchronized with local tables and users always work against local data, the performance of a linked Access table has been improved, compared to versions prior to Access 2010.
Bulk edits
Synchronizing a SharePoint list with Access 2010 makes it much easier to do bulk changes to your SharePoint list data. To bulk add, update, or delete data from a SharePoint list, run an append, update, or delete query in Access 2010, and then open or refresh the SharePoint list.
Taking data offline and resolving conflicts
If you need to take some work home with you or on the road, you can take your linked SharePoint lists offline by using Access 2010. For example, you may want to provide a parts catalog to a client while you are on the road. You can work on your data in Access 2010 and then synchronize your changes when you reconnect with the SharePoint site at a later time.
If conflicts occur — for example, if someone else updates the same record on the server or while that person also is working offline — you can resolve the conflict when you come back online by using the Resolve Conflicts dialog box. This dialog box displays information about the conflict, such as why the error occurred, and provides options for you to try to submit the data again or to discard your changes. If there are multiple errors, you can view the details of each error by clicking the Previous and Next buttons in the dialog box. Some errors can be resolved only by discarding your changes.
Considerations when synchronizing data
The following table explains some considerations to keep in mind when synchronizing data between a SharePoint list and Access linked table.
|
Feature |
Comparison |
|
Columns |
Access does not support more than 256 fields in a table, so the linked table includes only the first 256 columns. |
|
Folders |
Each folder in the SharePoint list appears as a record in the Access table. Items inside a folder also appear as records, immediately below the record corresponding to that folder. |
|
Lookup columns |
If a column looks up values in another list and the related list isn't already in the database, Access automatically creates linked tables for the related lists. Note: A column of type Person or Group is a special type of lookup column that looks up values in the User Information list. If you link to a list containing a Person or Group column, Access automatically creates a linked table for the User Information list. |
|
Calculated columns |
The results in a calculated column are displayed in the corresponding field, but you are not able to view or modify the formula in Access. |
|
Attachments |
The attachment column of the list is displayed as a field named Attachments. |
|
Read-only columns |
The columns that are read-only in a SharePoint list will continue to be read-only in Access. In addition, you might not be able to add, delete, or modify columns in Access. |
|
Multivalued columns |
A column of type Choice or Lookup can contain multiple values. For such columns, the linking operation creates fields that support multiple values. Multivalued lookup columns are created in the linked table if the column is of type Lookup. |
|
Unsupported lists |
The following lists are unsupported:
|
Synchronize a SharePoint list with Access
-
Navigate to the SharePoint site that contains the list you want to synchronize with a spreadsheet program.
-
Click the name of the list on the Quick Launch, or click Site Actions, click View All Site Content, and then in the appropriate list section, click the name of the list.
Note: A SharePoint site can be significantly modified in appearance and navigation. If you cannot locate an option, such as a command, button, or link, contact your administrator.
-
In the ribbon, click the List tab, and then in the Connect & Export group, click Open with Access.
-
Specify a location for the new or existing database, or click Browse to locate a database.
-
Select Link to data on the SharePoint site, and then click OK.
Note: You can also click Export a copy of the data. Exporting data, unlike linking data, is a one-time copy operation of the current data and does not create a synchronization between the Access table that is created and the SharePoint list.
-
If you are prompted to confirm the operation, click OK.
-
In the File Download dialog box, click Open.
-
If you are prompted whether to enable data connections on your computer, click Enable if you believe the connection to the data on the SharePoint site is safe to enable.
Access 2010 creates a linked Access table to the SharePoint list, a UserInfo table, and additional linked tables if the SharePoint list contains Lookup columns.
Refresh synchronized data
Each time you open the synchronized SharePoint list or Access linked table, you see the latest data. To ensure seeing the latest data while the SharePoint list or Access linked table is open, you can manually refresh the data.
To refresh the synchronized data, do one of the following:
-
From Access 2010, in the ribbon of the linked Access table, on the Home tab, in the Records group, click Refresh, and then click Refresh.
-
From the SharePoint list do one of the following:
-
In the Datasheet view, in the ribbon, click the List tab, and then in the Datasheet group, click Refresh Data.
-
in Standard view, on the Internet Explorer address bar, click Refresh or press F5.
-
