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