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
Building an Access app
SharePoint Server 2013 provides the following two methods of building end-to-end web-based business solutions. Both require the purchase of SharePoint Server Enterprise Client Access Licenses (CALs).
Access web database These cannot be created using Access 2013. You can still view and edit a previously created web database by using Access 2010 and SharePoint Server 2010, and you can republish it to SharePoint Server 2013 using Access 2010. Web databases are exposed as a SharePoint site. Data held in Access tables is moved to SharePoint lists, and forms and reports are created as webpages. You cannot automatically convert a web database to an Access web app; however, you can manually convert a web database to an Access web app by importing the data from the web database into a new Access app, and then re-create the user interface and business logic.
Access apps These apps are SharePoint apps, which can be deployed to the SharePoint Store. You use the browser to view and edit data, and you use Access 2013 to design the Access app. Data and Access objects for each Access app is saved in its own Microsoft SQL Server 2012 database; it is not saved in SharePoint lists. This SQL-integrated approach improves the performance, manageability, and scalability of the business solution. Also, this makes it possible for SQL Server developers to extend the solution by directly connecting to the tables in the database, including building reports with Desktop Access Reports, Excel, and Power View.
However, as the data is not stored in SharePoint, some functionality is lost when compared to creating a SharePoint integrated forms solution by using Microsoft InfoPath 2013 Designer. For example, you cannot create or initiate a SharePoint workflow on data in Access apps, nor can you have unique permissions at the table or row level. Also, the data stored inside the Access app is not indexed by the SharePoint search engine. The rest of this section describes the creation of Access apps.
Access 2013 includes a set of templates that can be used to jump-start the creation of Access apps. Any template with a global icon and that does not contain the word desktop can be used to create an Access app. These templates include Custom web app, Asset tracking, Contacts, Issue tracking, Project management, and Task management.
The Asset tracking, Contacts, Issue tracking, Project management, and Task management templates are not available when you first install Access. They are available when you are connected to the Internet. When you choose one of these templates, you are provided a preview of the home webpage, a description, and the download size of the template.
)
Your Access app can contain tables, views (known as forms), queries, and data macros. When you create an Access app based on the Custom Web App template, it contains no tables, views, queries, or data macros; and when the Access app appears in the browser, the webpage displays a link to open the app within Access so that you can start adding tables.
)
The Access app templates available for download over the Internet are summarized in Table 12-1.
Table 12-1. Access app templates
Template name |
Description |
Tables |
Views |
Asset Tracking |
Track equipment used by your business and assigned to employees. |
Assets, Categories, Employees |
List, Datasheet, By Category, By Group |
Contacts |
Manage individual and corporate relationships. |
Contacts |
List, Datasheet, By Group |
Issue Tracking |
Record issues related to your business, associate them with customers, and assign them to employees |
Issues, Customers, Employees, Issue Comments, Related Issues |
List, Datasheet, By Status, By Group |
Project Management |
Manage projects by breaking work items into tasks, associating them with customer, and assigning them to employees. |
Projects, Employees, Customers, Tasks | List, Datasheet, By Status, By Group |
The name of the SQL Server 2012 database that is created when you add an Access app to your site can be found when you open the Access app in Access and click the File tab to display the backstage Info page. The database name will be of the format db_guid, where guid is an automatically generated number. The name of the site where the Access app was added appears in the title of the Access window.
)
The tables, views, queries, and macros are all stored in the database. Whenever you enter data or modify the design of the Access app, you are interacting with the database; however, the user interface gives no indication of this.
Although the server name and database name is of little interest to you, it is important to advanced users who wish to directly connect to the database. You can control external connections to your Access app database by using the Manage split button at the bottom of the backstage Info page. The default configuration of the Access app database is not to allow any external connections. Another group of interested users is your IT department, who may wish to schedule operational procedures, such as backup and maintenance on the database.
Whichever template you use, when an Access app is created, it inherits the permissions and branding from the site where the app was added. You cannot change the permissions or branding within the Access app. Users who design the Access app using Access must be mapped to the Full Control permission level at the site where the app was added. Users who use the browser to run the Access app must be mapped to either the Read or Contribute permission level, depending whether you wish them to just view the data or want them to create, update, and delete the data.
In this exercise, you will create an app from a template.
In the middle pane, click Custom web app.
In the Custom web app dialog, in the App Name text box, type EquipmentTracking, and then in the Web Location text box, type the URL of the SharePoint site where you want to add the Access app, such as http://wideworldimporters.
Click Create to create the app and to display the app in Access.
)
)