使用SQL从MS Access VBA更新SharePoint列表时，批量查询错误3981 [英] Bulk Query Error 3981 when updating SharePoint list from MS Access VBA using SQL

问题描述

使用以下语句(在VBA中只是代码的一部分)，我偶尔会不断出现错误3981，只是在尝试进行更新时，插入就没有问题

I kept getting Error 3981 once in a while with the following statements (just one part of the codes in VBA), I don't have problem with insertion, just when it is trying to do the update

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

发生这种情况时，我失去了与服务器的连接，而当我尝试重新连接时，它询问我是否要放弃所有更改.

When this happens, I lost connection to the server and when I tried to reconnect, it asked me if I want to discard all the changes.

推荐答案

我不知道它是否相关，但是此块包含在循环中

I don't know if it is related, but this block is included in a loop

这篇关于使用SQL从MS Access VBA更新SharePoint列表时，批量查询错误3981的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！