What Is Normalization: Part III

Putting It Back Together

In What Is Normalization, Part I, I defined normalization and the negative consequences of not normalizing your database. In Part II , I talked about how to normalize your tables to remove as much redundant data as possible. Now, it isn't much good to split your data into separate tables without the ability to put it all back together. For that, we need to talk about relationships.

Relationships store the information about how normalized data can be put back together. They are the heart and soul of a relational database like Microsoft Access. If you don't have relationships in your database, you don't really have a relational database.

Continuing our example from Part II, we have two tables: Employee and Salary History.

Figure 1: Employee

Figure 2: Salary History

So how do we create a relationship between them? In a relational database, tables are related on common data elements -- specifically the primary key of one table and the foreign key in the other. I've defined primary key elsewhere, but foreign key is new. A foreign key is simply a field in another table that has the common data elements needed to create a relationship.
In our example, SS# is the primary key of Employee. In the Salary History table, SS# is the foreign key to the Employee table. If you've normalized your database properly, your related table should have a foreign key. Figure 3 shows how the data in our two tables will be related.

Figure 3: The tables related

So how do we create this relationship? In Access, the easiest way is to use the Relationship Window. In Access 2003 and before, it can be found on the Database Toolbar or on the menu choosing Tools > Relationships. In Access 2007, go to the Database Tools tab on the Ribbon and choose Relationships.

To create your relationship, add both tables to the Relationship window. I usually RIGHT-click on the empty relationship window and choose Add Table. Your window should look like Figure 4.

Figure 4: Relationship window with tables added.

