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.
- Redefining Relationships between exported SharePoint tables.
- Indexing fields in SharePoint tables with more than 5,000 records.
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
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.
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|
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|
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|
|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|