Disappearing Primary Keys Notwithstanding, You Can Migrate to SharePoint

Recently, a poster at Utter Access asked for help moving Access tables from an existing database to SharePoint tables (or more accurately, to SharePoint lists). His goal was to link to those tables in a Hybrid Access database. One of the issues raised was the supposed limit on the number of records you can export. Fortunately, a couple of really knowledgeable people, Albert Kallal and Bonnie Hicks, stepped in and helped address the poster’s questions.

Reading the question and answers prompted me to see if I could move one of my personal databases to Office 365 in order confirm how it works. In doing so, I came up with a few "gotchas". These are especially problematic when exporting existing tables with large numbers of records already in them. In this case, large means only a few thousand records. I haven’t yet tried to upload a really large table, i.e., one with tens of thousands of records. When I do, I'll come back and tell you about that.

The gotcha I want to talk about in this blog post is this :

• Dealing with SharePoint generated Primary Keys which replace existing Primary Keys in exported SharePoint tables.

In later blog posts, I'll address two other related tasks you have to complete when exporting either Local Access tables or SQL Server Linked tables to SharePoint.

• Redefining Relationships between exported SharePoint tables.
• Indexing fields in SharePoint tables with more than 5,000 records.

Before You See It, After you Don’t
When you export an existing table to SharePoint, the table will end up with a new, different, Primary Key field for that table on SharePoint. I assume that you, like me, usually or always define Primary Keys for your tables (surrogate or natural). When exporting those tables to SharePoint, such existing Primary Keys are not honored. Instead, SharePoint creates its own Primary Keys for tables. SharePoint does this automatically and without notification to you. If you are not aware it has happened, it can be confusing when you look at the table (list) later.


The Way We Were. NOT!
In SharePoint the AutoNumber field is the only option for Primary Keys. That's generally not a problem for me because, like most Access developers, I almost always use AutoNumber Primary Keys (or Identity Fields in SQL Server) anyway. However, SharePoint will not honor existing Primary Keys in the table. Instead, SharePoint

• renames the existing Primary Key field to _OldID and
• creates a NEW ID field, using the AutoNumber datatype

On the surface, that seems reasonable enough because the SharePoint list has to conform to SharePoint configuration rules, but there some serious potential problems with this. By default, new ID's in new tables are named simply "ID". However, when you export an existing table to SharePoint, things are a bit different. First, the existing Primary Key field is renamed _OldID. Then, SharePoint creates a new Primary Key field to replace it. The name of that new field is the same as the one previously used in the table. However, the VALUES in that new Primary Key field may or may not line up with the values in the now-defunct field called _OldID. I’ll explain why that happens a bit later. First, let’s talk a look at an example of exporting an existing table to SharePoint so you can see how it works.

Let’s say your existing Primary Key field is called “CustomerKey”. When you export the table to SharePoint, that existing CustomerKey field becomes “_OldID” and the new, SharePoint generated, Primary Key field is called “CustomerKey”. So, when you look at that SharePoint list linked into your Access database, you see what LOOKS like the CustomerKey field, which is duly designated as the Primary Key for that table.


Here’s what that the process involves, starting with a look at the table before it is sent to SharePoint.

Note the gap in the CustomerKey values in this table. They go from 1 through 6, then skip to 22.

Figure 1. SQL Server Table ready to be Exported to SharePoint

A Brief Sidetrack, if you don’t mind

We’ll get back to the issue of Primary Keys in a bit, but first I want to note a couple of other factors about this table which result from its origins. It may not be clear from the screenshot, but in this example I am migrating SQL Server tables—which are OBDC linked into my Access database—directly to SharePoint. For that reason, you’ll see at least one difference from standard Access tables. tblCustomer_SPBlog has a field called “AccessTS.” It’s a Timestamp field in SQL Server. Timestamp fields are commonly used in SQL Server tables linked to Access. This may be more than you want to know at this point, but here’s what MSDN says about timestamps. “Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp fields are used typically as a mechanism for version-stamping table rows.” Access often needs timestamp fields to update SQL Server linked tables correctly. The point here is that timestamp fields don’t work in SharePoint. Access will export the field, but not the data, making the field useless after export.

This table also has four other fields which we do not need in SharePoint. These fields are: 

• CreatedBy
• CreateDate
• UpdatedBy
• UpdateDate

SharePoint lists already have similar fields, and we don’t need to duplicate them with our own home-grown fields.

I won’t delete any of these five fields, now, of course, because they are still needed on the SQL Server side where they now live. We’ll do later it in the exported table, though.

Welcome Back
So, I’m ready to export this table to an existing SharePoint site now. The next screenshot shows you how to do that.


Figure 2. Ribbon Option to Export a Table to SharePoint

This option allows us to export one table at a time. That’s what we want to do in this case because we need to keep track of the changes to the exported tables as we go. We could export them in bulk, using a different method. However, in my first attempt at doing so, I found the bulk export method was a bit confusing. Every table needs some fixing up after it’s exported, and I wasn’t always sure which tables I had fixed up, and which ones I hadn’t. So, I fell back to the approach of doing so one at a time instead. This method also doesn’t automatically relink the exported list back into Access, which is a minor hurdle.


Of course, if you have a relatively small number of tables to handle, you might find the bulk export method more appropriate because it would be quicker and complete the linking for you. Here’s what that option would look like on the ribbon.


Figure 3. Moving All Tables to SharePoint



However, right now we're interested in moving a single table, though. When you click the Icon to export a table to SharePoint, the following wizard opens.

Figure 4. Select a Destination

If you have a choice among multiple SharePoint sites, the listbox will show you those sites you’ve previously used. Selecting one displays the site name in the textbox below the list.


Name the table as you want it to appear on SharePoint. This may or may not be the same as the local table name. It’s up to you.

You can also have SharePoint open the new table after export. I generally don’t when I’m moving a lot of tables, one after the other. It just takes time and doesn’t give me any additional, useful, information. However, the next screenshot shows what that might look like.

Figure 5. Exported List Appears on SharePoint

So, once the table has been exported to SharePoint, you can link to it from your Access database. Here’s how you do that from the Ribbon.



Figure 6. Link to a SharePoint List

 The Wizard that opens when you click on SharePoint List looks like this.


Figure 7. Select a Source for Linked Lists

Selecting a source and clicking “Next” takes you to the next step in the wizard.

Figure 8. Select a List to Link

As you can see in this screenshot, I’ve already moved all of the tables in this database to my SharePoint site and then linked to them from Access. I have only one new table left to link, the one we’re using in this blog example.

Linking to it is simply a matter of clicking the checkbox next to it and then clicking the “OK” button.

Figure 9. Newly Linked List in the Navigation Pane

I’d wager it took me longer to describe that process than it will take you to export a table  to SharePoint and create a link to it, especially when you've had a little practice doing it.

Before You See It, After you Don’t- Redux

So, this post started out describing the problem with Primary Keys being bumped out of the way by SharePoint. Here’s how that looks in an actual table. The first six CustomerKey values in this 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. We’ll take advantage of that fact when we have to rebuild the Foreign Keys that related to these CustomerKeys. In other words, it’s a pain to have to handle this “behind-the-scenes’ change to your Primary Keys, but there is a way out of it. I’ll tell you how to do that next time.

Figure 10. New Primary Keys Don't Match Previous Primary Keys But Do Match _OldID