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 the FAQ 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 Microsoft Access 

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 allowed)
• 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 a sample).

 Fig.: Data Entity settings for the RSS Feed as data source for the SQL Azure table.

To connect to any RSS Feed, please:

• 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 feed.

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 lists.
• 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 etc.
• 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 data aggregation.
• 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. Oracle.
• You will find more connectivity options here.

Next steps

​Please download and try the Shareware Edition of the Layer2 Cloud Connector after registration here.

Ready to go next steps?