Sharepoint list update is not working sometimes

[AAB_786]

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 using different commands like CurrentDb.TableDefs("shp_table").RefreshLink but unfortunately its not working.

I am using a loop with maybe 5 recordsets, sometime 4 gets updated, sometimes 5 my update command is right. Do you have some possible solution for this?

many thanks in Advance

BR

Arslan

[#2]

@AAB_786 

What does it mean to say that you want to "update" a SharePoint list? What, exactly, is supposed to be changed? How is it to be changed? 

Show us the actual VBA code; we can't see it unless you provide a copy to look at.

[#3]

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

[#4]

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.

[#5]

Also, what's the reason for including the trailing space after the ID, & " "

[#6]

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

[#7]

@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

[#8]

And, out of curiosity, why does the CurrentDb.Execute line include that trailing space? I would think it's unneeded, but there may be a reason I just don't see....

[#9]

actually the recorsets get locked automatically, how can we unlock them?

[#10]

no unfortunately not working in my case, still have some recordsets locked.

[#11]

I have no idea what could be locking the recordsets, other than the guess at a record in a form being edited simultaneously. Well, some things could be considered. Are others working on the same forms with the same SharePoint lists simultaneously? Is there a network process temporarily blocking your connection?

[#12]

I have spent more than 10 Hours to locate the error, have done everything, also which you said i implemented, but unfortunately no success.

There is only me who is just updating one table, which have may be 10 entries, but why the DB recordsets gets blocked, no idea...

Is there any workaround?

[#13]

I don't know. If the table is getting locked randomly, "something" else or "someone" else has to be using that table. At this point I'd focus away from your Access front end to it and start investigating the environment in which it is running, and who else might be updating it from other places.

[#14]

Hi,

Are you on Windows 11 by chance?

I ask because I just got off the phone with someone who reported similar sounding "disruptions" to Sharepoint 365 for about 3 months, and according to him only on the Windows 11 systems. On the Win10 machines, everything works as it has for years.

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

[#15]

Karl, do you have any indication that this impact other applications, or only Access/SharePoint applications? It appears to be a Windows 11 "anomaly", but it's not clear if it is specific to the Access and SharePoint configuration.

[#16]

Hello dear,

no i am not using Windows 11, it is Windows 10. As the connection is also not getting lost, but the recordsets get locked, after second, third time it works.

Servus
Arslan

[#17]

Hi George,

The report today was only about Sharepoint because they have their entire backend on it. I'm doing a little digging for more reports on this. If you know of any, please let me know. This discussion here doesn't seem to hit it, as Arslan replied that he's using Win10.

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

[#18]

Hello Karl,

I have a feeling,, if the cursor is on some recordset then it gets locked, and let not update.

But i applied all the different ways but unfortunately not successfull.

Is there anyway to move the curso to some other recordset?

BR
Arslan

[#19]

Hello all,

i still have a same situation, any other solution for this problem?

BR

[#20]

Maybe you use relations between tables? My experience that this cause locked records. That is a reason I never use relations between tables if my tables are in sharepoint in combination with linked sharepoint tables in Access frontend