How to Design App to Collect Data Using Access

If you need a simple way to collect data, an Access web app might be the right tool for the job.

hero — Image: iStockphoto.com/Jacob Ammentorp Lund

An Access web app is a database you can use in a web browser. You probably use them all the time with your mobile devices. What you might not know is that you can create and modify a simple web app using Access 2013 or 2016. You can share, view, and modify data. In this article, we'll introduce this feature's possibilities and limitations by generating a web app that tracks books and author contacts.

I'm using Office 2016 with an Office 365 Enterprise E3 subscription on a Windows 10 64-bit system. You'll need Access 2013 or 2016 and a SharePoint environment. This article assumes you understand relational database normalization rules and the nature of relationships.

Upgrade angst

One warning before we get started: Don't confuse Access web apps with Access Services (2010). The technologies aren't compatible and unfortunately the web database technology that looked promising with Access Services isn't supported in subsequent versions of Access. To the best of my knowledge, you can't update a 2010 web database to 2013 or 2016 (although you can migrate the data by importing the tables into a new custom web app).

A short overview

An Access web app has limited scope and functionality. Below is a simple list of reasons not to consider an Access web app:

Your organization isn't using SharePoint Server or your Office 365 plan doesn't include SharePoint Online.
You need Visual Basic for Applications (VBA).
You need to link to external data, consolidate data from different sources, or import data from XLM, Data Services, HTML documents, or Outlook folders.
You need to use update or crosstab queries.
You have complex reporting needs.
You need a password-protected database; permissions are inherited from the SharePoint site where the app is created.

If you still need help determining whether an Access app is right for you, please read Should I create an Access app or an Access desktop database?

Create an Access web app

After launching Access 2013 or2016, click Custom web app (Figure A). When choosing a template for a Web app be sure not to select templates with the term Desktop at the beginning. These are for the desktop version of Access and not web apps. Instead, look for the globe icon or the term SharePoint web app in the name.

Figure A

Choose Custom Web App.

The next screen you see after choosing Custom Web App will depend on your 365 subscription or organization configuration. In my case, the interface displays my web location sites, as you can see in Figure B. These are the sites users will go to when they use your app.

Figure B

Choose a web location for your app.

To finish creating the app, do the following:

Enter a name for the app.
Choose a site.
Click Create to access the interface shown in Figure C.

Figure C

The Access web app interface is limited but user friendly.

In regards to making the right site choice (#2), you'll use the site to store, organize, share, and access information from your many devices: desktop, laptop, and even mobile. If you're using Office 365, you're using SharePoint Online, and the Available Locations list (Figure B) shows the web locations you can use to create your web app. In my case, I have two:

Personal Apps @ Susan Harkins is a private site. The apps will be available only to me.
Susan Harkins is a team site; the app will be available to anyone who uses that site.

If you're using SharePoint Server, Access Services must be enabled and you might need the assistance of your site administrator to access your organization's site.

Add tables

You'll store data in tables the same way you do in a desktop database. There are three ways to create a new table in a web app:

Use a template.
Import data from an existing data source.
Start from scratch.

Let's create a table that stores book titles, ISBN numbers, and a few other details:

Click the Add A New Blank Table link (circled in Figure C).
Using Figure D as a guide, enter the fields.
To save the table, right-click the tab or close it.

Figure D

Create a table from scratch.

Similar to a desktop table, you have access to several data types and their respective formats. The last four fields are foreign keys for related tables, which we don't, as yet, have. I selected the Number data type as a placeholder for now. You could just as easily create the related tables first.

Now, let's create an author table, so we can create a relationship between the two tables. This process is different in a web app because there's no Relationships window. To create the second table, do the following:

Close the Books table if it's still open.
In the Create group, click Table and click the Add A New Blank Table link.
Use Figure E as a guide to create the People table.
Save it and close it.

Figure E

Create a second table for storing (author) names and email addresses.

Add a relationship

At this point, we need to create a relationship between the two tables using the Lookup Wizard. First, open the Books table in Design view by double-clicking it in the Errata Tracking tab. Then do the following:

Choose Lookup from the Data Type dropdown (Figure F).
In the next pane, choose the I Want The Lookup Field To Get The Values From Another Table Or Query option, which will display additional options.
Select the People book.
From the Which Value Do You Want To Display In Your Lookup dropdown, choose Display Name.
You can alter the sort and cascading options in your custom apps, but for this example, don't. At this point (Figure G) click OK.
Save and close the table.

Figure F

Choose Lookup from the Data Type dropdown.

Figure G

Identify the foreign key field.

At this point, the two tables are related. Everything's self-explanatory except for the Display Name field. In a desktop database, you could use an expression to combine the first and last names and sort alphabetically by the last name. The Display Name field is a shortcut that will do the same thing. You'll enter the name yourself in last name, first name format. (This route simplifies the example, but there are better ways.) The sort property will sort the names in alphabetical order by last names. When entering new books, this configuration will make it easier to find and select the book's author if the author is already in the People table.

In a real-world app, you might need to identify multiple authors, but in this simplified app, we'll be working only with a book's "lead" author. That means we'll never need to relate a book to more than one record in the People table. (Of course, you know that kind of thinking always leads to trouble, but to simplify the example, we're going to suspend reality a bit.)

SEE: 10 steps to populate an Access list control using field-level properties

Launch the app and add data

To launch an app, click Launch App in the View group (Figure H). Our simple app's purpose is to track post-publication contact between an errata editor and authors. The editor will enter info for newly published books and their respective authors. In a desktop database, you'd supply an input form that would let you enter all the information at once and then disseminate those values to the appropriately related tables. We don't have that setup in the web app yet. If you enter a book before the author exists in the People table, you won't be able to select an author for that book. The control, by default, will let you enter a name, but doing so won't populate the remaining fields in the table.

Figure H

Launch the app to start entering records.

You could enter the names first, but the web app is smart enough to handle missing names, so let's enter a book before entering any authors to see what happens:

In the newly launched app, click Books and then click Add to display a blank form and enter a few values for the new book (Figure I).
When you reach the author control, enter the name in last name, first name format. As you can see in Figure I, there's no matching value for the example author.
Click <Add A New Item> to open the related People table.
Enter the author's information to create a new author record in the People table (Figure J).
Save the author record and close the form. The Books form will display the newly added author.
Finish the book record (if necessary) and click Save.

Figure I

Click Add to add a new book record.

Figure J

Add the new author record.

Because of the relationship, the app helps you find and enter related values. You didn't have to jump through any development hoops to make this happen; the app did it for you.

SEE: How to print one or more labels for a single Access record

Sharing

An Access web app is available to anyone with access to your team site. You can also share it with others outside your organization. If you're the administrator for your sites, flip the sharing switch. If you're not, you will need to ask your administrator to do this for you. Then, you can invite others using their email address.

To change the sharing status as an administrator, sign into your 365 subscription account using the 365 portal as the administrator. From the Resources option, choose Sites and select the site you want to share. Edit the Sharing Status to On. Afterward, you and other members to this team site can sign in and click the Share tile to complete the process.

Worth it?

Access web apps will prove useful to some organizations. It's limited but user friendly. With a web app, you can be entering data within just a few minutes. In subsequent articles, we'll explore this feature's more sophisticated offerings.

If you have advanced needs, there are better development environments to consider, such as Visual Studio. The learning curve is steep but the long-term benefits are greater.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.