You can use Access to work offline with data that is linked to SharePoint lists. This can be helpful, for example, if you need to be away from the office or need to continue working when a server isn't available or you've lost connections to the server.
Overview
Before you work offline with data from a SharePoint site, first you must create links between the Access tables and SharePoint lists. You can then take the lists offline to update or analyze them by using Access. When you reconnect, you can synchronize the data, so that the database and lists are updated. If your database has queries and reports, you can use them to analyze the linked data.
If you update any data while you are offline, you can synchronize your changes when Access connects to the server again. If data conflicts occur — for example, if someone updates a record on the server while a different person updates the same record offline — you can resolve the conflict when you synchronize.
You can link Access tables to lists by using several methods. For example, you can move your database to a SharePoint site, which also links the tables in the database to the lists on the site. Or you can export data from a list in datasheet view on a SharePoint site to an Access table. For example, you can use a report in Access to summarize the data. For more information about linking to SharePoint data from Access, see Import or link to data in a SharePoint list.
Work offline with SharePoint lists in Access
Beginning in Access 2010, working offline with linked SharePoint lists is more of an automatic process maintained by Access. If Access can't connect to the SharePoint site where your linked SharePoint lists reside, perhaps because you've lost Internet connectivity, Access switches into offline mode. In the Status Bar on the lower right side of the Access window you'll see a message saying All SharePoint tables are disconnected. If you click File > Info you'll also see a similar message indicating that you have disconnected tables. When you're in offline mode, you can continue to add, edit, and delete data in a cached local copy of the SharePoint List data.
Access continues to try and reconnect at regular intervals with the linked lists on the SharePoint server. Once Access reestablishes a connection to the SharePoint site, you'll see a message bar below the ribbon asking if you want to synchronize your offline data with the server.
Click Synchronize and Access reconnects to the linked SharePoint lists and then tries to merge any data changes.
Resolving conflicting data changes
A conflict arises when two users make a change to the same portion of a list's data or structure. The user who submits a change first will succeed in committing his or her changes, but the second user will be notified of a conflict.
For example, user A changes the City column of the third row from Dallas to Seattle, and at the same time, you change the cell from Dallas to Houston. User A's changes are submitted to the server first, followed by your changes. The server informs you of a conflict and lets you resolve the conflict.
Note: A conflict will occur even when users edit different cells in the same row.
You won't be able to make any changes to a record that has a conflict until you resolve the conflict.
If Access detects any data conflicts while synchronizing your offline changes with the SharePoint server, you'll see the Resolve Conflicts dialog box.
Do one of the following:
-
To resolve the currently displayed conflict or error by ignoring the changes you made to the record, click Discard My Changes. Your changes will be lost.
-
To resolve all pending conflicts and errors by ignoring all of your changes to the list, click Discard All My Changes.
-
To resolve the currently displayed conflict or error by reapplying your changes, click Retry My Changes. In case of a conflict, if you and the other user change the same column, your changes will overwrite the other user's changes. If you edit different columns, your changes will be merged with the other user's changes.
-
To resolve all pending conflicts and errors by reapplying your changes, click Retry All My Changes.
-
To view the details of the next conflict or error, click Next on the top right corner of the dialog box.
-
To view the details of the previous conflict or error, click Previous on the top right corner of the dialog box.
-
To resolve conflicts and errors at a later time, click Close on the dialog box title bar.
Notes
-
The details grid shows all the columns in your current view. If the columns are not visible, use the horizontal scroll bar to scroll, or drag the right edge of the dialog box to increase the width of the details grid.
To zoom the contents of a column, click the column in the details grid. Your changes and the other user's changes will be displayed in the Field Details dialog box. The dialog box is also useful for viewing rich text formatting changes.
-
The details grid displays Deleted row, instead of a row of data, if either you or the other user deletes a row. If the other user deletes the row, the dialog box does not display the name of the user or the date and time of the deletion. Also, you will not be able to retry your changes.
Setting Caching Web Service and SharePoint tables option
The default setting for new databases in Access 2010 and higher is to cache web service and SharePoint linked tables. If you want to change this behavior and instead use the behavior that exists in Access 2007, click File > Options to open the Access Options dialog box. Click Current Database to see the settings listed under Caching Web Service and SharePoint tables.
-
Use the cache format that is compatible with Microsoft Access 2010 and later Select this option to have Access cache a local copy of linked data. This setting can improve performance while working with linked data. Loading and viewing of linked data will be quicker using this option. Clear this option if you want to use the caching behavior that existed in Access 2007.
-
Clear cache on close Select this option to have Access clear any local cached data from memory when you close your database.
-
Never cache Select this option to not have Access cache a local copy of data while working with your linked SharePoint data sources.
Note: The Clear cache on close and Never cache options are not available if you clear the Use the cache format that is compatible with Microsoft Access 2010 and later setting.
Can I work offline manually in Access 2010 and higher?
In order to have manual control of working offline with linked SharePoint data in Access 2010 and higher, you'll need to clear the Use the cache format that is compatible with Microsoft Access 2010 and later setting for the current database found in the Access Options dialog box. See the previous section on how to find and set that option.
Note: You might be prompted to close and re-open your database after changing the setting.
After you've cleared the Use the cache format that is compatible with Microsoft Access 2010 and later setting, you'll see a Web Linked Lists group of commands appear on the External Data tab.
In this group, you'll see commands such as Work Offline, Synchronize, Relink Lists and others. These are the same commands that were in Access 2007 on the SharePoint Lists group in the External Data tab.
To work offline with your SharePoint list data in this mode, click Work Offline.
To synchronize your offline lists with data from the server, click Synchronize.
To reconnect the linked tables after you work offline, click Work Online.
Using Access 2007?
To perform this procedure, first you must link your Access tables to SharePoint lists.
Note: If you have a database that is already published on a SharePoint site, first you must save a local copy of the database and then take the lists offline.
-
Open the database that is linked to SharePoint lists.
-
On the External Data tab, in the SharePoint Lists group, click Work Offline.
Note: If the Work Offline button is not available, either your tables are not linked to SharePoint lists, or you already took the list data offline.
-
Open the database that is linked to your SharePoint lists.
-
On the External Data tab, in the SharePoint Lists group, click Synchronize.
If a conflict occurs over a change that you made offline and a change that someone made on the server, the Resolve Conflicts dialog box appears.
The 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.
The details grid in the Resolve Errors dialog box displays all the columns in your current view. For the affected row, the details grid shows the change that you made. You cannot edit the values, but you can click each column to see more details.
-
In the Resolve Errors dialog box, do one of the following:
-
If you want to keep the changes that were made on the server to a selected item, click Discard My Changes.
-
If you want to try to submit your changes again to a selected item, click Retry My Changes.
Depending on the situation, you may need to wait until the issue is resolved. For example, if you cannot synchronize your changes because of a network connectivity problem, you should wait until you are connected again.
-
If you want to keep the changes that were made on the server to all items, click Discard All My Changes.
-
If you want to try to submit your changes again to all items, click Retry All My Changes.
Depending on the situation, you may need to wait until the issue is resolved. For example, if you cannot synchronize your changes because of a network connectivity problem, you should wait until you are connected again.
Notes:
-
If the error can be resolved only by discarding your changes, the buttons for retrying your changes may not be available.
-
You can close the dialog box without taking any action, but you will not be able to leave or update the list or make changes to a calculated column until you resolve the error.
-
-
After you reconnect the linked tables, any changes that you make to the data or objects are synchronized.
-
Open the database that is linked to the SharePoint lists.
-
On the External Data tab, in the SharePoint Lists group, click Work Online.
Note: If you want to discard the changes that you made offline, click Discard Changes in the SharePoint Lists group.
