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.”
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 (
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:
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:
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