Update SharePoint multi choice field programmatically

Question

Update SharePoint multi choice field programmatically

Tom Weir (CONTSBW) 40

Need help with updating a multi-choice field in a SharePoint List using VBA. Key points:

I'm using an ADODB.connection object to update a SharePoint list. I am successful opening the connection, building the necessary SQL Update command and running connection.execute.
All fields of interest update successfully except the multiple-choice fields.
The current format of the update command looks like
SQL= "UPDATE [" & Name & "] SET " & values$ & " WHERE [ID]=' " & Key$ & "';"
values$ is in the form [Field1]='value1', [Field2]='value2', etc.
For the multi-choice field, the format looks like [MultichoiceField] = ';#Item1;#Item2;'.
Connection = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=No;DATABASE=" & Path$ & ";LIST=" & Name & ";"

Note: This is the same format that I get the data when I download those fields using the same ADODB.connection. Only difference is that for the download, I'm opening a recordset and using the recorset.getrows function to dump the SharePoint list data to a variable for processing.

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Varsha Dundigalla(INFOSYS LIMITED) 3,725 Microsoft External Staff

Thank you for reaching out.

It looks like you’re running into a common challenge when updating SharePoint multi-choice fields through VBA and ADO. The issue happens because these fields are stored in a special internal format in SharePoint. The “;#” format that you see when reading data works for displaying it, but it doesn’t work the same way when writing back.

Here are a few approaches you can try:

Use a Recordset to update instead of a SQL command

Rather than using a direct SQL UPDATE statement, it’s more reliable to open the list as a recordset and update the values through it. This method handles the SharePoint data format automatically and avoids formatting errors that often occur with direct SQL updates.

Adjust your connection settings

When performing updates, make sure your connection string allows editing. You can do this by setting it to use an update-friendly mode — for example, changing your IMEX and RetrieveIds options. This helps ADO recognize your list items correctly.

Check your field type and name

Ensure that the field you’re updating is a “Choice” field that allows multiple selections, not a Lookup field. Also, double-check the internal name of the field in SharePoint, as that’s what ADO uses for updates.

If recordset updates still don’t work

You can consider using SharePoint’s REST API or Microsoft Graph API from VBA. These allow you to send update requests directly to SharePoint and fully support multi-choice fields. It’s a bit more advanced, but it gives reliable results once set up.

Tip: Start with the recordset approach it’s the most straightforward and works for most SharePoint setups.

If you continue to face issues after trying this, please share what error or behavior you see, and we can guide you step by step.

Let me know if you need any further help with this. We'll be happy to assist.

If you find this helpful, please mark this as answered.

Varsha Dundigalla(INFOSYS LIMITED) 3,725 Reputation points Microsoft External Staff

2025-10-16T03:34:06.98+00:00

Hope you're doing well. Could you please let us know if the issue still persists or if it has been resolved? If everything is working fine now, we’d appreciate it if you could mark the response as answered. We’re planning to close the thread by 27 October, so we are hoping for your reply before that.
Tom Weir (CONTSBW) 40 Reputation points

2025-10-16T15:49:43.3433333+00:00
Varsha,

Thanks for the note. I'm validating the recordset approach as the easiest to implement from where I am.

I've been successful using the recordset to update other fields (basically showing this approach is at least as good as the SQL Command.

For the multiple-choice field, I can modify recordset.value and see the difference between recordset.originalvalue. I've confirmed the formatting is consistent between the two.

Main issue that recordset.update doesn't commit the change. This issue seems specific to this field only. What I see is that recordset.value reverts to what was recorded in the recordset.originalvalue. It should go the other way. Recordset.Update works for all other fields that aren't multiple choice.

I'm using recordset.open with adLockOptimistic. Any other ideas to make recordset work beyond switching to Rest?
Tom Weir (CONTSBW) 40 Reputation points

2025-10-16T17:40:15.6+00:00

One other comment. Recordset.Original value has type Variant/String. The change I'm adding to Recordset.Value also is Variant/String.
Varsha Dundigalla(INFOSYS LIMITED) 3,725 Reputation points Microsoft External Staff

2025-10-17T07:01:06.46+00:00
Thank you for confirming the behavior.
Based on what you've observed, it does match how the share point provider handles multiple choice fields.
You can try these steps:

Confirm the column setup Make sure the target column is Choice (Allow multiple selections) (not Lookup/MMS), use the internal name, and ensure the values you write are valid defined choices (no validation rules or workflows blocking updates).

Use update‑friendly ADO settings Open the list with IMEX=0 and RetrieveIds=Yes, use a client‑side keyset cursor with optimistic or pessimistic lock, and ensure your selection includes the ID and the target field (missing the key causes silent reverts).

Write in the shape the provider expects & verify updatability Provide the multi‑choice value as a multi‑value array of strings (not the ;# string), then resync to confirm it committed. If the field is flagged not updatable under those settings, ADO will revert every time.

If these still don’t persist the update, switch to SharePoint REST (preferred) or Microsoft Graph for reliable multi‑value updates (especially required for multi‑Lookup or Managed Metadata fields).

Please let us know if you require any further assistance, we’re happy to help.

If you found this information useful, kindly mark this as "Accept Answer".
Tom Weir (CONTSBW) 40 Reputation points

2025-10-23T21:30:04.75+00:00

Varsha,

I've done as requested. Key to the update seems to be using the client-side keyset cursor. With that set, I'm able to update the recordset.orginalvalue as well as the recordset.underlyingvalue using either the recordset.update or recordset.batchupdate when the replacement value is a string with the concatenated values (e.g., var=item1, item2).

That being said, while it updates on the client-side record, the SharePoint list is still not updated and there are no error messages on the recordset or the connection.

Note that the field errors out when using an array as suggested. Passing an array to the multi-choice field results in "Multiple-step operation generated errors. Check each status value."

I've tried various permutations of the status values to include (item1, item2), ('item1', 'item2'), ("item1", "item2"), (;#item1;# , ;#item2;#), etc. both in array format or as a string without any change.

Not sure if this is a gap in the provider not being able to handle the array.

Any other ideas?
Susmitha T (INFOSYS LIMITED) 1,630 Reputation points Microsoft External Staff

2025-10-24T10:12:57.35+00:00

Thanks for reaching out!

Since you have already confirmed updates work client side but not on the server, The next step would be implementing the REST API (The REST API directly supports multi-choice field updates using the results array syntax) approach for multi-choice field updates or delegating the update to power automate.

Please let us know if you require any further assistance, we’re happy to help.

If you found this information useful, kindly mark this as "Accept Answer".
Tom Weir (CONTSBW) 40 Reputation points

2025-10-24T13:30:40.88+00:00

Thanks. I accept the answer
Varsha Dundigalla(INFOSYS LIMITED) 3,725 Reputation points Microsoft External Staff

2025-10-27T09:00:43.1333333+00:00

Thank you for confirming! To officially mark this as resolved, could you please click the “Accept Answer” button on the post? This helps others find the correct solution and closes the thread. Let us know if you need any further assistance, we’re happy to help!

Share via

Update SharePoint multi choice field programmatically

0 additional answers

Your answer