Forum Discussion

Copper Contributor

Jan 29, 2023

Solved

Sharepoint list update is not working sometimes

Hello all, I have a sharepint list, and updating the list using access vba. I have a problem sometime the update works and sometimes it doesnot works, i have been trying to solve this problem usi...

MS Access

VBA

Karl_Donaubauer
Feb 09, 2023
Hi,

Do I understand the situation in your application right?

1. You have a form with a linked Sharepoint list as recordsource.

2. From within this form with a button or sth you are trying to update several records of the same list with your DAO.Execute statement.

3. You get error 3218 and you think it occurs for the currently active record in the form.

If that's correct then I would

a) not do a Refreshlink for the list or Refresh on the form in every iteration of the loop where you do the Execute.

b) try if the updates work when you disconnect the form from the list while executing the update statement. Just as a hard test if the form is the culprite:

Me.Recordsource = ""

For i = 0 To UBound(arr_iID) - 1
CurrentDb.Execute "Update shp_tblName set Bestätigung_um = '" & Now & "' where ID = " & arr_iID(i), dbFailOnError
Next i

Me.Recordsource = "shp_tblName"

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

Me.Refresh

AAB_786

Copper Contributor

Feb 09, 2023

Thanks for your reply, no i am not using any relational tables, this problem arises only by update of a single table.

Jurgen_Geelen

Copper Contributor

Feb 09, 2023

Another issue: avoid a form with recordset type dynaset and update the underlying table with curremtdb.execute. If you want both than you can better create a local temp table en put this temp table under the underlying form. Fill this temp table with the data you want to see/edit with an add query. Put in the form_afterupdate of the form the code to add/update the recordset of the sharepoint list. This is not the most sexy solution because you need a lot of code. On the other hand this avoid recordset locking and your form works a lot better.

AAB_786
Copper Contributor
Feb 09, 2023
Thanks for your solution with temp table, as you already said, it will take a lot of time to programm.

I am updating the sharepoint list by only using currentdb.execute
- Jurgen_Geelen
  Copper Contributor
  Feb 09, 2023
  AAB_786
  Are you sure that in sharepoint in your sharepoint lists there are no dropdown lists? If yes, than Sharepoint have created a relation with that table on the background.
  - AAB_786
    Copper Contributor
    Feb 09, 2023
    Jurgen_Geelen
    
    I have no dropdown lists in Sharepoint list, i have only checkboxes and some text fields in sharepoint list, and it's not too long and also have may be 20 Recordsets.