Converting an Access DB to Sharepoint

I’m a big fan of making people more productive by sharing data entry. In my current project, the tool we have for collaboration is Sharepoint 2007.One of our team members is really comfortable with Access development, and built a nice database for tracking the status of our document deliverables. The problem with this is that since Access is a local sort of solution, it doesn’t allow for people to make updates as they work, so we end up spending a lot of time with the database owner updating status.

The solution (in this particular environment) is to utilize Sharepoint lists. Access 2007 has a nice wizard driven approach to building Sharepoint lists, as long as the database is built correctly.

So here are the iterations I had to go through to make this work …

First pass: Diving right in, I loaded up the database to see what it looked like (how many tables, what sort of structure was there, etc). The designer did a good job of making a lot of the fields lookups, and had named all of the reference tables with the prefix of “REF” – so far so good:

So, thinking it would all work fine, I simply start the “Move to Sharepoint” button in the “External Data” tab of the Access Ribbon:

This starts the wizard, which walks you through the process of building the lists on Sharepoint. Once the task is finished, you are left with a new Access database that is linked to the newly created Sharepoint lists.

Looking at these lists I noticed something off: it didn’t appear that any of the reference tables were being used. They were there as linked Sharepoint lists, but when you looked at the data in either SP, or Access, there was no drop-down or choice to be made, they were simply text fields.

So digging a bit, and having a discussion with the developer, I found a few issues: Most of the reference tables weren’t actually setup as relationships in Access, and some of the reference tables didn’t have an ID column (an Autonumber primary key).

The wizard requires the links between tables to be relationships on autonumber primary key fields, in order to build the field as a lookup.

Looking at the base relationships, it turns out that there was only one of the myriad of tables that actually had a relationship:

In order to get the Sharepoint lists to build fields that were lookups to the “REF” tables, I would have to create new fields that linked to the primary keys in those tables. To do that turns out to be relatively simple in this case.

First I open up the design of the main table, and look for the fields that are using lookups (in the case below, Contract_Task):

Then I simply create a lookup field that has the name of the table in question (in this case REF_Contract_Tasks). First step is to insert or append a new column :

Name the new column the same as the table in question:

Choose “Lookup Wizard …” for the Data Type:

Then complete the lookup wizard:

Choose the table you are interested in (in this case REF_Contract_Tasks):

Pick which fields you want to display (typically this will just be a name field, but you can choose whichever fields you think would be helpful):

Choose the order you want things displayed:

Format the columns for lookup display (it’s recommended not to display the ID column):

Choose a name for the field (if you already typed this in, you don’t need to change it):

The wizard now asks you if you’d like to save the changes, which you should do:

Now if I look at the relationships, and hit “show all relationships”, I see the newly created link:

Right click on the relationship line to edit the relationship:

And I typically just check the “Enforce Referential Integrity” box to make sure it is an identifying relationship:

Once that is done, I can either build an update query, or copy the contents of the original row to the new one in datasheet view. For the purposes of this database, there aren’t that many rows, so a simple cut and paste works fine:

Hit ctrl-C or choose “Copy” from the menu:

Select the new row, and paste the data there:

Repeat for all of the other rows in the table that have some query in the lookup tab …

Now in a couple of cases, I got prompted for which field to use to establish the relationship. These are the tables that didn’t have an autonumber primary key. The fix for this is relatively painless (once you discover which tables have the problem).

Just add a new field to the table that has a Data Type of Autonumber, and make it a primary key.

The trick with this is that the primary key won’t likely have the same numbers as the original ID column, and you can’t create an AutoNumber column with data in it. So in order to get the new field updated, the easiest thing to do is create an update query and edit it to use the old join values.

Do query wizard, and choose the columns in question, in this case Deliverables, and REF_Projects. Since you created a relationship with the new REF_Projects field on the Deliverables table, you should end up with something like this:

In theory you should be able to edit the relationship in the GUI by dragging it around and/or right clicking on it. I’ve actually had limited success with this, so I tend to simply add the column I want to update (REF_Projects) to the query, then hit the button on the Design tab of the ribbon that says “Update”:

Then switch to SQL view in order to modify the join properties more easily:

The query so far will be something like:

UPDATE REF_Projects
INNER JOIN Deliverables
ON REF_Projects.newID = Deliverables.REF_Projects

SET Deliverables.REF_Projects = [REF_Projects].[newID];

And you want to update it with the old columns from the lookup query that was in the original table, so in this case it becomes:

UPDATE REF_Projects
INNER JOIN Deliverables
ON REF_Projects.ID = Deliverables.Project_Level

SET Deliverables.REF_Projects = [REF_Projects].[newID];

Then run the query and the values in the two fields should look relatively the same in the datasheet view (since they are now both pointing at the same rows.

Then another one that threw me off was one field that had a different column heading than the field name. When I got to the “Doc_Type” field, I didn’t see it in the Datasheet view. Turns out it had a value in the Caption area of the field definition:

So in the datasheet view it showed as “NDCP Standard”:

This field also has another interesting challenge – the values didn’t all match up with the values in the lookup table (REF_Doc_Type), so when I did my cut-and-paste trick I got the following error:

So looking at the REF_Doc_Type table, I was able to figure out that there is no row that is named “Standard” (likely it had been changed to “Program standard” at some point, and since this column stores the actual value, there is a disconnect).

So in this case, I chose to simply update the current column values to match the one I thought it should be with an update:

After that my cut-and-paste from the “NDCP Standard” field to the “REF_Doc_Type” field went without a flaw.

This same trick needs to be applied to any of the tables that will be worked directly with (typically those that contribute to forms) in order to make sure all of the proper links are built.

Once you have the links built, you want to rename the original table and build a view (in Access this is called a Query) with the same name that has all the joins you need in place. So after you rename the table, just build a simple query with that table in place (I use the wizard and make sure I include all of the fields):