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
Key points
You can create a custom list from the browser by importing data from an Excel spreadsheet.
You can create an Excel spreadsheet from the browser and export data into it from a SharePoint list.
From within Excel, you can export data from an Excel table into a newly created SharePoint list.
You can synchronize changes between a SharePoint list and an Excel spreadsheet. This is a one-way synchronization process.
Integration with SharePoint makes Access 2013 a great collaboration tool, while also permitting data to be stored on enterprise servers for better manageability.
In Access 2013, databases can be published to SharePoint Server 2013 as an Access web app, also known as an Access app, where data is stored in its own SQL Server 2012 database. The browser is used to view and edit data, and Access 2013 is used to design the Access app.
In Access 2010, databases can be published as an Access web database. These can only be created using Access 2010 and Access Services 2010.
Both Access apps and Access web database can only be used when SharePoint Server Enterprise Client Access Licenses (CALs) have been purchased. You can integrate Access desktop databases with any edition of SharePoint 2013.
When using an Access desktop database, you can export data from a table to SharePoint lists. Data in the Access table is not affected by subsequent changes made to the SharePoint list because there is no synchronization process between Access and a SharePoint site.
Using either an Access app or a desktop database, you can use linked tables. This lets you take advantage of SharePoint features such as workflow, security, and searching.
At the time that this book was written, data from a linked table in an Access app is read-only; however, in a desktop database, two-way synchronization is provided and you can maintain a cache to use when working offline.
You can move an Access desktop database to a SharePoint site. Data is moved into SharePoint lists, and if you want, you can save the desktop database in a library.