Using SharePoint 2013 with Excel and Access
- 8/15/2013
- Importing data from an Excel spreadsheet to a list in SharePoint
- Exporting a SharePoint list to an Excel spreadsheet
- Exporting an Excel table to a SharePoint site
- Building an Access app
- Creating a table in an Access app
- Working with an Access app in the browser
- Exporting data from an Access desktop database to a list
- Importing a list
- Linking to a list
- Moving data from a desktop database to a list
- Working offline
- Key points
Working offline
In the previous section, you moved data from an Access desktop database to a SharePoint site, and you created linked tables pointing to a SharePoint list where the list items can be viewed and updated using Access. In these tables, the data is stored outside Access. However, you might still like to access the data in a disconnected environment. When using Access 2007, Access 2010, or Access 2013, you are able to cache SharePoint list data locally in an offline mode. The data that is held locally is not independent of the data in the SharePoint list. You may synchronize changes back to the SharePoint site any time that you want.
To switch from working online to offline, on the External Data tab, click Work Offline—the first command in the Web Linked Lists group. The command changes to Work Online and the previously inactive Synchronize and Discard Changes commands are enabled. All data is cached within the desktop database, and links to the SharePoint lists are cut temporarily. However, Access behaves much as it did online. The only indication that it is offline is the Access status bar at the bottom of the Access window, which reads OFFLINE WITH SHAREPOINT.
In Access, when you are online with a SharePoint website and you modify data within a row in a table, moving out of the row causes Access to synchronize changes. However, when you are working offline, a dimmed pencil icon in the first column of the row that you have amended indicates that you have made changes to the row and synchronization has not occurred.
In this exercise, you will explore synchronizing data with a table linked to a SharePoint list when working offline. This exercise uses the linked tables that were created during the move operation in the previous exercise. You can use your own tables that are linked to a SharePoint list, if you want.
Under the Supporting Objects group, click the double down arrow, and then right-click Opportunities. Select More Options, and then click Open Default View to open the Opportunities list in browser, so that you can verify changes to the list later in the exercise.
Switch back to Access. On the Access ribbon, click the External Data tab, and in the Web Linked Lists group, click Work Offline.
Under the Supporting Objects group, double-click Opportunities to display the contents of the table in Datasheet view.
Click the cell in the first row under the Title column, and then type Bianca Corner Unit. Click a cell in the second row.
Switch back to the browser. Click Refresh, and then verify that the first row has not been modified.
In the browser, click the cell in the first row under Title, type Woodland Bench, and then click a cell in the second row.
Switch back to Access. On the External Data tab, in the Web Linked Lists group, click Synchronize to force Access to temporarily connect to the SharePoint list to synchronize changes.
In the Resolve Conflicts dialog box, click Discard My Changes.
Under Supporting Objects, double-click Opportunities to open the table in Datasheet view, and verify that the Title column of the first row contains the text Woodland Bench.
In the Datasheet view of the Opportunities table, click the cell in the first row under the Title column and type Bianca Corner Unit. Click a cell in the second row.
On the External Data tab, in the Web Linked Lists group, click Work Online.
Switch back to the browser. Click Refresh, and then verify that the first row contains the text Bianca Corner Unit.
On the ribbon, on the List tab, in the Manage Views group, click Create Column.
In the Column name box, type Advertisement and select the Yes/No option. Click OK.
Switch back to Access. Under Supporting Objects, right-click Opportunities, click More options, and then click Refresh List.
Open the Opportunities table, if necessary, and check that the Advertisement column is visible.
)