Introduction

Greetings Fellow SharePoint users!
In this article I am going to walk through the process of building an Access service app using an example of a Photography sales store. This app will store information of the employees, customers, inventory and orders using tables and forms. We are going to create forms to both add and read data from tables.

In this article I am going to focus on details which an everyday IT Professional will need to know. One of the key features of this article is to make changes to the tables template and customize them (using no-code) to match your needs.

Throughout this article you will see me add a 'Similarity' comment where I will be comparing the Access feature I just described with a SharePoint List.
For this example, I am using the SharePoint Online Plan 2. You can find further information about this plan athttp://office.microsoft.com/en-us/sharepoint/collaboration-tools-compare-sharepoint-plans-FX103789400.aspx

My SharePoint Online Setup

In this section I am going to give you a quick summary of my current SharePoint Online site setup. As I mentioned earlier, I have a SharePoint Online Plan 2 license.

Site Collection Template

I have already created a site collection using the Team site template. Below is a screenshot of my Office 365 SharePoint site collections

Site Collection and site features

Below are the Site collection features which are activated
a. Disposition Approval Workflow
b. Document Sets
c. Library and Folder based retention
d. SharePoint Server Enterprise Site Collection features
e. SharePoint Server Standard Site Collection features
f. Site Policy
g. Three-State workflow
h. Video and Rich media

Below are the Site features which are activated
a. Access App
b. Following Content
c. Getting Started
d. Minimal Download strategy
e. Mobile Browser View
f. SharePoint Server Enterprise Site features
g. SharePoint Server Standard Site features
h. Site Feed
i. Site Notebook
j. Team Collaboration Lists
k. Wiki Page Home Page

Create a Custom Web App in Access

Setting up connections

Open your Access 2013 application and click on Custom Web App. Below is a screenshot.


Give your app a name that makes sense, provide a URL and click create. In my case, the URL was https://christianfamily.sharepoint.com/sites/access2013. DO NOT COPY the entire URL ending with /_layouts/15/start.aspx#/SitePages/Home.aspx and paste it. Below is a screenshot



The publishing process completely depends on the internet speed provided by your ISP during which you will see status messages such as the ones below.








Once completed, you will see your new Access App created and also have an active connection to the app. Below is a screenshot

How to open the access database file i.e. accdb again

I thought it was relevant to mention this piece, especially if you plan on editing this access app regularly. While your connection is still active, do the following-
Click on File



Here you will notice that the Connections are by default set to disabled. We need to enable them, hence click on Manage.



Click on 'From Any Location' and confirm that the 'Enable Read-Write Connection' is enabled. Below is a screenshot.



Once the connection is enabled, below is what you should see



Now you can go back to your Access file, Save it and choose 'Launch App'. After a few seconds, a browser will open and you should get a confirmation that you have successfully created an Access app and now it is time to start designing. Below are two screenshots: -





At this point I want to pause and consider three scenarios. The first scenario is that you did carefully look at the screenshot, the second scenario is that you didn't and now what and the third is if you are using a new machine.

Scenario I

In the screenshot above, did you notice the 'Open this app in Access to start adding tables.'? If you did and clicked on it, then you should get a dialogue box which asks if you want to directly open or save it. I highly recommend that you save it in a location that you can remember. Below is a screenshot of what that dialogue box looks like




I also want to point out the download URL you will get to see at the bottom of your IE browser. Note the URL. The access file is coming directly from SharePoint



If you click on 'Open' then below is a message you will receive.

Scenario II

I like to call this the 'What-if' scenario. What if you didn't click on the 'Open this app…' link and closed the browser and now want to open the Access App on your local Access 2013?

In this case go to your Database Cache folder and open the Access database from there. Your Access Database Cache folder is located at
C:\Users\UserName\AppData\Local\Microsoft\Access\DatabaseCache\15.0. Below is a screenshot of my current Access Database Cache folder and there you will see the new 'Photography' Access database. Note, that while you have a live connection to your Web Access database, you will have another Access temp file created as well. Also, these cached databases will remain on your local machine even if the app is deleted from SharePoint.

Scenario III

