Wednesday, May 12, 2010

Access 2010 Web Databases: Web Compatibility

(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)

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

3 comments:

Anonymous said...

I have encountered a problem with primary keys (autonumber) in Access Web Database. By accident, the link for each other is using the primary key. It means table 1 is looking up table 2's primary key and vise versa.

This scenario can't be uploaded to Sharepoint as this is not an allowed relationship. Primary keys can't be deleted or modified as it is a primary key. Look-up with each other can't be deleted. Tables can't be deleted because they have relationships with eachother. So far I haven't found a solution for this type of scenario.

Have you encountered this type of error before? How did you handle it?

Roger's Access Blog said...

No, I have never encountered this problem. It certainly seems as if the lookups should be able to be modified to look up the correct field.

But this isn't the best venue for support. I suggest you ask this question on a forum like www.utteraccess.com

Anonymous said...

What if the "Publish to Access Services" button is greyed out?