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
Creating a table in an Access app
With an Access app, you use the browser to add, edit, view, and delete data, and you use Access 2013 to design the Access app. This means that Access 2013 is used to create and customize tables, views, queries, and macros.
Each Access table is created as a SQL Server table, which has the same name that it was given in Access, as are the fields (columns) you create in the Access. Access data types are mapped to SQL Server database data types. For example, text fields map to nvarchar; number fields use decimal, int, or float; and image fields are stored as varbinary (MAX). However, in many instances, you will not need to create tables and add fields to those tables, because Access provides a number of table templates (nouns), which you can use as a basis for your new tables.
When you select a table template, other related tables may be added to the Access app; for example, when you add the Assets table, the Categories table is added, which allows you to group and organize your assets. The Orders template provides an Orders table, as well as tables to track related Customers, Employees, Products, Suppliers, and Categories.
You can also create linked tables; for example, you can connect to and display real-time data from SharePoint lists, including external lists.
For each table, two views are automatically generated: List and Datasheet. Each can be likened to the views on SharePoint internal lists and libraries. These are the pages in the browser that users see when they go to the Access app, and use to interact with the data. There are two other types of views, Summary and Blank views, which you can use to develop custom views. The Summary view allows you to group data rows based on a value in a column or, alternatively, calculate a sum or an average of a column.
The Datasheet view in the browser is similar to the Quick Edit view you use on lists and libraries. If your users find that they extensively use the same filters, sorts, or column hiding, you may consider creating queries for the Access app. Queries can combine related tables, perform calculations and summaries, and automatically apply changes. Queries in the Access app are created as SQL Server views or as a table-valued function (TVF), if the query has parameters.
To automate common tasks, you can create macros. A macro is a miniature program that you create and store in an Access app. Access apps do not support the Visual Basic (VB) programming language. There are two types of macros:
User interface macros, which perform actions, such as navigating to another view, or showing or hiding controls. They can be attached to command buttons or combo boxes.
Data macros, which are created by selecting Data Macros from the Advanced split button in the Create group on the Home tab. These macros are used to implement business rules at the data level, and therefore can be used to create, edit, and delete records.
To create views, macros, and queries, use the Advanced split button in the Create group on the Home tab within Access.
In this exercise, you will create a table in an Access app.
On the Add Tables screen, under Create a new table using our templates, type equipment, and then click the search icon to the right of the text box to search through the list of table templates.
Click Assets to create the Assets, Categories, and Employees tables.
In the left navigation pane, click Assets to display the design view of the home page of the Access app.
)