What if you do not have your desktop/laptop device and want to make a change to the Access App? You will need to have Access 2013 on your temporary machine you are accessing. Then go to your Access App on your SharePoint site, click on the Settings icon on the top right of the page, choose the option 'Customize in Access'. The entire accdw file will be downloaded and stored on the same DatabaseCache folder whose location I have provided above. Below are the screenshots

How to access the database and tables in Windows Azure

I won't be covering this section in detail, but I would like to point out a few things. As the below screenshot shows, once the Access app is setup you will be provided the SQL Azure server and database name. Below is the screenshot.




If you attempt to access the database using SQL Server Management Studio, you will be given the below error. This is by design.





The Microsoft Windows Azure SQL Database service prevents access to your SQL Database server with the SQL Database firewall. You can use the Windows Azure Platform Management Portal or master database to review and edit your server-level firewall configuration. You can follow the steps provided in the article.
http://msdn.microsoft.com/en-us/library/windowsazure/ee621783.aspx%20

Below is a video which walks you steps I displayed above. As an FYI, to record this video and all the other videos henceforth, I am building a separate app called 'Photography2' which has the same setup as the 'Photography' app.



(To view a higher resolution video click here)

Creating and Editing tables in Access

In this section we are going to create new tables using existing templates and then edit them.
As stated earlier, we are going to build the following four tables.
1. Employees
2. Products
3. Customers &
4. Orders

Open your new app and draw your attention to the 'Add Tables' section. Microsoft has already provided us four suggestions, Persons, Order, Service and Sell. If you browse through these you will notice they do have some common table templates. Templates such as Customers will be found in all of them. In case none of them work for you then you do have the option to create your own table.

For the sake of our requirement, we are going to use the Order suggestion and create the following four tables. Below is a screenshot of the Table templates.



Similarity: SharePoint also gives suggested List templates as well. If you recall, there are already built List templates in SharePoint, templates such as Contacts, Calendar, Discussion board, Promoted Links, Tasks etc. You can also create your own custom list. Below is a screenshot which shows you a few of these List templates.




As I create a table, Access will automatically build the necessary forms to view the contents of the tables. I am going to add one table at a time and show you screenshots of both the table and their respective forms.

Employees table:

You will see that the Employees table is created. If you click on the Navigation pane, then you will see the respective new forms as well. Below are screenshots of both the new Employee table and the new forms.




By design in the Access app for every table you create you will have a datasheet form and a list form created. The datasheet form in access is very similar to the spreadsheet view available for a SharePoint List. The list form in access is very similar to the default view of a SharePoint list. So what is the 'By Group' form? I'm glad you asked :-)

This new form which is built out-of-the-box by Access is a great feature which is very impressive and extremely useful. Below is the screenshot of the form.



The new 'By Group' form for the Employees table has two controls. One is called Group simply because this control shows the group of employees and counts the total number. The second control on the right will give four descriptions of the employee. The four descriptions include Display name (includes first and last name), company, job title and work phone. Below are screenshots of these two controls.




I am going to save and publish the new table and their views. Then add some employee data to see how the views work. Below are screenshots of what the Employees table and the three forms look like on SharePoint. Currently there aren't any employees added to the table and hence the forms are empty.
Employees List form



Employees Datasheet form



Similarity: For a SharePoint List you also have the option to create several views very similar to what we see in Access. Access List view is very similar to the SharePoint List's default view. The Access Datasheet view is very similar to the SharePoint Datasheet view.

Employees By Group form



I am now going to add some employees to the table directly on SharePoint after which below is what the forms look like.

Employees List form



Employees Datasheet form



Employees By Group form - Now the data filters we saw earlier will make sense! The Employees table had a field named Group. Group is a lookup data type which has three options, Contract, Full-time and Part-time. The employees are grouped by their employment category i.e. if they are contract, full-time or part-time.







Below is the setup to create the three groups we just saw above. The left control is using the Group field and calculating the sum of the employees in that group.



Below is the setup which dynamically shows us the list of employees when their groups are selected, i.e. if you selected Contract group then this control will automatically populate all the employees who are on Contact.



For this first table I have created a video which walks you through the steps I have described above.



(To view a higher resolution video click here)

Products Table

We are now going to build our second table i.e. the Product table. This table will be used to store information of all the products the Photography Inc. is selling. The steps to setup this table is exactly the same except choose the Products template as shown below.



