none
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!!!

    Friday, December 08, 2017 1:26 AM

All replies

  • 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?

    Friday, December 08, 2017 1:49 AM
  • Thanks for the reply.

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

    I only got the error message pop up every time.

    Friday, December 08, 2017 2:02 AM
  • 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?

    Friday, December 08, 2017 2:31 AM
  • 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.
    Friday, December 08, 2017 2:33 AM