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]

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

[#3]

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.

[#4]

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

[#5]

@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. 

[#6]

@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.

[#7]

1. Are the checkboxes of type yes/no? If you use numeric, update could be a problem if the field value is null
2. Don't use: CurrentDb.TableDefs("shp_Bestellformular").RefreshLink. You don't need this if there aren't design changes of your table.
3. Try for test to edit values in the link table directly. Sometimes the record lock could occur on a specific record.

[#8]

1. I changed it from Numeric to YES / NO
2. I commented it.
3. Local works always.

And your 1 & 2 i have changed still no difference.
any other idea?

[#9 · Accepted solution]

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

[#10]

Hello Karl,

thank you very much, Now i tested with your code (Me.Recordsource = "") and still no problem, i hope that this solved my problem.

BR

[#11]

Hi @George Hepworth ,

My Windows 11-related query was obviously about a different Sharepoint problem. Just as a follow-up that might help somebody else:

The company affected by frequent but irregular interruptions to Sharepoint informed me that the problems were most probably caused by the "Dell Optimizer". At least things have been running for 3 days without problems since they uninstalled that tool. According to Dell:

"Dell Optimizer is a software application that intelligently and dynamically optimizes the performance of your computer by using artificial intelligence(AI) and machine learning(ML)."

Fascinating what all unexpected and hard to detect can be involved in such problems. 

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

[#12]

Hallo Karl,

Thanks for your tip, at the moment it works fine. Now I have a problem after Me.Recordsource=" move the cursor to first RS. Is there any solution which let the RS stay on the same position.

Thanks

[#13]

Hi,

You can use code similar to my FAQ 4.5 to go back to the same record as before.

I assume that you understand German as there's some in your code. Even if not you can just take the code from there. Your current code then practically replaces the Requery line in the FAQ example.

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