This post was written by Lois Wang, a Program Manager on the Access team.
Access 2013 web apps are great places to centralize your data. Whether you’re tracking people, events, products or something else, storing data in an Access app allows you to easily collaborate with others while keeping things organized.
Sometimes, though, the stuff you care about is already stored somewhere else. Although you could import the data into Access, those external sources may be maintained by other people or processes. In these cases, you want to make sure that as these sources are updated, you’re always seeing the latest version in Access. Wouldn’t it be great if you could simply link to these data?
Access 2013 makes this easy. The web apps you create with Access 2013 can connect to and display real-time data from SharePoint lists. That way, you can easily supplement or combine external data sources with the things that your app uniquely tracks.
Imagine a scenario where a small business owner named Ryan is trying to manage a party planning company. He and his five employees use Office 365 for sharing information. His accountant manages all the suppliers of his business in a SharePoint list. Ryan has built an Access 2013 web app to manage all the parties that he is in charge of planning. He wants to pull supplier information into his app, but he doesn’t want to have to worry about manually keeping his app in sync with the accountants list. How can he do that?
He opens up his Access 2013 web app in the Access designer and clicks the Create Table button in the ribbon. Then, under the heading “Create a table from an existing data source,” he chooses “SharePoint List.”
The next step is to provide the URL of the SharePoint site where the PartySuppliers list lives. Since he wants to link to rather than import his data, Ryan selects “Link to the data source by creating a linked table.”
Access will go fetch the names of the lists on that site, and Ryan selects the one he wants—the PartySuppliers list. In order for Ryan set up this link, his account’s permission level needs to be “Full Control” for the PartySuppliers list in SharePoint.
Now, the supplier data show up in Ryan’s project management app. Access automatically creates a List view and a Datasheet view for displaying the suppliers. It looks and feels just like the rest of his Access web app.
Ryan can further integrate this SharePoint list into his app by adding a lookup field in his Events table to show which PartySupplier is working on which Event. Even though the PartySuppliers “table” is actually stored externally, setting up this relationship works exactly as it would if the data were stored in a local table.
Two things are worth noting about this external data feature in Access 2013 web apps. First, Access currently only supports read-only connections to SharePoint lists. In our example, that would mean that in order to change information about a supplier, Ryan would have to edit the SharePoint list directly.
Second, in order to set up a connection to an external List, your user account has to be allowed to change permissions to the List. This is because when you set up the connection, you need to give the Access web app itself the right to read the data. The right to grant other accounts or apps access to a List is usually included with the “Full Control” or “Owner” SharePoint permission groups. If you have trouble, check with the person who is in charge of your SharePoint site.
Access 2013 web apps can easily integrate with external SharePoint lists. You can try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.