In my last previous blog post, I described one of the problems with exporting existing tables from Access or SQL Server to SharePoint. SharePoint bumps the existing Primary Key field out of its way and replaces it with a new one which SharePoint generates in its place. Here’s how that looks. In the second table in this screenshot, called “tblCustomer_SPBlog1, SharePoint has renamed the orginal “CustomerKey” field to “_OldID” and added a new AutoNumber-based field called “CustomerKey” in its place. "CustomerKey" is/was the Primary Key field for this table.
 |
Figure 1. SharePoint Generated Primary keys
|
The first six CustomerKey values in this new SP table are the same, but as soon as the gap appears in the previous CustomerKey values at the seventh record, they go wildly off-track. Fortunately, SharePoint didn’t delete the prior values; they’re still there as _OldID.
If you were to re-define a relationship between this table and another existing table, using this new, SharePoint-created CustomerKey to match any existing CustomerKey Foreign Keys in that second table, you’d find that relationship makes no sense for any but the first six records. Bad, very bad. And even worse, unless you know about this little trick ahead of time, you’ll end up confused and unhappy. I know I was the first time it happened to me. Fortunately, you've been warned now, and in the rest of this blog post I'll tell you how I dealt with it.
In this post, I'll show you how you can patch up the relationships between this table and related tables, in which CustomerKey appears as a Foreign Key, based on the fact that we know what SharePoint is up to behind the scenes.
 |
Figure 2. Address List Before Move to SharePoint
|
Let’s do that with an address table which relates one or more addresses to each of the Customers in the table shown above.
This table has a Foreign Key, CustomerKey. It is related to the Customer table through the relationship on CustomerKey in the two tables. What isn’t clear from this screenshot is that the relationship matches values from the CustomerKey field in this table to values that are now found in the “_OldID” field in the migrated Customer table. This CustomerKey field no longer matches the values of CustomerKey in the SharePoint table, except for the initial three records in the CustomerAddress table in which the CustomerKey values were not changed during the migration of the Customer table. As you recall, that is because SharePoint bumped those old primary keys off into "_OldID".
Once we migrate this CustomerAddress table to SharePoint, we’ll be able to re-establish the proper relationships, and that’s what I’ll show you next. I’m not going to walk through the table migration steps again. Refer back to my previous blog post if you want a refresher. Here’s what the migrated CustomerAddress table looks like after I migrated it into SharePoint.
 |
Figure 3. Address Table After Migration to SharePoint
|
Resolving A Puzzling Inconsistency
Note that for this Address table, the “_OldID” field created for it by SharePoint still lines up with the new, SharePoint-generated Primary Key field, CustomerAddressKey. That is because there were no gaps in the sequence of values in CustomeAddressKey in the existing SQL Server table. Refer back to Figure 2 to confirm that.
To me, this inconsistency seems to be potentially even more confusing for someone just starting out with SharePoint. Primary Keys in some tables, like the Customer table, go all hinkey on us when they are migrated to SharePoint. Primary Keys in other tables, as in the Customer Address table, come through the transition intact. If you aren’t aware of the reasons for this outcome, and alert to the results it creates, it can be even more confusing, IMO, to be “successful” sometimes, but not always. But now you do know why it happens, so it’s cool, right?
Okay, so now we have two SharePoint tables migrated from the source tables in SQL Server, and we can go to SharePoint, relink them back into the Access database, and re-establish the relationship between them in a way that SharePoint can understand.
Start by selecting the SharePoint icon from the “More” drop-down under “Import & Link” to External Data on the ribbon. See Figure 4.
 |
Figure 4. Link to Existing SharePoint List
|
When the dialog opens, you’ll see a list of the SharePoint sites to which you have permissions. It should, of course, include the one to which you just published your Access tables. In my case, that’s the one shown here.
 |
Figure 5. Select a SharePoint Site with Existing Lists
|
Select the site and click “Next” to see the available lists.
 |
Figure 6. Select One or More Lists to Link
|
Notice that in my existing SharePoint site, all of the real lists I’ve previously created for my working database are available, along with the two I put together for this blog series. I’m only interested in the two of them for this blog post. In your case, you'll check the lists you want to link and then click “Ok” to finish up.
The result of the linking action is shown in the next screenshot.
 |