Next, Click on the primary key (SS#) in Employee and drag it over to the corresponding field in Salary History (also SS#). When you finish, the Edit Relationships dialog box will pop up. Figure 5 shows the dialog box.

Figure 5: Edit Relationships dialog box

It will already have the fields filled in. In order to actually make this a relationship, however, you MUST click the Enforce Referential Integrity check box. If this box is not checked, it it NOT a relationship.
If you cannot click the Enforce Referential Integrity box, then there is something wrong with your relationship. More than likely, it will say Indeterminate as a Relationship Type. Usually, this means you have attempted to create a relationship without one of the fields being a primary key. Don't simply uncheck the box and continue. Go back and fix the fields.
Click Create to finish the relationship.

Figure 6: Completed Relationship

You can tell that Referential Intergity has been enabled because the relationship line has a 1 on one end and an infinity symbol on the other. This indicates it is a One-to-Many relationship. That is, each record in the Employee table is related to one or more records in the Salary History table. The 1 will be next to the table where the link field is the primary key. If you do not have those symbols on your relationship, you do not have a relationship.
Here, I showed the One-to-Many relationship, which is the most common type in a relational database. But there are two others: One-to-One and Many-to-Many. I'll talk about those next in What is Normalization: Part IV.
.

Access 2010 Web Databases: Viewing Tables In SharePoint

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 discussed errors in creating relationships in a web application (Creating Relationships). Last time, I talked about loading the tables into SharePoint (Uploading To SharePoint).

This time, I want to take a look at the tables in SharePoint itself. It's not really necessary. You can and should do everything you need to do in Access. However, viewing your application in SharePoint can be instructive.

Go to File > Info:

[click to enlarge]

And click the link. The opening screen will look like this:

[click to enlarge]

The Design With Access link will allow you to make changes to the application in Access. I should point out here that you should always make any changes in Access and not directly in SharePoint. You can easily mundge your database into uselessness. Clicking the link will download a new copy of the application (with an ACCDW extension) to your hard drive. Make your changes and then save it back to SharePoint.

If you want to delete the application, under Settings, click the Delete This Site link. You won't do this often with a production database, of course, but if you're experimenting, you might need to do this. In the course this series, I did it quite a lot.

Table Properties
 I can look at my table properties as well. Navigate to your lists/table by going to a URL like so:

http://<yourserver>/<yoursite>/Lists/<yourtablename>/Allitemsg.aspx

You should see your table/list in datasheet view. Click the List Settings button on the List tab of the SharePoint Ribbon.

[click to enlarge]

Clicking AuthorID, I can view the properties of that table:

[click to enlarge]

From this, I can see that all the properties I set up in Access – including the relationships – made the transition to SharePoint. However, I want to re-emphasize that although you can view your tables in SharePoint, you should never modify them there. With Access Services, everything begins and ends in Access.

Next my next post (Other Compatibility Errors), I'll finish up the data portion of this series by looking at other data related issues that you need to watch out for.
.

Access 2010 Web Databases: Other Compatibility Errors

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 started 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 discussed errors in creating relationships in a web application (Creating Relationships). I talked about loading the tables into SharePoint (Uploading To SharePoint). Last time, I showed how to view the tables in SharePoint (Viewing In SharePoint).

In this final post discussing moving legacy tables from Access to SharePoint, I'll look as some other common errors that keep your tables from uploading to SharePoint.

Other Compatibility Errors

1. Property value contains one or more characters that are incompatible with the Web.
   Table, field, and control names cannot contain characters that the Access client will accept. They cannot be longer than 64 characters and may not contain any of the following: / \ : * ? "" < > | # <TAB> { } % ~ & period (.), exclamation point (!), square brackets ([]), leading space, or begin with an equal sign (=).
2. Only one of the Allow Zero Length property and Required property should be set to 'true' to be compatible with the Web.
   As the error says, you can't set both the Allow Zero Length and Required property of a field to "yes".
3. Property value should to be empty or set to 'auto' to be compatible with the Web.This error refers to subdatasheets, which are not allowed in SharePoint lists. To correct it, set the Subdatasheet Name property of the table to [None] or [Auto]. You'll also get this message if you have values in the Link Child Fields and Link Master Fields properties. These properties must be empty.
4. Calculated Column expression has an input field of a type incompatible with the Web, such as AutoNumber, Lookup, Memo, Hyperlink or Binary.
   Calculated columns are new to Access 2010, so they won't be in legacy systems. However, if you do use them, you can only use the following data types: Text, Number, Currency, Yes/No, Date/Time.
5. Calculated Column result data type is incompatible with the Web.
   Likewise, the result of a calculated column can only be of the following data types: Text, Number, Currency, Yes/No, Date/Time.
6. Column data type for value list lookups must be 'Text' to be compatible with the web.
   If you use a Value List for a lookup field (which I strongly advise against, anyway), it has to be filled with Text values. You cannot use numbers. A better solution, however, would be to create reference lists that you can query to fill the list.

There are others. I won't try to list them all here. In the Web Compatibility Issues table created by the Compatibility Check, there will be a link referencing each error.

Then, of course, there are errors (many, many of them) associated with uploading your application (forms, reports, etc.) to the web. I'll deal with those in a later series.
.

Access 2010 Web Databases: Uploading to SharePoint

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?). Last time I discussed errors in creating relationships in a web application (Creating Relationships).

I'm finally ready to upload the tables to SharePoint. Before I do that, however, I need to check the compatibility again (see Compatibility Checker). Assuming there are no more errors to correct, I can Publish To Access Services.

Access Services

Obviously, I need a SharePoint 2010 site with Access Services installed. While it's always possible to set up my own server, there are various hosting companies that provide Access Services, and there will be more in the coming years.

Important Note: Hosting services for this blog series are provided free of charge by http://www.accesshosting.com/. They are one of the first SharePoint providers specializing specifically in Access Services. If you'd like to try anything from this blog series, AccessHosting provides a 30-day free trial. They also have a Developer Sandbox option for developers to use Access Services and prototype solutions for customers beyond the 30 day trial.

So once I have my hosting account, I can fill in the appropriate information: the Server URL and the Site Name. The Server URL is my account on the SharePoint host and the Site Name is the name I want the site to have. In my case I chose the name of my database.