The Products template will create three separate tables, i.e. this template will create a products table, categories table and suppliers table. Forms for each of the tables will also be built. Below is a screenshot.



I really don't need the categories and suppliers table for this app so I'm going to delete them. However, when I first attempt to delete the Suppliers table, below is the message I got.



There is more to this message than that which meets the eye. What this tells me is that Access does a lot of behind the scene job to create fields and looks out-of-the-box to make things a lot easier for the end user. However, in our case we don't need that table so I'm going to delete the field 'Supplier' from the table 'Products'. First, check if we have the field 'Supplier' added as an Indexer, if there is then we need to delete that first.

On the top horizontal ribbon, click on Indexer






I am going to delete both Supplier and Category indexes. Then I am going to delete the Supplier and Category Lookup fields as shown below



Now I should be able to delete both the Supplier and the Category tables as well. However, now we still have a 'By Category' form which remains. When I access the form below is what I see.





I see that the Category field (which was a lookup field) still shows up even after deleting it. For now I am going to change the 'Group By' from Category to 'Quantity at Hand' Below is a screenshot.



I saved the changes and launched the app to confirm that the deleted indexes, lookup columns and tables didn't cause any issue…but they did. Below is a screenshot



So it looks like the Access app is still looking for the field. So here's what I am going to do. Go back to Access and add a Category field to the Products table. I'll make it a lookup field and add 'Camera Body, Camera Lens and Camera flash'. I will also change the name of the 'By Category' form to 'By Quantity'. Save it and launch the app.

This time I shouldn't see any errors. Below are screenshots.







No error!! Success!!

Similarity: The lookup field in Access looks a lot like the Choice type list column. Also Access has a Lookup field which is very similar to the Lookup column in a list.

For this second table as well I have created a video which walks you through the steps I have described above.



(To view a higher resolution video click here)

Customers Table

We are now going to build the Customers table. The process is exactly the same except choose the Employees table template as shown below.



Once the Customers table is created, If you click on the Navigation pane, then you will see the respective new forms as well. Below are screenshots of both the new Customers table and the new forms.



The customers table is very similar to the employees table except that it has a different set of fields in them. We will focus more on the By Group1 form.
The new 'By Group'1 form for the Customers table has two controls. One is called Group simply because this control shows the group of customers and counts the total number. The second control on the right will give a four descriptions of the Customers. The four descriptions include Display name (includes first and last name), company, job title and work phone. Below are screenshots of these two controls.

Orders table

We are now going to build our last and final table, the Orders table. Below is a screenshot of the Orders table template. The description of this template states ' Orders for selling goods to companies or people - includes Order Details, Products and Customers.'…Well…ummm...I already created the Products and Customers table so does this mean that when I now create the Orders table I am going to have a secondary Products and Customers table? *Gulp*…Lets' see…



Phew…It didn’t!! You will see two new tables created. One is a Orders table and second an Order Details table. I also see a total of 6 new forms created. Later on, we will look at what changes were made to both to all the tables including the employees and customers.

Lets' go through these tables and forms one by one. Below is a screenshot of the new tables and forms.



Below is a screenshot of the Orders table. As I was viewing it I immediately noticed the two lookup fields. I'm very impressed with how Access can 'read between the lines' so to speak and make the connections needed to generate desired outputs with pre-existing data.



Below are screenshots of the Order Details table. I see three lookup tables here, two are the same as the Orders table and the third one, Status, is where values have been added manually.





The Order Details table is hidden which is why you see the table looks grayed out and if you look at the details you will see the 'Unhide' option.





Most of the Order forms are pretty straight forward as well and have very similar setup to the forms we have viewed for other tables. However, I do want to focus a little more in detail on the 'Orders List' form. This is the first form we have come across which is using a very popular control called 'Related Items Control'
In the screenshot below you will see the Order Details has been added as a related item. Now you see why the Order Details table was hidden?


Save this app, launch it and view it on the browser. Below are screenshots of the tables on the browser. Currently these tables have no data.





Also now you will notice related items controls have also been added to both the Employees and Products list forms as well. Now you realize what were the changes made to these two existing tables when the new Orders table was added. Once again, I'd like to point out the remarkable job Access does to make changes to already existing data.