Figure 7. Linked Lists with One Related List Included Automatically
|
One and One Equals Three, Sometimes
The Navigation Pane now includes both of the lists we just selected from SharePoint, with the suffix "1" appended because the original tables are still there as well.
However, the Navigation Pane as shown in Figure 7 includes one additional list we didn’t ask for: "tlkpAddressType1". Why is that table there? Quite simple, it’s a lookup table related to the CustomerAddress table. Remember, I am working with tables that I’ve already defined on SharePoint for my real working database. SharePoint and Access already “know” about this related table, and Access therefore brought it down to ensure the CustomerAddress table works correctly in Access. That wouldn't have happened if I were working with a new SP site with no other tables there.
Once again, this "silent assistance" from Access and SharePoint might be a bit confusing the first time you start linking tables, or lists, from SharePoint into your Access data base and see “bonus” tables popping into your Navigation Pane. However, it is a very handy thing to have that tight integration working on your behalf, if you need it. And once you come to expect it, it won't be a surprise.
On the other hand, when you are first migrating tables to SharePoint lists, SharePoint and Access won’t necessarily know about those relationships, so you won’t get that assistance. It only happened here because I’m mixing existing tables with new ones for this Blog.
Tidy is as Tidy Does
Next I’m going to tell you about cleaning up these tables to work in your Access - SharePoint database. We could do some of this cleanup later, but it's going to be easier to show you what we're doing if we get some of the dreck out of the way now.
 |
Figure 8. Delete Unneeded Columns from SharePoint Table
|
First, let’s get rid of the four SQL Server columns we don’t need, as highlighted in the screenshot in Figure 8.
These are the columns used in SQL Server to track record changes. SharePoint has its own fields which do exactly the same thing, automatically, so we can drop them from the list now that it’s in SharePoint. In the next screenshot, you can see the table with those columns deleted and the Key fields highlighted in red and yellow. The yellow highlighted fields being the new Primary Key for the table and the old Primary Key, bumped off and renamed "_OldID". The red highlighted field contains what USED to be the Foreign Key back to the Customer Table. We'll fix that next.
 |
Figure 9. Cleaned Up Address Table
|
Clear as Mud?
Here’s where we have to stop and make sure we have the basic concepts clear. There are two fields called “_OldID” in our sample tables, one in the Customer table and one in the CustomerAddress table. BOTH fields contain the previously defined Primary Key for the table in which they reside. We’re going to use the “_OldID” field in the CUSTOMER table to redefine the relationship between Customers and their Addresses. In the SharePoint version of the CustomerAaddress table, we still have a field called “CustomerKey”, but that field does NOT point back to the CustomerKey field in the Customer table. It points back to the “_OldID” field in the Customer table. Is that clear? Let’s look at a graphic representation to be sure we’re on the same page and ready to move forward.
 |
Figure 10. Customer Key (Previous Foreign Key) in Address Table Currently Points back to OldID (Previous Primary Key) in Customer Table
|
The "_OldID" in the CustomerAddress table contains the same values as the New CustomerAddressKey, as we’ve previously discussed. Also, it is not needed in this process. For those reasons, I will just hide it while re-defining this relationship, in order to minimize the potential for confusion between the two "_OldID" fields. (I know I confused myself more than once along the way to this blog post.)
Access and SharePoint did NOT recognize the relationship between the Customer and CustomerAddress tables before we migrated them. That’s because SharePoint only recognizes relationships when they are defined using Lookup Fields. Lookup Fields in tables only exist (to my knowledge) in Access and in SharePoint. So our next step is to fix up that relationship in the SharePoint side. We have to manually replace the existing “CustomerKey” field in the Address table—which is no longer a valid Foreign Key field—with a Lookup Field so that Access and SharePoint can handle it. The first step is to rename the existing field so we can reuse the name "CustomerKey" for our new, properly defined Foreign Key; we’ll call the old one, oddly enough, “CustomerKey_OLD”.
Let's Get Together Again
So, let’s go ahead and do that. If you need help renaming the field in your SharePoint table, consult Access Help. I'm going to assume you can handle that task without coaching. Figure 11 shows the table with the field already renamed.
 |
Figure 11. Insert a Lookup & Relationship Field as a Foreign Key to Replace CustomerKey_OLD
|
Select the "Lookup & Relationship" Field Type from the “More Fields” dropdown under the Add & Delete section of the “Fields” tab. The wizard will open and walk you through defining the relationship. Clicking "Next" takes you to the table selection step.
 |
Figure 12. Relationship Wizard. Step One, Create a Relationship on Another Table
|
 |
Figure 13. Relationship Wizard. Step Two, Select a Table
|
 |
Figure 14. Relationship Wizard. Step Three, Select A Foreign Key Field
|
At this step, be careful to select the correct field from the “one-side” table. Remember, SharePoint has created its own Primary Key field for this table. Even though we know that the values in“_OldID” WERE the primary keys, they have lost that status so don’t get confused and use them here.
Also, I can see no good reason to include any more than the single required Primary Key field in the lookup. The lookup field does allow you to pour on other fields. However, to me, that’s veering too far away from sound database design and I resist the temptation to do it. We use this method to define Relationships in the SharePoint/Access environment because that's the only option open, but that doesn't mean they're useful otherwise, at least not in my opinion.
As an aside, it may be the case that your migrated tables will appear on SharePoint with the new Primary Key field identified as “ID”, which is the default name SharePoint uses. If that happens when you’re migrating your tables to SharePoint, it’s a good idea to rename that field to one that fits your naming convention if needed. In other words, if SharePoint had called this one “ID”, I would have renamed it “CustomerKey” before this step.
 |
