Using Access 2007 To Update SharePoint Lists

Someone asked a question on the UK SharePoint User Group Forums about moving Access data to SharePoint lists, so I thought I would illustrate some of the Access 2007 features you can use with SharePoint.  Before starting, please note – SharePoint is not a relational database system, so don’t try to use it for full-blown relational database applications!!!  Having said that, it does work well for many smaller list-management applications for which you might have used Access or Excel, and I guess it’s possible (pure speculation here) that by the time we start hearing more about SharePoint 14 and Office 14, SharePoint and Access may be getting even closer together.

So, lets look at a SharePoint scenario:

I have a SharePoint list, in this case for contacts, and it includes a “Department” column.  This list may have been imported from Excel or Access, and the department column is currently a single line of text:

So I create a separate custom Department list with a Title column like so:

But at this stage, it’s not possible to edit the Contacts Department column from text to lookup, as you will see if you try to edit the Column properties:

So instead, I am going to create a new Department column of type Lookup, and use Access to do the updating work.  First, we RENAME the existing department as OldDepartment, then create a new column of type Lookup to store the lookup values from the Department list:

Now at the moment the Department list doesn’t contain any values, so we’ll also populate that list from a set of unique departments in the contact list.  So fire up Access 2007 and create a new blank database.   Next we link the contacts and department lists from SharePoint.  Do this by selecting the External Data tab and clicking on SharePoint list:

The Get External Data – SharePoint site wizard starts up:

Enter the url of the site containing the Contacts and Department lists, and be sure to select “Link to the data source by creating a linked table” option.  Then check the lists you want to link to and click OK:

Now we’ll populate the Departments list.  from the Create tab in Access select “Query Design”:

Add the Contacts list to the Query pane and drag OldDepartment into the grid:

We want a unique list of departments, so open the query property sheet, and set Unique Values to “Yes”:

This will give us our unique list of departments, and we want to append these to our empty Department list, so change the query type to Append:

Select Department as the table you want to append to:

Then select Title as the field you want to append to:

Now run the query.  Access will tell you how many records will be appended, and warn that the action cannot be undone.  Once it runs, you will have a unique set of departments:

Now we can update our department lookup column in Contacts.  Under the covers, the lookup will be stored as a number in the Contact list, and match to the ID column of a row in the Department list.  So, in effect, we want to create a query which says “look up the Department ID by matching Contacts oldDepartment with Title in Department, and add the Looked up Department ID to the Department column in Contacts”.  So we create a new Update query to do this.  If we create a query from Contacts and department, it will default to joining Contacts.Department to Department.ID:

But we don’t want this, so delete the join, and instead drag Department.Title to join to Contacts.OldCompany like so:

We need to change the query type to an Update Query:

And we will update the Contacts.Department to the value of matching Department ID:

Now we can run the query, and when it completes, our lookup values will be updated:

We can now delete the OldDepartment column from our SharePoint list, and we will have available Department values in the correct lookup column:

And that’s it!

Technorati tags: Access 2007, SharePoint

---

Possibly related posts: (automatically generated)

• SharePoint 2010 – Out of NDA and All Over Twitter
• Top 10 things (that excite me) from the SharePoint Conference so far…
• Lookup Fields to the Same List – Defined as a Site Column
• SharePoint Lookup Column Based on a Calculated Column Woes