Migrating Larger Tables to SharePoint

This is the third, and possibly the last, in a series of blog posts on my experiences migrating data from a working Access/SQL Server database to SharePoint, where it now serves me daily. It's a "hybrid" database, in the sense that the Front End is the original Access accdb, complete with all of the original VBA intact, while the Back End lives on SharePoint.

A couple of posts back I introduced you to this small project. In that initial post, I told you about the problem of "disappearing Primary Keys". To be honest, of course, Primary Keys don't really disappear. However, SharePoint does a really good job both of disguising them and hiding them from you. Unless you know how that happens and why, you might be as confused as I was at first. You do know all about that now, of course, because you've read my first blog post on the topic. The major consequence of that trick with Primary Keys is that it causes any relationships you've defined on those tables to go all squirrelly. "Squirrelly" is a software term that means, more or less, your relationships go flying off in random directions without much guidance or purpose.

In the second blog post in this series, I told you how I went about patching up those lost relationships between existing tables.

All of the tables I used as demos in those first two phases of the data migration held small numbers of records--no more than a few hundred.  However, if you are migrating a lot of data to a new SharePoint database, as I was, you probably have tables holding many thousands of records. That's a whole different story. When your recordsets go over a few hundred rows, you'll definitely want to adopt a different strategy, one based on advanced planning and some staging steps to get the tables and data ready to make the journey.

A Journey of Seven Thousand Records

As you may recall from the two previous posts, the data I migrated in this project came from a decade-old database, most recently with a SQL Server back end. I copied it all to my Office 365 Hosted SharePoint site. I'll use the term "migrate" in the rest of this blog to describe that process, in which I copied all of the records from one table to the other. Of course, the Access Front End remains as before; it's just connected to a different set of tables. The migration went well, once I figured out how to handle the little gotchas that cropped up. I've been using this SharePoint version for several weeks and couldn't be happier.

