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.
-
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 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?
In order to have manual control of working offline with linked SharePoint data in Access, 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.
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.
