How to create, publish and integrate Access Web Apps with SharePoint Online
The Layer2 Cloud Connector for Microsoft Office 365 and
SharePoint can connect almost any data source including the new Microsoft
Access Web Apps in SharePoint Online. For any questions please use
Web Form or contact [email protected] directly.
You can connect and synchronize data from Microsoft Access Web Apps in SharePoint Online or on-premise with external data sources or native SharePoint lists, if required. The options are shown below step-by-step.
Fig.: Create an Access Web App using
your local Office Access software
You can select between several templates or start from scratch. You have to give
a name and location.
Fig.: Enter your SharePoint Online web site as a storage location.
As a next step create your tables and forms using Microsoft Access. In this sample we are going to host a RSS news feed archive (just as a sample). First we create the appropiate table and fields required, e.g. a link, a title, a description and additional information like the publication date and source.
Fig.: Create the data table RSS in
As a next step we create the forms to display and manage in the app later on in
a web browser.
Fig.: Create a form to display and
manage the data
When you are finished with the data structure and forms simply publish the app.
The web app is created automatically, it can be found at your SharePoint Online
web site under web site settings > apps. Additionally a SQL Azure database is
created automatically to keep the data. Now lets take a look, how to connect the
SQL Azure data table to any external data, e.g for import or permanent
synchronization. You can find out where your data lives directly in the
Microsoft Access Info menu tab.
Fig.: Access database connectivity information for SQL Azure
Now, as we are know where the data is hosted, we can simply connect using the
Layer2 Cloud Connector.
Fig.: Connecting the
newly created Access Web App database using the Layer2 Cloud Connector
To connect to Azure please
- select the SQLClient Data Provider
- enter an appropiate connection string for SQL Azure
- enter a select statement to your data table created previously (any SQL
- enter a primary key required for replication
Please verify all settings and display the data preview to take a look.
Now we have to create the external data source, e.g. a RSS News Feed (just as
Fig.: Data Entity settings for the RSS Feed as data source for the SQL
To connect to any RSS Feed,
- Select the RSS Data Provider (included in Cloud Connector Package).
- Enter a valid connection string for RSS feeds. In most cases the URL to the
feed is enough.
- Enter a primary key (unique column), e.g. link or GUID if provided by the
You can aggregate several feeds. Please use comma as delimter, e.g.
Url=url1,url2,url3, if required.
Please verify all settings and display the preview data. As a next step
please map the external data feed fields (RSS Feed) to the App data table (SQL
Azure) in the mapping section of the Layer2 Cloud Connector. You can simply use
the auto-mapping feature in this case because of simiar names on both sides.
Fig.: Field mapping of both data source (RSS Feed) and destination (SQL Azure).
Please verify the mapping. Now you are ready to run the connection first time -
manually in the Connection Manager (for testing) or in background (later on)
using the Cloud Connector Windows service.
Fig.: Sample Connection settings to
sync the RSS News Feed with SQL Azure used by the Access Web App
While the data entities are in sync now, just take a look at your Access Web App
in SharePoint Online.
Fig.: Access Web App in SharePoint
Online connected to an external RSS Feed by the Layer2 Cloud Connector.
- You can search for titles directly in the Access Web App and select any
title in list to provide the details.
- You can manage large data sets, no 5.000 items list view threshold - because
it's SQL Azure, not a SharePoint list.
- The form layout and field attributes are very basic at this time with Access
Web Apps, e.g. no HTML Field. But you can modify at any time.
- Connecting to RSS was just a sample, you can connect to almost any other
external data source via the Layer2 Cloud Connector, including native SharePoint
- You can add the SQL Azure table or query as a SharePoint External List via
BCS, if required for improved integration.
- You can also sync the SQL Azure table directly with a native SharePoint list
for maximum integration, e.g. alerts, workflows, SharePoint search integration
- Please note that this sample is not about bulk import. Only new source data
is added to the data destination or modified / deleted, if required.
- Data source and data destination are connected only while syncing (just for
seconds). Your SharePoint users do access native SQL Azure tables with high
performance and availability.
- The "no delete" option in the data destination entity opens up the way to
- You can aggregate multiple RSS News Feed sources to one list (e.g. in one
connection string as shown above) or with different connections.
- The Layer Cloud Connector is a no-code solution, but you can write your own
data provider if required. The Layer2 Data Provider for RSS is included with
source code as a good starting point. You can also make use of any 3rd party
provider like ODBC, OLEDB, OData, XML or any database provider, like e.g.
- You will find more
connectivity options here.