Bulk Query Error 3981 when updating Sharepoint linked list through Access VBA using SQL

Question

Access 2016

SharePoint 2013

Every minute, the program will loop through some data, and either insert a new entry, update an existing entry or does nothing based on certain criteria.

This may sound wired, but I don't have any problem with insertion, only with updating after the program had been successfully running for a few hours. And also, when this happened, I found that the SharePoint Server was disconnect, and access was working offline. Here is a part of the code. Why is it doing this?

If signal < 1 Then
                
            ' check if the curr lineNum has been defined in the dictionary
            If Module1.dict_pre_Signal.Exists(lineNum) Then
                    
                    ' check if line at the current time is down and if curr time is within 3 mins diff from pre time
                    If Module1.dict_pre_Signal.Item(lineNum) < 1 And DateDiff("s", Module1.dict_pre_Time.Item(lineNum), combine_Date_Time) < 300 Then
                        ' Update the old downtime entry
                        SQLst = "UPDATE TS_Downtime_Entry AS tb_1 SET tb_1.[End] = #" & combine_Date_Time & "# WHERE exists " _
                            & "(select top 1 * from TS_Downtime_Entry as tb_2 INNER JOIN Line ON tb_2.LineNo = Line.ID WHERE Line.[Line Code] = '" _
                            & lineNum & "' and  tb_2.Downtime_ID =  tb_1.Downtime_ID ORDER BY tb_2.Downtime_ID DESC);"
                        db.Execute SQLst, dbFailOnError
                        ' ---------------Troubleshooting---------------
                        ' MsgBox ("Line #: " & lineNum & ", UPDATE")
                    Else
                        ' Insert new downtime entry
                        'MsgBox ("Line #: " & lineNum & ", INSERT")
                        SQLst = "INSERT INTO TS_Downtime_Entry (LineNo, Downtime_ID, Start, [End]) SELECT Line.ID, " _
                            & "'" & DT_ID & "', #" & combine_Date_Time & "#, #" & combine_Date_Time & "#" _
                            & " FROM Line WHERE Line.[Line Code] = '" & lineNum & "'"
                        db.Execute SQLst, dbFailOnError
                        ' ---------------Troubleshooting---------------
                        
                    End If
            Else
                
                    Module1.dict_pre_Signal.Add lineNum, 10
                    Module1.dict_pre_Time.Add lineNum, combine_Date_Time
                    ' Insert
                    SQLst = "INSERT INTO TS_Downtime_Entry (LineNo, Downtime_ID, Start, [End]) SELECT Line.ID, " _
                        & "'" & DT_ID & "', #" & combine_Date_Time & "#, #" & combine_Date_Time & "#" _
                        & " FROM Line WHERE Line.[Line Code] = '" & lineNum & "'"
                    db.Execute SQLst, dbFailOnError
                    ' ---------------Troubleshooting---------------
                    ' MsgBox ("Line #: " & lineNum & ", INSERT")
            End If
End If
            
Module1.dict_pre_Signal.Item(lineNum) = signal
Module1.dict_pre_Time.Item(lineNum) = combine_Date_Time

Thanks in advance!!!

Answer 1

Hi Aaron,

SharePoint usually requires a user to log in. Then, a connection has to be maintained. I am not sure a non-interactive connection can be maintained, if you're only using code to manipulate the List. Do you notice a login prompt every now and then while the database is open?

Answer 2

Thanks for the reply.

I don't see any prompt asking for login info.

I only got the error message pop up every time.

Answer 3

Is it the possible that an insertion query was not fully completed but the next update query has already started doing the updates?

One of my co-worker said he had similar issue when he was trying to insert many entries at the same time.

I timed one loop, it took about 3 sec to finish. So, I am guessing if it is because that SharePoint entries were being modified too fast and that caused this error.

If it is the case, what should I do to solve the problem?

Answer 4

Network connection problems is hard to overcome because we can't control all the possible causes. Access is "smart" enough to work offline using a cached copy of the data and also manage to sync up when reconnected. Unfortunately, if you're updating records using code when the connection drops, there's probably not a whole lot we can do about the situation.

Just my 2 cents...

PS. One approach might be is to check for a live connection first before making record changes.

Answer 5

Hi Aaron,

>>I timed one loop, it took about 3 sec to finish. So, I am guessing if it is because that SharePoint entries were being modified too fast and that caused this error.

To check whether it is related with this, I would suggest you add delay time before process the next insert, update query. Maybe you could wait for 5 minutes.

Run the query every minutes is not a reasonable design.

Best Regards,

Tao Zhou

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].