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

Jan 29, 2023

Hello George,

thanks for the fast reply.

I am trying to update the sharepoint field (see the code below)

For i = 0 To UBound(arr_iID) - 1
CurrentDb.TableDefs("shp_Bestellformular").RefreshLink
CurrentDb.Execute "Update shp_tblName set Bestätigung_um = '" & Now & "' where ID = " & arr_iID(i) & " "
Me.Refresh
Next i

Sometimes, some elements gets updated sometime, nothing gets updated.

BR

George_Hepworth

Silver Contributor

Jan 29, 2023

Why do you think you need to refresh the link before executing the update query? What's the problem you are trying to solve with that?

The actual update looks appropriate except for one thing. It's a good idea to include dbFailOnError to cause an error message to be returned if an update fails. That way you know what actually caused it "not to work".
CurrentDb.Execute "Update shp_tblName set Bestätigung_um = '" & Now & "' where ID = " & arr_iID(i) & " ", dbFailOnError

If the update doesn't raise an error, it silently moves on.

AAB_786
Copper Contributor
Jan 29, 2023
THanks this comand helped me , dbFailOnError

now i recieved the error after 2-3 Updates like:

Error3218: Update not possible while at the moment blocked
- George_Hepworth
  Silver Contributor
  Jan 29, 2023
  AAB_786
  
  That error suggests that one record--the one which failed to update--is currently being edited in a form, and is therefore locked. Does that make sense in the context where you are trying to run this update?
  
  Perhaps you can resolve the problem by adding this line to the code. It will save the current record if it is being edited when the update runs. If it is the record that is blocking the update, that should resolve the problem.
  
  If Me.Dirty Then Me.Dirty = False
  
  For i = 0 To UBound(arr_iID) - 1
  CurrentDb.TableDefs("shp_Bestellformular").RefreshLink
  CurrentDb.Execute "Update shp_tblName set Bestätigung_um = '" & Now & "' where ID = " & arr_iID(i) & " "
  Me.Refresh
  Next i
  - AAB_786
    Copper Contributor
    Jan 29, 2023
    no unfortunately not working in my case, still have some recordsets locked.
George_Hepworth
Silver Contributor
Jan 29, 2023
Also, what's the reason for including the trailing space after the ID, & " "