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
Linking to a list
Data was copied in the previous section so that the same data could be stored in both a list on a SharePoint site and either an Access app or an Access desktop database. If you do not want to maintain two copies of that data, but you do need to refer to the data within the Access app or an Access desktop database, then Access provides methods of accessing external data that are physically located outside an Access database, known as linked tables, which were known as attached tables prior to Access 95.
The easiest way to reference a SharePoint list externally is to use linked tables. You should use linking rather than importing if the data is maintained by either a user or a separate application on the SharePoint website. Also, to set up a connection to a list, the user who creates the linked table must have Change permissions rights to the list.
With a desktop database, the data from the linked tables is cached in local tables when the user is online with SharePoint, thereby improving large list performance. When server connectivity is lost, the database automatically goes into offline mode. When connectivity is restored, Access automatically synchronizes data changes for you. Text in the far right of the status bar at the bottom of the Access window indicates the connectivity status of Access with SharePoint.
With an Access app, the data is not displayed in Access; it is displayed in the browser and data is directly accessed from the tables in the app’s database.
In this exercise, you will link a table to a SharePoint list, enter data in Access, and check that the data appears in the list.
On the Access ribbon, click the External Data tab. In the Import & Link group, click More, and then click SharePoint List. If an Access dialog box opens, stating that all objects must be closed prior to continuing this operation, click Yes to close the objects.
On the Get External Data - SharePoint Site dialog box, in the Specify a SharePoint site area, select the site that contains the list to which you wish to link.
Check that the Link to the data source by creating a linked table option is selected, and then click Next.
On the Choose the SharePoint lists you want to link to page, select the check box to the left of the list to which you wish to link, such as exportFurniturePrices.
Click OK.
Under Tables, right-click the linked exportFurniturePrices1 table, and then select More options.
Click Open Default View to display the exportFurniturePrices list in the browser.
Switch back to Access. Under Tables, double-click exportFurniturePrices1 to open the linked table in Datasheet view, and then click the cell in the first row under the Furniture Name column, and type Base Unit.
Click the cell in the second row under the Furniture Name column.
Switch back to the browser, click Refresh, and then verify that the first row has been modified.
On the ribbon, click the Items tab, and then click New Item in the New group.
In the StockNo text box, type W0033, and then click Save to add the W0033 list item to the list.
Switch back to Access. On the Home tab, in the Records group, click Refresh All.
)