One of the most significant problems appeared when I was trying to upload the larger tables to SharePoint. These tables hold several thousand records each. The two largest tables, daily work details and invoice details, held over 7,500 records and over 5,700 records respectively. (It turns out I don't bill for every hour I record in this database. Go figure.)

Et Tu, Bruto

I found that I couldn't safely move more than a few hundred records at a time if I tried to do it the "brute force" way. "Brute force" would be clicking on a table, pointing it at a SharePoint site, and clicking the button to copy the data. The big culprit, I think, was that SharePoint was trying to index fields all during the migration. A lot of work was going on behind the scenes, and SharePoint and Access simply couldn't keep up. SharePoint couldn't import thousands of records and manage the indexes and referential integrity on the Foreign Key fields in those records efficiently in a single go.

Eventually, I realized that a more disciplined, planned and phased approach was going to work out better, with fewer cuss words and less frequent "do overs". The overall time it took move the data from linked Access tables to SharePoint wasn't all that much shorter because of the preparation work that went into staging the tables for migration. Yet, I was far less frazzled when it was over. In the following paragraphs, I'll describe the method I worked out to "stage" my larger tables for migration.

He that filches from me my good name robs me of that which not enriches him and makes me poor indeed

Here's the table I selected as a demo for you. I have to make a confession here, at the risk of getting unduly embarrassed. As you can see, the name of this table is "tblWorkWork". I don't know what I was thinking that day back in 2001 or 2002 when I first named this sucker "WorkWork", instead of "WorkDetail". I still don't know. As embarrassing as it is, though, it's always seemed like too much trouble to go back and rename it, along with all of the references to it buried in the bowels of the database. Maybe that'll be another project after this one, but probably not. It's tblWorkWork and that's that. I can live with it; and have been for 10 years now. Feel free to snicker if you'd like, though.  

 

 Figure 1. Source table with 7,593 Records to Migrate to SharePoint


  The first step in a planned migration is to create a staging query, based on the table to be migrated. It'll look like this one in Figure 2. It has several advantages over the previous "Brute Force" approach.


 Figure 2. First Staging Query Selecting Fields to be Created

  First, using a select query on the source table allows us to include only the fields we need to establish in the destination table. As you recall from my previous post, one of the by products of a "brute force" approach was that all of the SQL Server fields in the source table went along for the ride, even though we didn't need or want them in the final results 

Second, using a select query allows us to apply a filter to prevent any records from being migrated yet. I applied the criteria "WHERE 1=0" to get that result. In other words, a key element in this staging strategy is that, when we first create a SharePoint list from this table, we only establish the fields in it, no data. That frees us from worrying about which records might or might not successfully get migrated. Nothing is more frustrating that trying to sort out a partial transfer after a failure half way through.

It's that old Transactional Integrity thing. You know, everything gets committed or nothing gets committed.

My Relationships? It's Not Complicated, Really

Third, using a select query allows us to pre-alias the destination fields for the "OLD" foreign keys in the table. As you recall from the previous posts, one of the things we have to do with the tables after the data is migrated is to reestablish the Foreign Keys using the SharePoint approved "Lookup Field" method. Getting the proper fields pre-staged in this query makes that later step a little less of a hassle. Figure 2 shows three "_old" fields. One will be the Old Primary Key field (WorkWorkKey), and two will be the Old Foreign Key fields.

As you can see in Figure 3, the query presents us a "shell" or "template" of the fields we want to send over to SharePoint, without data complications.

 Figure 3. First Staging Query Datasheet Returns No Records

Exporting this query to a SharePoint Site is very much like sending a table would be. Here's a refresher of the Export dialog you saw last time.


 Figure 4. Export the Template Query to SharePoint

 I selected the destination SharePoint site as I did before with the full table. This time, though, I want to rename the destination table because the query name is meaningless. The exact name is important, but not critical. You can rename it later, on SharePoint, if you need to do so. As you can see, I made this one quite unique so it will be readily identifiable in later steps.



 Figure 5. Select the SharePoint Site and Name the table

 Because we are creating a list, but not migrating any records to it, the creation should be quicker than before, when we were migrating records in this step. If you left the checkbox "Open the list when finished" checked, SharePoint will show you the resulting, empty table. See Figure 6. 

 Figure 6. New SharePoint List On the Site

Sharp eyes comparing this screenshot to the one in my previous post might notice that, for this post, I used my AccessHosting.com Site, instead of my Office 365 Site. For a lot of reasons, I really prefer the Access Hosting experience for Access Web Database. For one thing, they support Online Reports better than O365 does. Nonetheless, for the purposes of this blog, the migration process is the same on both. I also migrated the same table to Office 365, just to confirm that it was going to be the same.

Let me adjust that seat belt for you...

The next step in the staging process was to fix up this SharePoint list so it's all nice and tidy -- and safe -- for when I start pushing records into it.

Clicking on the list editing icons on the Ribbon in SharePoint takes us to the online screens where we can do that. First, I clicked on "List" under the List Tools tab, and then selected "List Settings".  Those icons are highlighted in Figure 7.



 Figure 7. Edit a SharePoint List



Figure 8 shows you the screen where you'll make changes to the list. I'm intent on fixing up only a few things at the moment, so I won't walk through the whole screen.

 Figure 8. SharePoint List Editing Screen

The first change I had to make to this new SharePoint list in order for it to be usable as a destination for thousands of records was to create the relationship fields to relate this table to other relevant lists, or tables, in the database. The ones we're going to need are "WorkKey" and "WorkRateKey". Again, though you can see THREE "_old" fields in Figure 8, only two are Foreign Keys, the other one, "WorkWorkKey_Old" will contain the OLD Primary Key values from the source table.

And now you can see a fourth benefit of migrating tables this way. Apparently SharePoint didn't recognize that field as a Primary Key when it came through the query instead of directly from a table. Therefore, SharePoint didn't bump it off into the "_OldID" field and hide it from us. As far as SharePoint knows, I guess, it's just another field.

To us, that means we can fill it with the Old Primary Key values from the source table, and, later, if we need to, we can use it to update "downstream" relationships from this table. You saw that in the previous blog post. I'm not going to show it to you  again in this blog, but that "downstream" table would be the "InvoiceDetail" table where I record each Work detail ("WorkWork") in a detail line on an invoice. If that last bit is not 100% clear, feel free to ping me for more details ( pun intended). I'll be happy to chat about what I'm doing here.

The link we need on this screen is highlighted in Figure 8, down near the bottom of the screen. It's called "Create Column". Clicking it takes us to a screen where we can define a brand new Lookup Field to serve as the Foreign Key.




 Figure 9. Create a Lookup Field for a Foreign Key

  We'll need two of these Lookup Fields, but if I show you one; you could figure out how to create the other one the same way. I named this Field the same as the OLD foreign key was in the source table. Don't forget, these tables need to take over the role of the old SQL Server tables in the original Access FE when we're done, so keeping the names straight is crucial to avoid refactoring the interface. This field needs to be a Lookup Field to support our purposes for it.

I split the screenshot into two halves (Figure 9 above and Figure 10 below) so you can see them better.



 Figure 10. Name and Define a Lookup Field for the Foreign Key (Lower Screen)

 You know, it occurs to me (and I hope it also occurs to you) that, although this interface looks very different from the Access Lookup Field Wizard we saw in the previous blog, it involves the same elements needed to do the job:

• selecting a related table and key field
• enforcing Referential Integrity on the lookup and so on.

The last step is to click on the "OK" button to save the new field.

Do You Want an Index With That?

 Figure 11. Creating Indexes

The answer, of course, is "Yes", you very much want that index. Indexes are the key to getting this table to handle large numbers of records smoothly and efficiently. If you need more background on the importance of indexes in tables, I suggest you do a Bingoogle search and read whatever turns up. Indexes are important in any database, and that goes for SharePoint Lists as well. In fact, much of the motivation behind what I'm doing here is to make sure that Relationship Field is predefined and indexed against the coming of the records in the near future.

I won't show you how I added the other Lookup field to serve as the new Foreign Key for WorkRate, it's just like this one, except for using a different name, different related table and different field name.

Now I'm going to show you how I manually added a couple of additional indexes. Take a look at Figure 12. Down at the bottom of the screen. There's a link to Indexed columns (or fields) in this list. That's where you go to add new indexes.

 

 Figure 12. Adding a New Index

Clicking on the link takes us to a screen where we can define indexes for non-key fields. I added two indexes, but I'll only show how I did the first one, on the WorkDate field. You can figure it out from there. 



 Figure 13. Adding WorkDate Index

  Select a field from the dropdown on the screen--I used "WorkDate" for this example--and click "Create" to make it so. That takes you back to the Index screen where all of the indexed fields are displayed.

 Figure 13. New Indexes Are Added to WorkDate and WorkEffort

By the way, this Indexes screen doesn't show you the index on the Primary Key field, but it's there. You can only see the non-Primary Key indexes, which include those on Foreign Key fields, of course, and on the fields to which you add indexes manually.

Warning Index Robinson

This may seem a bit inconsistent, coming on the heels of telling you that indexes are very important and showing you how to create them, but I'm also going to warn you against getting all index-happy in SharePoint lists. For reasons beyond the scope of this blog discussion (it has to do with the architecture of SharePoint lists), we don't want to burden the list with too much indexing activity. Moderation in all things is always good advice. Foreign Key fields, like "WorkKey", for sure need them. Fields on which you might frequently filter or sort a list are also possible candidates for indexing. The thing is, once you've indexed a table and added a ton of records to it, most SharePoint installations won't let you change, add or delete any existing indexes. For example, on Office 365, that happens when you hit 5,000 records in a list. AccessHosting is a good deal more optimistic, but it still has a limit. Tread lightly here. Index the fields you must index (Primary and Foreign Keys are indexed by default), and judiciously index other fields if you really expect to hit them heavily in the interface. There's no going back, though, if you get it wrong and need to remove an index. You might end up starting over. Not unthinkable, but a PITA nonetheless.

Now it's time to head back on over to the Access side where we can link this new, empty SharePoint list back into our Front End database. Once we've done that, we'll be ready to pour records into it.

Open the Access accdb to which this list will be linked, and select the "More- SharePoint List"  option on the "Import & Link" section of the "External Data" tab on the ribbon. See Figure 14.



 Figure 14. Go Get a SharePoint List

 If you follow the same steps as I showed you in the previous post, you'll soon have a linked SharePoint table that looks like Figure 15. However, unlike the tables we looked at in the previous blog post, though, this one has no records in it yet. For our purposes now, that is a good thing.


 Figure 15. Empty SharePoint List ready to Accept Records

On the Access side you can now see the new Primary Key field that SharePoint provided when it created this list. It's called "ID", which is the default for all new Primary Key fields. Remember, we used the staging query to create this list, not the table itself. That query disguised the old Primary Key from SharePoint. I like to think of it as a bit of revenge for the trick where SharePoint bumps off our Primary Keys and calls them "_OldID". Well, yes, that is a bit silly, isn't it? SharePoint doesn't REALLY care one way or the other. It makes me feel good to think about it that way, though.

We'll take care of naming problem by renaming this New Primary Key to the same name the OLD Primary Key had. Again, the reason for doing that is to preserve the existing architecture of the Access Front End in which it will be used. All those forms and queries and reports expect to find a Primary Key called "WorkWorkKey" in this table. Don't disappoint them.

Also, keep in mind that I still have the two OLD Foreign Key fields as well as the two NEW foreign Key fields. Well, actually it's two NEW Foreign Key fields. The other two are fields that will serve as temporary placeholders for the Old Foreign Keys we'll need to supply in order to re-align the records we add to this table in the next step. Refer back to my previous post if that previous sentence sounded like complete gibberish to you.

Aren't We There Yet?

Almost there now. We need a new query to grab the records from the old source table and past them on to the new SharePoint table. You can see that query in Figure 16.



 Figure 16. Append Query to Migrate Records from Source to SharePoint

 Pay special attention to the destination fields illustrated in Figure 16. You want the OLD Foreign Keys from the source table to go into the OLD Foreign Key fields in the destination tables. The names are not the same in the two tables, but you shouldn't be confused by that, right? Especially if you've been paying attention all along. In the next step, we'll go get the NEW Foreign Keys.
 Take a look at the datasheet view of the records in this query, ready to be migrated on over to SharePoint.



 Figure 17. Datasheet View of Source Table Records In Append Query

As I said earlier, this table started out with almost 7,600 records. Because I took the time to stage and prepare our destination table in SharePoint, I've been able migrate that entire recordset in a single shot. It took almost two minutes to move them, but the append completed smoothly each time I re-ran it.

Snug as a Bug in a Rug



 Figure 18. 7,593 New Records Nestled Snugly in SharePoint List

There they are, all 7,600 records in their destination table on SharePoint. I've repeated this same migration several times now, on both Access Hosting and Office 365. As long as the destination table is properly staged the way I showed you, with a Primary Key and Lookup Fields for Relationships defined and ready, Access and SharePoint can handle the migration without a glitch. I've not tested recordsets larger than this one. I strongly suspect I'll eventually find an upper limit on the number of records I can move safely. And I'm also sure having a fast Internet connection helps. Nonetheless, I'm pretty pleased with the results so far.

I have not forgotten. There are a couple of things left to do. The Foreign Key fields in this table still need to be updated. I showed you how I did that in my previous blog post. The procedure here is the same. In a query, join the two tables (tblwork and tblworkwork, for example) joining on the OLD primary and foreign key fields; that would be "_OldID" in tblWork and "WorkKeyOld" in this table. Update the NEW foreign key in tblWorkWork to the NEW Primary Key value from tblWork. Then you can delete the old foreign Key fields which are no longer needed.

It's a Fine Time to Be Here Irene

That's how I did it. It took a couple of tries to get it right, but in the end, I'm confident I can migrate data from any source table in Access (or SQL Server) to SharePoint, and do it quite efficiently.

You can too. Go on out there and migrate your old Access databases to SharePoint.

Patching Up Relationships On SharePoint Migrations

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.

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