The WorkerThread Blog

we know stuff so you don't have to

Using Access 2007 To Update SharePoint Lists

Posted by workerthread on February 3, 2009

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:

image

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

image

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:

image

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:

image

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:

image

The Get External Data – SharePoint site wizard starts up:

image

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:

image

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

image

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

image

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

image

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:

image

Select Department as the table you want to append to:

image

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

image

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:

image

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:

image

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

image

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

image

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

image

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

image

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

image

And that’s it!

Technorati tags: ,
Advertisements

46 Responses to “Using Access 2007 To Update SharePoint Lists”

  1. […] Using Access 2007 To Update SharePoint Lists (WorkerThreadBlog)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. […]

  2. Hamed said

    Hi Derek,
    Nice tricks.
    So Many Thanks.

  3. Oscar said

    I used your recommendations and worked excellent.

    But I liked to know how can I do to update a field with the property “Allow multiple values” is activated.

    Thanks in advance.

  4. Hi Oscar,

    Do you mean how do you get the unique values out of a “multiple value” list? You could try creating an Access query and use the .Value field in the SELECT. Here is a Microsoft Article which describes this.

    Derek

  5. MSTech said

    How can administrator’s or designer’s remove external (DEAD) datalinks for issues with permissions.

  6. Larry said

    Thanks for the article on how to create lookups from data orgininating from Access. I do not have Access 2007 but rather, Access 2003. Will I be able to accomplish the same thing through Access 2003?

    In Access, I tried using my Contacts table which was performing a Lookup on ContactType to the ContactType table. I then exported the Contacts table to SharePoint (WSS 3.0) and saw that the export did not bring over the ContactType table when I exported the Contacts table. I thought it might do that in that the ContactType column in the Contacts table was performing a lookup to the ContactsType table based on ContactsType.ID. Instead it exported only the number (ID) from the Contacts table to SharePoint.

    Is there no easier way to do this without having to go through all these steps whenever a table lookup occurs?

    Thank you.

    • Hi Larry,

      Well, Access 2003 isn’t so aware of SharePoint lists as 2007 is. But I do believe you can use Get External Data/Link Tables to link to SharePoint lists in 2003, and I guess after that you could try to create the queries I describe above.

      If you just need to use Access to make the list updates I describe as a one-off exercise, I guess you could install the time-limited evaluation version of Office or Access 2007 and do the work from there 🙂 .

      Derek

  7. qctester said

    Thanks for the good information. I have a question that has not been pointed yet I think. Is there a way to verify that Access 2007 is compatible with Sharepoint 2003 because our Sharepoint server is still running the 2003 version. I did some tests and I can’t update the sharepoint list from Access 2007 as proposed. I was wondering if it was user-right problem or just a software compatibility issue.

    Thanks in advance for an answer,

  8. Robert said

    Thanks for a good post, there is so much potential in the SP-Access 2007 integration that we need much more of this type of information.

    I’ve run into a problem that I wonder if you may have seen or heard of before. I have several lists from a MOSS 2007 site as linked tables in an Access 2007 db. I used to be able to open and work with all of them through the Access GUI, but lately one of the lists won’t open, be refreshed or at all participate in any commands from the Access frontend. It works a charm through the SP web interface.

    The error messages I’m getting are

    “Cannot connect to the Sharepoint site ‘site name’. Try again later.”

    and

    “Microsoft Access can’t open the table in Datasheet view.”

    I’ve troubleshot from every angle I can think of to no avail. Any ideas?

    • Hi Robert

      I agree with you, Access 2007 and SharePoint work really well together, and a lot of SharePoint users haven’t exploited it fully.

      Regarding your problem with one SP list, I haven’t had this problem myself although doing a quick web search I can see one or two others reporting it. I guess you have tried deleting the link and re-linking?

      The fact that it was working once and is not now (and that it is only one list) suggests that something has changed somewhere. Maybe something related to structure or permissions on the SP list?

      Derek

  9. Robert said

    Yeah I’ve tried deleting and relinking. I’ve also tried recreating the Access database and table linkage in all possible ways. I’ve tested it on different machines and on different user accounts to no avail. It shouldn’t be a permissions issue as I am able to work with the list freely in the Sharepoint web GUI.

    I have added columns to the list since the point in time when it was working. I’m having a hard time troubleshooting individual columns though as Access will not open the table at all, nor include any single column from the table in a query. I can view and edit all columns in Datasheet view through the Sharepoint web GUI, though, so no clues there.

    Searching the web extensively I have only found a few other people with the same issue and no one with a solution. Would really appreciate any tips or more information from anyone on this issue.

    • Hi Robert

      Clutching at straws here, but I wonder if it’s something specific in the SP list? Have you tried saving the list as a template including content from within SharePoint, creating a new list from the template then seeing if Access can connect to it?

      Also maybe it’s worth trying to connect to the list from something else that uses the SP Web service to get data, to . If you search around there are free downloads available like the Stramit CAML viewer that should be able to connect/query the list. Would be interesting to see if you can any problems with these.

      Derek

      • Robert said

        Thanks for the tips, I’ll give them a go and see how we go. I’ll gladly clutch at any straw at this point.

  10. Robert said

    So my above issue mysteriously disappeared after I had recreated my Access database a few times. Not at all reassuring but I’ll take it…

    Next issue I have run into has to do with lookup columns. One of my list uses about five lookup columns, all connected on ID. When I run update queries on the list, I sometimes get an error saying that a record could not be saved because the value in one of the lookup columns was not valid. Looking closer at the list this is caused by the value zero (0) in lookup columns. It seems that the value of a lookup column is set to zero if the item is edited through the default EditForm.aspx and left blank. Has anyone else run into this and do you know if there’s any way of avoiding it (other than not using the default EditForm.aspx page of course)?

    • Hi again Robert

      Could you make the lookup column required to ensure it isn’t left empty?

      • Robert said

        The thought has crossed my mind, but some of the lookup columns are used exclusively by custom workflows and assigning a value to the columns would indicate that a process has been applied to the list item. I would really want to keep null values unless there is a real value, and this is not a problem most of the time. It is only when I edit an item using the default EditForm.aspx page and leave the lookup field blank that it is set to 0.

    • Brandon said

      Robert, the workaround would be to run update queries replacing the 0’s with Nulls before you run your forms/ other update queries.

      This will require a number of queries because you have to query for every permutation of “0” fields, working down from 5 fields to 1, because if you do them one at a time, it won’t let you save the record.

      Alternatively, you could write a VB module which would go through the table and replace all 0’s with Nulls for any number of columns. Then, just run that code before you open any related forms or run update queries.

  11. Brandon said

    Thanks for the post! I’m having the exact same problem but haven’t diagnosed it until your comment. I can now see that SharePoint (2007) sets the lookup values to “0” if the user chooses “(None)”, which then causes errors in Access 2007. My fields aren’t used by SharePoint workflows or anything, so I should be able to require values and supply a “null” lookup or something, but it’s very annoying. Please post back if you find a fix.

  12. Beth said

    This article helped me SO much. The only thing I found a problem with was if the field you are replacing is the main “title” field, it won’t let you delete it afterwards. I just hid it from the view, but it still shows up when the user clicks “new”. However, they can add through the datasheet without any problem. Any ideas of how to get around this would be appreciated. I have SP Designer, but didn’t want to modify the form if I didn’t have to.

  13. canio said

    I have a question sort of in this area. When I run a query in Access on a Sharepoint Table that has columns in it linked to other Sharepoint Tables does Access give me the Column ID and not the data in the actual column? It is so bizarre?

    Please email if you can I’m so up a creek on this one

  14. Brandon said

    In a SharePoint list X, you can have a value be a “lookup” in another SP list, Y. You can choose what field to look up in Y, so that it is more user friendly (users can see the name of something instead of the ID). However, no matter which field you choose, the ID of Y is stored in table X. Therefore, when you do a query in Access, the lookup field in table X will be the IDs of records in table Y.

  15. donviti said

    got it.

    I think I figured out how to get the ID to stop appearing. We linked the tables individually even though nomrally you wouldn’t need to do so with a regular qry.

    thanks again

  16. Jarexs said

    Hi, can anybody tell me how to link SharePoint List with MS Access 2007 without opening Access environment. I want to do it programmatically through C#/VB.net.

    Your help will very much appreciated. Thank you so much!

    -Jarexs

    • Hi there

      If you are going to use a .Net app to do this you will probably want to communicate with the SharePoint list through a web service. If you do a search online for “update sharepoint list programmatically” or “update sharepoint list web service” you will find lots of examples.

      the Windows SharePoint Services SDK available here also has examples of reading/updating SharePoint lists.

      Derek

      • Jarerx said

        Hi Workerthread, thanks for your reply. I knew about this and unfortunately this doesn’t apply to my current application. As far as I understand, this approach will only allow you to develop inside your SharePoint server environment which is not the requirements. I want to deploy it on client machice e.g. XP/Win7.

        I want my application to be able to send data to Sharepoint list right from the client machine. And I think the easiest way to do this is to link the Sharepoint list to Access 2007, but don’t want the user to open the Access and link them. I want to do it programmatically.

        Thanks

        -Jarexs

        • Hi Jarexs

          No you could definitely develop an app in C# or VB.Net to run on a client PC and communicate with SharePoint through web services. In fact this is pretty much how client apps like Access, Project or InfoPath do it.

          Derek

  17. Jarexs said

    Thanks workerthread. I’ll try to look at it

    -Jarexs

  18. raoul said

    Thanks for the time to read this – maybe someone can point me into a direction.

    My goal is to create a document populated with entries from a sharepoint list. That in itself is easy. Problem is, that some of the data needed are aggregated and I had to this using access placed on a shared directory. The queries in there do the trick and the data is created without having access installed on the client. It is for instance accessible through the “mergefields” in word.

    What I would like to do now is somehow to push the access query back into a static sharepoint list. Having a sharepoint list linked to access does not do the trick, since the aggreggation is not supported in sharepoint.

    Think as an example of creating bills where you enter the amount in a sharepoint list, but the calculation for instace what rates to take with what kind of client is done purely in access. These results need then to be accessible in sharepoint again.

    Thanks for your help

  19. Hi Raoul

    Once you have aggregated the data in Access, If you want to put it back into SharePoint can’t you use an “append” query to do that?

  20. KonRaj said

    I hope you can help. i have an access db linked to sharepoint – everything is working as expected apart from my queries.

    I have a really simple query setup in access to select data from 2 tables .I then exported the query to an access table, then linked to a sharepoint list so i can display the results of the query in a list on sharepoint, this again works well.

    The trouble is if data in the 2 tables that have been queried is updated – my query does not automatically update in sharepoint.

    • Hi,

      Not sure I follow. is the data that’s not automatically updating in a SharePoint list which is linked to an Access DB? or have you exported SharePoint data to an Access table via a create table query? If the data has been exported to Access then you will need to re-load that back into SharePoint somehow.

      If it’s a linked table and Access is connected to SharePoint then should be able to update, unless you’ve set the database to work offline.

      • KonRaj said

        Hi,

        I have two tables in access and using a simple query to pull data from the tables. I am exporting this query results by clicking on export to sharepoint which is creating a SharePoint linked table in the access.

        When data in two tables is updated and when i run a query to update the SharePoint list, the link between the sharepoint and table is getting removed.

        I want the SharePoint list to get updated automatically when ever i run a query.

  21. KonRaj

    If the linked SharePoint list is editable in Access, and the query you are running is an “update” query and doesn’t return errors when run, can’t understand why there would be a problem. When you say the link between SharePoint and the table is getting removed, does it just disappear or become invalid?

  22. Chris said

    Hi…great article and comments. Wondering if I can have multiple Access databases (all with same fields, just different desktop users) populating and linking to one SP List, as if the Access users were front end to the SP master database. The Access DBs stay in synch with their respective records in the master SP list. Anyone?

    As a new user to this integration, I’m perplexed by the value of the interface between SP and Access if it’s always a one-to-one relationship? I’m hoping I misunderstand the upshot here. Thanks.

    • Hi Chris,

      An Access front-end will consider the SharePoint lists to be “linked tables”. If you have multiple Access DBs all connected to the same list(s) each user should be able to update providing they have appropriate permissions. Access 2007 and 2010 also let you take the data offline and re-sync when you are back online. In this situation there may be some collisions if the list data has changed since you were last connected.

      If you are looking at how to create hybrid apps I would recommend this Access 2010 Channel 9 video featuring Dick Moffat.

  23. bakshish kamra said

    ho do I link multiple tables in a single access file

  24. I’ve written a review of Simego Data Synchronisation Studio here, might be of interest if you are reading this post.

    Derek

  25. Julian said

    I’m wondering if anyone has seen this problem before. When I link my lists certain tables are linking the inbuilt Created By and Modified By fields as “Text” instead of “Number”. The result when I open the table/list in Access is the error: “The setting you entered isn’t valid for this property.” I’ve not been able to resolve this yet by relinking/deleting etc.

  26. Siva said

    I have a sharepoint list which im trying to import it into an access database where i have written a query to select the required fields.Since im importing the data using linked table, whenever i update the sharepoint list i can see the updated database. But my problem is everytime i try to change the name of a column in the sharepoint list i also need to change my ms access query accordingly. Is there a way to autoupdate the query so that whenever i change the table column, the query also canges.
    Thank you,
    Siva

  27. Tom masters said

    I have a SharePoint list linked to an Access 2007 database. I have been updating the list with a daily append of 400 or so records for over a year (the list is archived monthly). This process has worked fine for an entire year and now out of the blue I am getting an error message when doing this append. It appends about the first 7 to 20 records and then aborts with the following message “Cannot connect to Sharepoint site “. Try again later. However I am able to open and query the list in the same access database. I have checked disk space and culled the list from 14000 records to 2000 to no avail. Any help would be gratefull. Tom

  28. Shubhangi said

    Hi,
    Iam having Access 2007 and Sp2010. I am having access DB stored in my Sharepoitn Doc Lib. But to work on it I need to store it locally on my machine. So all my changes in the DB are local. How can I change anything globally.??
    Please suggest…

Sorry, the comment form is closed at this time.

 
%d bloggers like this: