Tuesday, May 25, 2010

Access 2010 Web Databases: Creating Relationships

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


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

.

Tuesday, May 18, 2010

Access 2010 Web Databases: How can I create a Compound Unique Index?

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


As I mentioned in a previous post (Access 2010 Web Databases: Uploading the Data to SharePoint), in an Access client database, I normally create a Compound Unique Index (that is, a unique index on multiple fields) to insure real world uniqueness for a record. Unfortunately, SharePoint does not support compound indexes, so I'll have to simulate it.

Composite Unique Indexes

There are two ways to simulate the effects of a unique index on multiple fields. Both involve the use of Data Macros, which are similar to triggers in other database systems. Data macros are automation that takes place at the table level.

Nulls Allowed
The first method is used when all of the fields in the "index" are required. You Look Up A Record (similar to a DLookUp) and use a with a multiple Where Condition to test whether those values have already been used. In my database project, the linking table (lnkBooksAuthors) has a unique index on the combination of the two Foreign Keys: AuthorID and BookID. Usually, I will use those two fields as a Primary Key. However, as I showed before, SharePoint does not allow multiple field primary keys. So I created an Autonumber primary key, and now I have to simulate a unique index on those fields.

In the BeforeChange event, use the following (click to enlarge):



The full Where Condition (above), looks like this in the Expression Builder:



Allowing Nulls
The second method is used when all of the fields in the "index" are NOT required. For instance, in my tblAuthors table, I want to create a unique index on AuthorFirstName, AuthorMiddleName, and AuthorLastName to make sure I don't get duplicate records. But there's a problem. Authors often don't have middle names, so the AuthorMiddleName field must allow NULL values. But a NULL in any of the values will prevent the expression to valuating to TRUE, so duplicate records could be saved.

To get around this, simulate the unique compound index with a Data Macro on a Calculated Column. In this case, one that looks like "AuthorName" (below):



Ordinarily, I would advise strongly against using them because they violate the Rules of Normalization. But because SharePoint is not a relational database, you will often have to do some things I'd ordinarily frown upon.

Therefore, your data macro would look like this instead:



Using a calculated column has 3 advantages:
  1. the Where condition in the macro is shorter and easier to read,
  2. it handles the NULL value problem, and
  3. this column can be used throughout your application where you need the full name in "LastName, FirstName MiddleName" format.
So this takes care of my Compound Index problem, but I still need to solve the issues with Relationships that I discussed previously. That's what I'll talk about next in Access 2010 Web Databases: Creating Relationships.
.

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

Monday, May 10, 2010

Access 2010 Web Databases: How Can I Put My Access Database On The Web?

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


How Can I Put My Access Database On The Web?

One of the perennial questions in the Access newsgroups and web forums is "How can I put my Access database up on the web?" Until recently, the only answer was "Learn ASP.NET or PHP and re-program it."

With Access 2007, it became possible to upload your data to a SharePoint list in the "cloud", that is, a server somewhere up on the web. However, the actual application still had to be run in the Access client, either in Access itself or the Access Runtime.

But with Access 2010 and Access Services in SharePoint 2010, it is possible to also publish your application, (ie, forms, reports, queries, and macros) up to the web as well as the data. From there, the application itself can be run in a browser. So the new answer is: "Go to the File menu, choose 'Save and Publish' and then 'Publish to Access Services'."

Can it really be that simple? Well, no. Not really. The chances that an existing application of yours will simply upload to the web are relatively small. SharePoint Access Services has specific requirements both for your data and application design. Some of these requirements may be at odds with your existing development paradigm.

For instance, a SharePoint back-end does not have relationships per se, it cannot have a composite primary key, and cannot have multiple field indexes. Web database applications cannot have any VBA code, only macros (although the new macros in Access 2010 are more robust than previous versions), and forms must be designed in Layout View rather than Design View. This is not an exhaustive list, just a few examples. I'll discuss them in future posts where I can go into more depth (and as I discover them).

Of course, I needed a project to use, so I decided to take an existing application and make it a web application. I have a sample on my website (Books.mdb) which is fairly simple (just a home book inventory database) but also has all the elements of a robust application. As an application, it's not horribly complex, but it should be enough to give me a good workout. If you're interested, you can download the sample and see what I'm up against.

One last thing before I get started. If I am to create a web database application, I need a server running SharePoint 2010 with Access Services. Hosting services for the samples on this blog are provided free of charge by accesshosting.com.

So let's get started.

The place to start (as with any database application) is with the data. That's what I'll do next time in Access 2010 Web Databases:Web Compatibility.
.

Wednesday, May 5, 2010

Changing Directions



So far, I've used Roger's Access Blog as a sort of introductory Access textbook. In my "Access 101" posts, I've attempted to explain some of those concepts that other websites and blogs assume the reader has previous knowledge of. I'm not nearly finished. There's a lot more to cover. In fact, I'm currently in the middle of exploring some of the more obscure types of queries: totals queries, crosstab queries, pass-through queries, etc. Eventually, I'll take this series back up.

But I'm going to take a break from that to explore something new to me. Access 2010 and SharePoint 2010 have jointly produced an answer to the perennial question: "How can I put my Access database on the Web?"

With Access 2007, it was possible to have your database Back-End in a SharePoint list where users can access data across the web, but the Front-End application still needed the Access client, either Access itself or the Run-time.

But with the introduction of Access 2010 and Access Services in SharePoint 2010, it is now possible to also push your Front-End application to the web as well. Unfortunately, it is unlikely that you can push an existing Access application to the web unmodified. Access Services has specific requirements for database and application design. These requirements are the things I will be exploring over the next few months.

This is all new to me, so it will be a journey of exploration more than a tutorial. I may make some mistakes and say some things in error. As I discover these, I'll go back and amend my posts so no factual errors are perpetuated. For that, I'd be happy for comments that correct my errors. As an MVP, I'm supposed to be an "expert", but outside of the Access development team, there aren't many experts in this new technology.

So, hopefully, we can become experts together.

Editorial note: Normally, it is frowned on for bloggers to edit their posts too dramatically.  However, since this is a factual, rather than opinion series, I'm going to edit posts for accuracy and as new information arises.  So when anyone leaves a comment that corrects information or provides new information, I will try to incorporate that into the existing article (with attribution, of course).  I think it is more important to keep correct information in the web than to attempt to prove that I am always "right".
.