It's all about the data.

Wednesday, December 21, 2011

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

4 comments:

Grover Park George said...

To the person who posted this comment:

Blogger screwed up. I tried to authorize this and went into never-never land.
============
I'm trying to "move" an Access db to SharePoint and have no problem with the process of the database, but my front end Access form is still pointing to the mdb files on my share. I can't seem to figure out how to get the form to point to the SharePoint lists as it only seems to allow for mdb files. I've done all the "linking" that I can find but using the form still only updates the database files within the "Linked Table Manager." What am I missing or is this a limitation? -Jer

Grover Park George said...

Blog comments are behaving badly for me lately. Sorry.

So, to the person who posted this comment, which is really more of a question, I think you'll find the answer in the second post in this series, where I describe linking to SharePoint tables. Once you've done that, and cleaned up the relationships, you can removed the linked Access tables from your database, if you want to do that.

Jer said...

Thanks for re-posting my question. I'm not sure the other post answers my question, but it did explain more to me that I needed to understand. I'm looking to use an Access form with the data residing (getting written) to SharePoint instead of the same place it has been getting written. There is validation in the form that looks to the database files to alter the options for the remaining fields of the form. That "front end" form can't seem to be pointed to SharePoint since it's looking for .mdb files. AM I on a dead end street?

Grover Park George said...

So, I think I finally sorted out the problem I was having with comments on the blog.

I think your question needs a bit more detail to fully understand it, but it does seem to me that your question is in two parts.

If you migrate data from Access tables to SharePoint lists, then link back to those SharePoint lists, you can safely delete the Access tables (or rather export them out into an archive file). From that point forward Acces just treats those list/tables the same as it would have done the original tables. Of course, here I'm assuming you sort out the Primary/Foreign Key shuffle.

The other part of your question seems to imply that your code actually tries to connect to a second mdb to work with data?

"There is validation in the form that looks to the database files to alter the options for the remaining fields of the form."

If so, that will definitely have to change. However, you need to explain what you intend to have happen here in order to offer you any suggestions. Specifically, what does it mean to say, "alter the options for the remaining fields of the form"? What does that entail?