Access 2010 Web Databases: Creating Relationships

Unfortunately, as of Access 2019, Access Web Apps have been discontinued.  From the March 2017 announcement from Microsoft:

“We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.”

In this blog series, I'm discussing how to convert an existing Access application to a web application (the series starts here: How Can I Put My Access Database On The Web?).

As with any database project, I'm starting with the data. When I attempted to upload my existing tables into SharePoint, I got a number of errors (Uploading the Data to SharePoint). I dealt with some of them (primary keys and unique indexes) in my last post (How can I create a Compound Unique Index?). I'll finish up with the last of the errors: relationships.

Creating Relationships

Relationships in SharePoint require the use of a Lookup Field. This is another feature that experienced developers have been railing against (see The Evils of Lookup Fields in Tables ) since it was introduced in Access 2000. Mostly, this is because Lookup Fields are used poorly. It may seem convenient to display a value other than the linked field, however if you are creating an application, it really does more harm than good. But, lookup fields don't have to be used to display a different field than the one that links the tables. You can also create a lookup field that actually displays the primary key value in the field rather than some other field. Doing so will remove the most egregious behavior of lookup fields, while still allowing you to use them to create relationships in SharePoint.

I still discourage their use in client systems (at least as far as displaying a value other than the one used to create the link), but they're necessary in web databases, so I've come to terms with them.
There are two ways to create Lookup fields: manually or with the Lookup Wizard. Most tutorials on web databases illustrate how do create a web database from scratch. In that case, the Lookup Wizard is the simplest way. It will walk you through all the steps necessary to create a proper relationship (with Referential Integrity) that will be acceptable to SharePoint.

However, this tutorial base on the premise you have an existing, client database with proper relationships already created that you want to convert into a web database. (Note: in this case, "client" means the database application runs in Access.) However, that's not enough to upload it to SharePoint. In addition to having a properly created relationship in Access, you MUST use a lookup field to upload it to SharePoint. If you've already got your relationships created and especially if you have data in your tables, it is far easier to create the lookup fields manually than to use the wizard.

Relationships must be created on the "many-side" or "child" table of a one-to-many relationship. As noted in the Relationship diagram above, the only child table is lnkBooksAuthors, and as a linking table, it is a child to both tblAuthors and tblBooks. Therefore, I need to go into each field and add the Lookup properties. I'll demonstrate it on AuthorID , however, BooksID would work the same.

To modify the AuthorID field, I open the lnkBooksAuthors table in design view, select the AuthorID field, and click the Lookup tab. It looks like this:

Selecting Combo Box in the drop down list will give me the options necessary to set the lookup properties.

I'll leave the Row Source Type as Table/Query, then select the Builder button [...] of the Row Source property to create the link. In the Query Builder pop-up, I'll select the "one-side" table I want (in this case tblAuthors)

And create the following query:

Closing and Saving the Query Builder results in a Row Source of

SELECT tblAuthors.AuID FROM tblAuthors;

The only other change is I must make is to set the Limit To List property to Yes. The final result looks like this:

Because I've left the rest of the properties like the Bound Column and Column Count as they are, the result is a Lookup Field that displays the linking field.

After I do the same for every relationship in my database (which in this case is just the tblBooks/lnkBooksAuthors relationship), my database is ready to be uploaded to SharePoint, which is my next topic. (See Uploading To SharePoint).

.