How do I use an Access query to update a field in a SharePoint calendar?

Question

I have a SharePoint 2016 calendar with 3 multi-value lookup fields (Org L1, Org L2 and Org L3) which need to be set based on a value in a multi-value choice field (Project). I'm trying to use Microsoft Access 2021 to do what I thought would be easy updates.

I had originally imported a SharePoint list rather than linking to the actual SharePoint list because I was afraid to make changes but this didn't retain the relationships. So I linked to my SharePoint list and the Org tables relationships are there.

I've read multiple times about display values and bound values. The bound values correspond to the ID fields.

I'm finding conflicting information on the Append query and the Update query. In an Access 2007 article, "you can insert a single value into a multivalued field by using an Insert query. But I've also seen statements, that an append query is used to add new records only. It does not update existing records or fields.

In the Access 2007 article, you can use an Update query to change the values in existing records. In my scenario, I need to update the OrgL1-L3 fields which are blank based on the value in Project.

I have done Update queries with OrgL1.Value and thought I would use the ID number but it doesn't seem to work that way. Neither does Project.Value. The query doesn't work on ID number but rather text. I read that Access has hidden tables that retain each value of the Multi-value field on a separate row and then brings it back together to show in SharePoint and Access.

My Select query on Project.Value where I put in "Like AuthA*", works. But when I change the query to Update and in Org L1 place either the ID number value or the Text, I get no records.

Thanks in advance.

Answer 1

Hi @Kandace Rowe,

It sounds like you're trying to update multi-value lookup fields in a SharePoint list using Microsoft Access. Here's a guide on how you can achieve this:

1. Link SharePoint List to Access:
   • Make sure your SharePoint list is linked to Access, not just imported. This ensures that Access maintains the connection with SharePoint and updates can be synced.
2. Understand Lookup Fields:
   • In SharePoint, lookup fields store both a display value (what's shown to users) and a bound value (usually an ID). When working with lookup fields in Access, you typically manipulate the bound values.
3. Update Query:
   • You're on the right track with using an Update query in Access. Here's how you can set it up:
   • Open a new query in Access in design view.
   • Select your SharePoint list as the data source.
   • Choose the fields you want to update (Org L1, Org L2, Org L3).
   • Set the criteria for the Update query. You should be updating the bound values based on the Project field.
4. Specify Bound Values:
   • When updating lookup fields, you need to provide the ID of the item you want to link to, not the display value. Ensure you're updating with the correct IDs corresponding to the values you want to set.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1 commentShow comments for this answerReport a concern