Figure 15. Relationship Wizard. Step Four, Sort Order
|
I never bother to add a sort order here, but you can if you want. Here’s why I don’t. This is going to be a Foreign Key in the table. I don’t expect to sort on Foreign Keys in most common scenarios, so it seems superfluous to me. However, all Foreign Keys are indexed, so this one will be indexed for us. Just click on to the Next step.
 |
Figure 16. Column Width of the Foreign Key Field
|
The default column width seems just fine to me. Accept it and click "Next" to go to the next step in the Wizard. The next step, shown in Figure 17, is an important one. This is the step where you tell SharePoint to enforce Data Integrity, which the term used in place of Referential Integrity you see in Access. By selecting "Enable Data Integrity" and "Restrict Delete" you ensure that only valid Foreign Key values can be inserted into the Foreign Key field in the CustomerAddress table.
 |
Figure 17. Name the New Foreign Key Field and Enforce Referential Integrity on it
|
Make sure you name this field the same as the previous foreign key field was named. Why, you ask? This set of tables is in a working database. I already have numerous forms, reports and queries all of which use that field name, "CustomerKey". If I didn’t preserve it, I'd end up having to refactor all of the interface stuff—not fun.
Here's what the new Foreign Key field looks like in the CustomerAddress table.
 |
Figure 18. New Valid, but Empty, Foreign Key Field and Old, Populated, Foreign Key Field
|
We have two more steps to complete. We’re going to go and get the proper Primary Key values for the Customer Key field from the new Primary Keys in the Customer table. After we do that, we’ll be able to delete the now-superfluous “CustomerKey_Old” field in the CustomerAddress table.
Why Not Join Us for a Good Time?
We need a query to update the new Foreign Key field in the address table. When we create it in Access using the default method, this query will look like this design view in Figure 19. It has a problem in it.
 |
Figure 19. WARNING. Access will try to Create the Join on the Relationship it Knows About
|
Access knows about the relationship between the CustomerKey fields in these two tables, and it will create the join on them for you. That is NOT what we want. Remember, we’re going to go get the “_OldID” value from the Customer table, and join it to the “CustomerKey_OLD” value from the CustomerAddress table. Those are the fields that existed in the pre-migration tables, but they are no longer valid as Primary and Foreign Keys on this side. We'll use them to update our NEW Foreign Key field to the values in the NEW Primary Key field in the Customer table. Change the join to look like this one in the update query we need to update the new Foreign Key field in the Address table.
 |
Figure 20. Join on Previous Primary and Foreign keys in the Update Query
|
This update query will fill our new Foreign Key field (CustomerKey) in the Address table with the proper Primary Key values from the Primary Key field (also called CustomerKey) in the Customer table. I’ve found that tables with a few dozen records update very quickly. If your tables have many hundreds of records, it may be slow. If you have thousands of records, it may even time out the first time you run it. Be patient. It has always worked for me, even on a table with over 7,500 records.
 |
Figure 21. Updated Foreign Key Values in the New Foreign Key Field
|
One more step and we’re done. Now that the new foreign key field is properly updated, the old one can go. Select and delete it. You can do that in the datasheet view. Right mouse-click on the column and select “Delete Field” from the shortcut menu.
 |
Figure 22. Delete the Old Foreign Key Field
|
This may not be 100% rational, but I would NOT delete the “_OldID” field. I would simply hide it in Access for now. It may not be useful again, but I’ve been around long enough to want to hang on to bits of data that I might just need at some point in the future. And you couldn’t get it back if it were gone.
Here’s one last cool thing to think about. You can SEE the relationships between your SharePoint tables in the Relationship Window, just like your Access tables. Of course, you can’t manage the relationships here because that has to be done with the Lookup Fields. But it is very cool to be able to review the relationships in a familiar environment.
 |
Figure 23. SharePoint Table Relationships Viewable in Access Relationship Window
|
At this point, I’ve talked you through the migration of a couple of tables from SQL Server to SharePoint. These tables were linked into an Access database via ODBC, so I was able to move them from Access. However, because they came originally from SQL Server, I had to manage the relationships between them after migrating them to SharePoint. That brought in the complication of having to refactor the Foreign Key fields after the migration. You can follow this same approach in your own efforts to migrate existing Access tables to SharePoint.
Now, we’re ready to look at migrating some much larger tables, in terms of record count. I’ll tell you how I went about that in the next blog post.