Once the information is filled in, the Publish to Access Service button will become active. Clicking it will bring up a login window.

After entering my password and clicking OK, I'll see a dialog box that shows the various object being uploaded to SharePoint

When it's done, I'll see the following:

Publishing to SharePoint produces a number of irreversible changes to the database, which I'll discuss presently. Fortunately, Access makes a copy of the original, pre-upload database with a "_Backup" suffix, so if you have problems, you can easily go back to your original version.

Database Changes

The first and most obvious change is that my tables now look different

The icon in front indicates they are now SharePoint Lists. As such, you can no longer go into Table Design view to modify your table. You must make any changes in the Table Tools Ribbon.

Between them, you have most of the table design ability of the Table Design View, limited, of course, to those properties that SharePoint list support.  Creating new objects like Queries, Forms, and Reports are more restricted.

Some queries like Aggregate Queries and Union Queries (to name just two) are not supported and forms can only be created in Layout View. Certain query, form, report, and code types are only supported in the "Client", that is, it will only run when used in Access as opposed to a Web Browser. This is useful in a Hybrid Web Application, but not in a Browser-Only application (which is what I'm hoping to develop here).

Many of the Database Tools are also absent in a Web Application. These include the Relationships Window, Performance Analyzer, Table Analyzer, and uploading data to SQL Server and SharePoint.

There are other differences that vary depending on the specifics of your database, but once you've uploaded your database to SharePoint, you need to be aware that some Access features are limited.

In my next post, (Viewing In Sharepoint), I'll look at the tables in SharePoint.

.

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

.

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

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

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

.

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

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

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

Access Web Databases: 2010 vs. 2013

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

Access web databases are dead.  But there’s no cause for alarm.  Now we have Access web “apps”.
With the introduction of Access 2013, Microsoft has made substantial changes to its vision of how to put Access database applications on the web. The differences between Access 2010 web databases and Access 2013 web apps are major, and you should consider them carefully before embarking on an Access web project.
Before I go further, I should point out a relatively minor terminology change.  Microsoft refers to 2010 web projects as “web databases”, while it refers to 2013 web projects as “web apps”.  I don’t think this will help much to differentiate the two products, but it’s at least worth noting. I’m going to continue prefacing the terms with the Access version, just to be clear.
So what are the differences, and why are they important? 
Differences
The most important difference is where and how the data is stored.  Both 2010 web databases and 2013 web apps require SharePoint (although different versions)  However, 2010 stores the tables and application in SharePoint lists, while 2013 stores them in SQL Server tables.  The minimum SQL Server version for working with Access 2013 web apps is SQL Server 2012. No earlier versions of SQL Server will work in this setup.
Secondly, both require SharePoint Access Services, but 2010 uses Access Services 2010 while 2013 requires Access Services 2013.  These services are very different and mutually incompatible. However, you can have both services running on the same SharePoint site, so you can host both access 2010 web databases and 2013 web apps as long as you have both services running.
Importance
This incompatibility means that 2010 web databases and 2013 web apps are entirely incompatible.  It that if you create a 2010 web database there is no upgrade path to 2013.  You cannot convert a 2010 web database to a 2013 web app.  It will have to be re-created from scratch.  You will still be able to maintain it in 2013, but you cannot create a new project.  There is no guarantee that future versions will even be able to do that.
On the other hand, you can easily migrate your data (structure and data) from a 2010 web database to a 2013 web app, but the application (forms, etc) will have to be rebuilt from scratch.
Recommendations
There are, of course, many other differences between Access 2010 and Access 2013, just as there are between any two versions of a product.  But in terms of deciding which is right for you, these are the most important.
If you already have an Access 2010 web database, there’s no need to immediately redevelop it in 2013.  But if you’re planning on using it in the long term, you should begin making plans to move to 2013.  All of Microsoft’s future development will be in the 2013 web app arena and future versions of Access may not even support 2010 web databases.
If you are starting a new project, there is no question you should be using Access 2013 web apps.