Below are the screenshots.




I have created two videos, the first video which walks you through the process of adding the Customers and Orders table.



(To view a higher resolution video click here)

The second video is built after some data is added in these tables to see how the 'Related Items' control works.



(To view a higher resolution video click here)

Re-Designing

In this final design section, I am going to make changes to the forms.

Change the design of the Employees form.

Similar to what we can do in the InfoPath designer I am going to change the design of the form. I am adding a title, image and move some of the fields. I am going to launch the Access application and edit the Employees List form. From the Controls ribbon, I am going to add the 'Label' control to give this form a title. I am also going to add the Image control.

Change the 'By Quantity to Category in the Products table

Earlier in the Products form I added the By Quantity on Hand as a control. This field only shows numerical data which can be confusing, so I am going to change it to 'Category'



Below is the video which walks you through the re-designing process.



(To view a higher resolution video click here)

Building Macros

In this section I am going to focus on building macros to display certain forms. My Intention to do this is to try and mimic InfoPath Designer's 'Form Load' rules. In InfoPath you can force a certain view of a form to be displayed by creating rules. In Access app you can do the same, but instead of rules we build macros.

Change Table View

I want a comment to appear on the screen when a user is on the app telling the user to view the existing orders before adding a new to avoid creating duplicate orders…
Then I would like the 'By Status' form for Orders table be set as the default i.e. after the user views the comment and clicks 'OK' you will see the 'By Status' form in the Orders table.



In the Action Catalog choose the Message Box action and type your message.





Next choose the Change View action and fill the conditions as shown below. You will not be provided a drop-down list to choose your table and view name, hence you need to be aware of the names before you build this macro. When you attempt to close this macro or save it you will have to name it. I recommend you give it a name that contains the table name. In my case I named it 'MacOrders'.

On Start View

Click on Advanced and this time select 'On Start Macro'.





You should see the macro you created. Select it, save the on start macro. As you can see from the screenshot below, you can directly add a Message Box and Change view in the On Start macro, however, over the period of time you might add several actions here which can get confusing hence I recommend that you create separate macros. I do want to point out that this recommendation comes with a caveat that if and when you delete the macro (like the MacOrders) then you must remember to delete it from the On Start macro as well. If you don't then you will get an error. I have re-created this scenario in the video I have provided below.



After you have saved the app and launched it. You will see the below message. Notice how we do see the Employees table first, but after you click the Ok, you are re-directed to the By Status view of the Orders table.



Below is a video which walks you through the process of building the macros.



(To view a higher resolution video click here)

Boundaries and Limitations of Access App

When it comes to Access web app there are several limitations hence if you are a die-hard Access user then pace yourself for the some of the limitations you are going to run into when building the Web App.
Here is a screenshot of what the ribbon options are for the Web App



Now, here is a screenshot of what the Create ribbon options are for a local desktop database. See what I mean?




Below are a few limitations that I ran into which I immediately noticed.

Queries and Forms.

There are several options available for a Local Desktop Database when it comes to queries and forms. The Query Wizard is very popular which is not available in Access Web app.



As far as forms goes, the most popular view is the 'Split Form' view which doesn't exist in the Web app.



There are also limitations for Macros. Below is a screenshot of all the Action Catalog currently available in web app.



Now, below is a screenshot of Actions catalog if you are creating a local Access database. See the difference? The web app has 20 actions available while the local access database has over 60.



You can view all the boundary limits at http://technet.microsoft.com/en-us/library/cc262787.aspx.

Also look at the article which lists the changes in features and functionality between Microsoft Access 2013 and Access 2010. It also describes how the changes might impact your Access database and provides alternatives for some of the removed features and functionality.
http://office.microsoft.com/en-us/access-help/discontinued-features-and-modified-functionality-in-access-2013-HA102749226.aspx

Conclusion

 I am a big fan of No-Code solutions using Lists, InfoPath Designer and SharePoint Designer in an On-Premise farm. However, this deep-dive study, which I am sharing with you has changed my perspective of Access 2013 web app with SharePoint Online. I am very pleased with the features and functionality it provides. 

See Also

An important place to find a huge amount of SharePoint related articles is the TechNet Wiki itself. The best entry point is SharePoint Resources on the TechNet Wiki