Transcript Learning Through Collaboration and the Wisdom of Crowds

Access Apps
for Business Users
Steve Stewart | [email protected] | @SharePointFly
get-SpSteve.com
Theresa Eller | [email protected] | @SharePointMadam
sharepointmadam.blogspot.com
1
Welcome to SharePoint Saturday Houston
Thank you for being a part of the
5th Annual SharePoint Saturday
for the greater Houston area!
• Please turn off all electronic devices or set them to vibrate
• If you must take a phone call, please do so in the hall so as not
to disturb others
• Special thanks to our Title Sponsor, ProSymmetry
2
Thanks to all our Sponsors!
3
Information
• Speaker presentation slides should be available
from the SPSHOU website within a week or so
• The Houston SharePoint User Group will be
having it’s next meeting Wednesday April 15th.
Please join us at www.h-spug.org
4
Access Apps
for Business Users
Access Services in SharePoint 2013
Steve Stewart | [email protected] | @SharePointFly
blog.get-SpSteve.com
Theresa Eller | [email protected] | @SharePointMadam
sharepointmadam.blogspot.com
Who is Theresa Eller?
• SharePoint/Business Systems Analyst at MD Anderson Cancer Center
(Houston, TX)
• President of Houston SharePoint User Group (HSPUG)
• www.h-spug.org
• Microsoft Office Specialist certified in SharePoint 2013
• Frequent speaker at SharePoint events
• SharePoint Saturday Paris in May!
Who is Steve Stewart?
• Site Administration for SharePoint 2003
• Beta tester for MOSS 2007 at Continental Airlines
• Left United Airlines in 2011 to work as a Consultant
• Currently with Accudata Systems, Inc. working on contract at MD Anderson
• Worked with dozens of companies from SMBs to Fortune 500.
• Architecture, installation, development 2003, 2007, 2010, 2013, O365
blog.get-spsteve.com
@SharePointFly
[email protected]
Our Agenda Today
Requirements
Creating An
Access App &
Data Sources
Edit Default
Forms
Working with
Views
Generate
Reports
Requirements
The only technical part of the presentation
On Premise Requirements
• SQL
• SharePoint 2013 can use SQL Server 2012 or SQL Server 2008 R2 SP1
• SQL Server 2012 for Access Services
• Separate instance from the one assigned to SharePoint 2013
• SharePoint
• Access Services service application running on a SharePoint 2013 app server
• Isolated App domain (see http://msdn.microsoft.com/enus/library/office/apps/fp179923.aspx for a complete walkthrough)
• Office
• Microsoft Access 2013 client
• Full requirements available on TechNet
http://social.technet.microsoft.com/wiki/contents/articles/12514.sharepoint-2013-access-services.aspx
On Premise Configuration
• For detailed instructions for installing an on-premise Access 2013
service application, we recommend:
Kirk Evans’ (@kaevans) blog: Access Services 2013 Setup for an On-Premises
Installation
Creating an Access App
and Data Sources
3 Methods to Create an Access App
1. Use a web app template
2. Download a web app from the Office store
3. Build a custom web app
• Start from Access 2013 client
or
• Start from SharePoint 2013 > Add an app
Data Sources
• Existing data source
• New (blank) table
1.
Use a Web App Template
Use a Web App Template – Add An App (1 of 2)
• In SharePoint 2013, click the wheel at the top right
• Select Add an app
• Locate and click on Access App (2nd page)
• Name the Access app
• Click Create
Use a Web App Template – Add An App (2 of 2)
• Click on the icon for the new Access app
• Click Open this app in Access to start adding tables
Search for a Table Template
Search Term Must Be in English
Use a Web App Template
• Access automatically creates tables
• Click Edit to add, change, delete, or rearrange fields
2.
Download a Web App from the Office Store
Download a Web App from the Office Store
• In SharePoint 2013, click the wheel at the top right
• Select Add an app
• Click SharePoint Store on the left
• Type Access in the Find an app search box
Free Access Apps in the SharePoint Store
Unused Apps Get Archived (Office 365)
3.
Build a Custom Web App
Build a Custom Web App – Access Client
• Open the Access 2013 client on
your computer
• Select Custom Web App
• Name the database
• Enter a location for your
SharePoint site
• Click Create
• If using O365, sign in
Build a Custom Web App – Add An App (1 of 2)
• In SharePoint 2013, click the wheel at the top right
• Select Add an app
• Locate and click on Access App (2nd page)
• Name the Access app
• Click Create
Build a Custom Web App – Add An App (2 of 2)
• Click on the icon for the new Access app
• Click Open this app in Access to start adding tables
Build a Custom Web App – Existing Data Source
(1 of 4)
• In the Access 2013 client, click on the desired existing data source
• e.g., SharePoint List
• Specify a SharePoint site
Build a Custom Web App – Existing Data Source
(2 of 4)
• Specify how and where to store the data in the current database
• Click Next
Build a Custom Web App – Existing Data Source
(3 of 4)
• Choose the SharePoint lists you want to link
• Click OK
Good to Know About “Linked” SharePoint Lists
• It’s worth noting that Access Apps currently only support a read-only
relationship to a SharePoint list. This means, if you want to update an
item, you have to do the updating in the SharePoint list. Items
updated in the list WILL appear in your Access App.
• In order to import a SharePoint list, the account you are using must
have the ability to change permission on the list – This usually means
you need full control (owner) rights.
Build a Custom Web App – Existing Data Source
(4 of 4)
Build a Custom Web App – New Blank Table (1 of 2)
• To create your own table, click add a new blank table within the text
to the right of the search box
Build a Custom Web App – New Blank Table (2 of 2)
• Access creates a blank table
in which you can
• Create fields
• Define data types
• Provide descriptions
• Create as many tables as needed
• Save the table(s)
• Switch to datasheet view to
enter data
Build a Custom Web App – View in SharePoint
(1 of 2)
• To view your Access App in SharePoint, click the Launch App button
on the Home ribbon
Build a Custom Web App – View in SharePoint
(2 of 2)
• List form view displays by default • Can switch to datasheet view
Edit Default Forms
Where We Left Off…
• Click Edit to add, change, delete, or rearrange fields
Add Fields to the Table
• Double-click the table name to open it
• Add the field name, data type, and properties
Add Fields to the Form
• Double-click the
form name to
open it
• Drag the field
from the Field
List onto the
form
Delete Fields from a Form
• Click on the field to be deleted
• Press delete on the keyboard
• Delete label separately
• Does not delete field from table
Rearrange Fields on the Form
• Drag and drop fields
• Highlight both label and field to move them at the same time
Working with Views
Create a Blank View
• Open the Access app in the Access 2013 client
• Click on the table to be associated with the view
• From the Home tab of the ribbon click Advanced
• Select Blank View
• Add fields to the view by dragging them from the
Field List
• To save the view, right-click on the tab labeled
View and click Save
• Type a name for the view
• Click OK
Add a View to the Access App
• From the main database view,
click + to add a view
• Type a View Name
• Change the View Type and/or
Record Source, if necessary
• Click Add New View
View Settings/Actions
• Click on the view
• Click on the star icon that appears
• Click on the desired setting or action
•
•
•
•
•
Open in Browser
Edit
Rename
Duplicate
Delete
Create a List View
• From the Home tab, click
Advanced
• Select List View
• List view can also be used as a
popup or subview
Controls
1.
2.
3.
4.
Text box
Label
Button
Web browser
control
5.
6.
7.
8.
Combo box
Check box
Image
Autocomplete
control
(text box, search,
drill through)
9. Hyperlink control
10. Subview
11. Multiline text box
12. Related items
control
Action Bar Buttons
• Cannot change logic of the five existing buttons
• Can delete unwanted buttons
• Can add custom buttons
Control Properties
1. Data
2. Formatting
3. Actions
Create a Datasheet View
• From the Home tab, click Advanced
• Select Datasheet View
Queries & Reports
Report On My Data
• Click the File tab
• Click Report on my data
• Creates second instance of Access app as a desktop application
• Requires SQL Server Native Client drivers
Send Email Action
Applicable to Access 2013 web apps (Office 365) only
Send Email Action Overview
• Available for Access 2013 web apps in Office 365
• No support yet for on-premises
• Use when working with named data macros or data macros attached
to table events (On Insert, On Update, On Delete)
• Can only send email to people within your Office 365 organization
Named Data Macros
• Send an email about a specific record
Data Macros Attached to Table Events
• Send emails when
• New records are added
• Existing records are edited
• Existing records are deleted
• Double-click SendEmail in the Action Catalog to add it to the macro
logic
Access App Thresholds and Limits
Thresholds and Limits
Limit
Max Value
Limit Type
Notes
Maximum
Access/SharePoint App
Package size
100 Mb
Boundary
100 Mb is the limit for an app package
created in the Access client. The package
is compressed when created and may
contain more than 100 Mb of data.
Maximum Access app
database storage size in
SQL Azure
1 Gb
Boundary
Each Access app created on SharePoint
Online creates a database on SQL Azure.
1 GB is the limit for the database storage
on SQL Azure. In an on-premise
installation, the administrator controls
the size of the associated SQL database
https://technet.microsoft.com/en-us/library/cc262787.aspx
Questions?
Theresa Eller | [email protected] | @SharePointMadam
sharepointmadam.blogspot.com
Steve Stewart | [email protected] | @SharePointFly
blog.get-SpSteve.com
Resources
• http://sharepointmadam.blogspot.com
• Access Apps for SharePoint 2013 (Parts 1, 2, and 3)
• SharePoint Conference 2014
• Anyone can build a SharePoint App with Microsoft Access
• Access is back! High-value, 'no code', functional & flexible business apps with
the new Access services
• The 'how to' guide for selling and managing SharePoint Apps built
using Access
• Access Team Blog
• http://blogs.office.com/access/
Resources
• Access 2013 training courses, videos and tutorials
• Microsoft Virtual Academy
• Database Fundamentals
Please Leave Feedback
Please fill out the session survey
with your feedback. You can
scan the QR Code to launch the
survey or by going to
www.WhatsYourAnswer.com?S2
01545184122