Transcript May 2014 MNSPUG Contentx

Creating Business
Solutions with Access
Services
RAYMOND MITCHELL & WES PRESTON
Abstract
In this session we’ll look at where Access sits in the
solution landscape, how to get started, and then walk
through demos of creating and publishing a custom
web app without writing code. We’ll also look at
what it means to configure Access Services in your
environment and show examples of integration
opportunities. Bring lots of questions and an open
mind – you’ve never seen Access like this!
Raymond Mitchell
Independent Consultant
Owner of IWSPACE, LLC
Specialize in SharePoint & Information Worker
Technologies
SharePointing since 2001
www.iwkid.com
@iwkid
www.iwspace.com
linkedin.com/in/iwkid
3
Wes Preston
Independent Consultant
Owner of TrecStone
Information Worker, No-code/low-code solutions
for business.
SharePointing since 2003
www.idubbs.com/blog
@idubbs
www.trecstone.com
linkedin.com/in/wpreston
4
Agenda
• Overview
• Getting Started
• Creating Access Web Apps
• Access Functionality
• Reporting & External Connections
• Publishing & Upgrading
• Resources & Questions
Overview
ACCESS SERVICES - 2013
Access 2013
• Still clinging to life..
• Start from a blank Database or from one of
many templates
Access Services 2010
Quick survey – who here used Access Services 2010?
Access Services 2010
• “Web Databases”
• Data was stored in Lists
• Publish your existing databases
• Lots of limitations:
• Not all form control events are supported in Web databases
• Not all column data types are compatible with Web lookups
• Not all field data types are compatible with the Web
• At the end of the day – not so great
• Possible reason to miss them? On-prem
configuration was much easier
Access Services 2013
• Still requires a Service Application on-prem
• Introducing Access Web Apps
• Data is stored in SQL Server
• Every Access Web App is a database in SQL
Access Web Apps
• They’re real SharePoint Apps!
• Uses “familiar” installation process
• Can be distributed just like other apps
• Corporate Catalog
• Deployed to the SharePoint Store
Where does Access Fit?
• Fills *some* of the gaps left by SharePoint Designer and InfoPath changes
• No-Code solutions (advanced capabilities for power users – queries, integration, macros)
• Arguably better than SharePoint at transactions / relational data
• Reporting is definitely better – data is in SQL
• Definitely part of Microsoft’s “to the cloud” vision
• Easiest on Office 365
• Leverages SQL Azure
Limitations
• Form and page layout is closed – development is limited to Apps for Office extensions
• No SharePoint-style Alerts
• No Workflow
• No true granular security within the app
Getting Started
WHAT ’S NEEDED
What’s needed
• SharePoint 2013
• SQL Server 2012 or SQL Azure (depending on deployment platform)
• Access 2013 client for design work
Office 365
• Almost ZERO configuration – it just works
• May have to activate the Access App Site Feature
• Available on ALL levels (not just Enterprise)
• Gets the latest and greatest improvements (Cascading dropdowns!!!)
• May have some data/capacity limitations with SQL Azure
SharePoint 2013 On-Prem
• Only available with Enterprise license
• SharePoint 2013 March Update (15.0.4481.1005)
• may as well go to SP1 (15.0.4569.1000) now that it is stable
• SQL 2012 configured for mixed-mode authentication
• It’s difficult to configure
• Going to be another example of the push to the Cloud…
Platform Differences
Office 365
On-Prem
• SQL Azure
• Local SQL Server 2012
• Latest features first
• Latest features on 2-3 year release cycle
• Setup:
• Setup:
Quick thoughts Configuring On-Prem
• Configure Apps first – that’s enough fun for one day
• Use the official white paper AND Kirk Evans’s blog post for reference
• Insane permissions issues not addressed in the white paper
• Really should have a separate SQL Server or at least Instance
• Can’t use SQL Aliases
• Be prepared for firewall rules and such
• Be prepared – all you get are GUIDs
• When you’re stuck, reboot
Create Access Web Apps
START FROM SCRATCH
Creating Access Web Apps
• When creating from scratch, two starting points:
• Access
• SharePoint
DEMO
• Create an Access Web App from SharePoint
• Show how to create from Access
• Show Table Templates
• Show Navigation
• Tables, Default views
• Launch the App
Create Access Web Apps
START FROM A PUBLISHED APP
Installing Access Apps from the Store
• Existing Microsoft-created Access Web Apps
•
•
•
•
•
•
•
•
Salon Management
Asset Management System
Meal Delivery
Recruiting Helper
Tool Tracker
Student Drop in Management
Training Management
More!
• Only recently added the ability to publish Access Apps to the Store – more will be coming
• Install process is like other Apps – except that Access Services must be configured first
DEMO
• Add an Access App from the SharePoint Store
• Show how to customize an Access App from the store
Access Functionality
BUILDING BLOCKS FOR ACCESS WEB APPS
Tables
• Data is stored in ‘Tables’ (as opposed to Lists)
• Table Templates are available to get started
• There are a LOT of samples available with one or more tables and associated views
• Search for ‘all’ to see the full list
• Add a new blank table – to start from scratch
• Existing data sources
Tables - Data Types
• Shift from SharePoint thinking…
• In Access, data type is separate from data display
Views / Forms
• A little terminology overlap
from SharePoint
Macros
•Types of Macros
•
•
•
•
Event
Data
OnStart
On Deploy Data Macro (ODDM)
Macros
•Types of Macros
•
•
•
•
Event
Data
OnStart
On Deploy Data Macro (ODDM)
Respond to clicks on a view
Macros
•Types of Macros
•
•
•
•
Event
Data
OnStart
On Deploy Data Macro (ODDM)
Respond to data events
Macros
•Types of Macros
•
•
•
•
Event
Data
OnStart
On Deploy Data Macro (ODDM)
Execute when the app opens
Macros
•Types of Macros
•
•
•
•
Event
Data
OnStart
On Deploy Data Macro (ODDM)
Execute during install / upgrade scenarios
SharePoint Integration
• List Lookups
• Site permissions
• Site branding
•App-style Navigation (back to site link)
DEMO
• Walkthrough of the building blocks
Reporting & External
Connections
USE THAT SQL SERVER
Reporting Instance
• Create a client-only database that links to your SQL database
• Separates reporting from application maintenance
Enabling Connections
• Where’s my data?
• Database named with GUIDs
• In Azure *somewhere*
• What’s my username?
• What’s my password?
External Connections
• Excel
• Visio
• Power View
• Crystal Reports
• Custom Code
• External services that can connect to SQL Azure (like www.Zapier.com)
DEMO
• Configuring External Connections
• Connecting with Excel
Publishing & Upgrading
LIFECYCLE OF AN ACCESS WEB APP
Application Package
• Create an .app Package
• Four deployment options:
•
•
•
•
Create an Access App by uploading the .app
Upload to Development Site
Deploy to your Corporate Catalog
Submit to the SharePoint Store
• Pay close attention to the
“Include Data in Package” option
Application Package
• Access Client SP1 Save As Options
• Includes better support for upgrade scenarios
• All Upgrade functionality is new (was releasing
around SPC14 timeframe)
Upgrading
• Table Locking
• On Deploy Data Macro
• Runs when the app is installed or upgraded
• Migrates data or loads sample data
• Access Solution Developer owns the logic (Great Power + Great Responsibility)
• App Locking
• Removes the “Customize in Access”
• If modified, cannot be upgraded
DEMO
• Package and Deploy an Access Web App
Resources
AND STUFF
Resources
Kirk Evans post – deploy Access on Prem
http://blogs.msdn.com/b/kaevans/archive/2013/07/14/access-services-2013-setup-for-an-onpremises-installation.aspx
SPC14 Sessions on Channel9
http://channel9.msdn.com/Events/SharePointConference/2014?sort=sequential&direction=desc&term=&tag=access&tag=accessservices#theSessions
(SPC204) Anyone can build a SharePoint App with Microsoft Access
http://channel9.msdn.com/Events/SharePoint-Conference/2014/SPC204
Book: Microsoft Access 2013 Inside Out – Jeff Conrad
http://www.amazon.com/Microsoft-Access-2013-Inside-Outebook/dp/B00JDMPLLO/ref=tmm_kin_swatch_0?_encoding=UTF8&sr=8-1&qid=1399951293
Resources
Add notifications to your Access 2013 web apps using Zapier
http://blogs.office.com/2013/02/18/streamline-collaboration-across-teams-add-notificationsto-your-access-2013-web-apps/
Visualize your Access 2013 web app data in Excel
http://blogs.office.com/2013/01/22/visualize-your-access-2013-web-app-data-in-excel/
Resources
SPC
◦
◦
◦
◦
◦
◦
SPC204 – Anyone can build a SharePoint App with Microsoft Access
SPC285 – No-code SharePoint 2013 Solutions for power users
SPC301 – Access is back! High-value, ‘no code’, functional & flexible business apps with the new Access
SPC335 – Rich extensions to SharePoint Apps using Microsoft Access
SPC338 – The ‘how to’ guide for selling and managing SharePoint Apps build using Access
SPC348 – Update on InfoPath and SharePoint Forms
Questions?