Access 2010 Web Databases: Web Compatibility

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

Back End Compatibility

In my previous post (Access 2010 Web Databases: How Can I Put My Access Database On The Web?) I talked about the new ability of Access 2010 and SharePoint 2010 to create and deploy a web database application completely in Access. As with any database project, you start with the data, so that's what I'll discuss here.

As I mentioned previously, I'm going to take an existing application (Books.mdb) and convert it into a web database. The Access-client version of the application is a "split database" (for a description of a split database, see: Split a Database), which means there are actually two files: Books.mdb and Books_be.mdb. So I'll start with Books_be.mdb, since that has the tables I need.

The first thing I need to do is save the database as an ACCDB. The MDB format, while still useable in Access 2010, does not have any of the features needed to create a web database.
The table structure and relationships in this application are simple:



There is a Many-to-Many relationship between tblBooks and tblAuthors, which is resolved with the intersection (or "linking") table lnkBooksAuthors. (See Many-to-Many Relationships for additional information.) Nothing especially earth-shaking here, but even this simple design presents some problems.

Web Compatibility Checker

To see what those are, I go to File > Save and Publish > Publish to Access Services.



And then run the Compatibility Checker:



This produces a report which details all the errors:


(click picture to enlarge)

So what do they mean?

Issues 1 & 2: Primary Keys

• Problem: My lnkBooksAuthor table has a compound Primary Key. Compound indexes are not allowed. Also, a table should have a Long Integer number field as a single-field primary key.
• Solution: Add an Autonumber field (just call it ID) and make it the primary key. Every table should have an Autonumber Primary key. This will solve both the problems of composite keys and non-numeric keys. The problem with an autonumber primary key is that, while it is guaranteed to be unique, it will not guarantee real-world uniqueness for the record. So ordinarily I create a Unique Index on a combination of other fields, but as we see in Issue 2, composite indexes are not allowed. But there's a way around that.

Issue 3: Unique Indexes

• Problem: There is a Unique Index on tblAuthors that consists of AuthorFirstName, AuthorMiddleName, AuthorLastName. The purpose of this index is to make sure an author is not added to the database twice.
• Solution: Create a Data Macro (something like a trigger) in the table, to test for duplicates. I will discuss this in greater depth in a later post (see: Access 2010 Web Databases: How can I create a Compound Unique Index?).

Issue 4 & 5: Relationships

• Problem: Relationships are supported by SharePoint, but they have to be defined in a slightly different way. 
• Solution: Use the LookUp Wizard to create a look-up field in the table on the "Many-side" table (foreign key) to the corresponding field in the "One-side" table (primary key). This is one reason a table can't have a composite primary key. I will discuss this in greater depth in a later post (see: Access 2010 Web Databases: Creating Relationships).

.