MS Access Update Query to SharePoint List Locking up, no errors

Question

I am combining multiple Excel Worksheets into one SharePoint list so our data is all in one place and modifiable by multiple users at once. The Append query worked without a hitch.

Now I am trying to update one filed in the list with an update query but it keeps locking up MS Access (Not Responding, 100% CPU usage). I have to terminate from the task manager.

I have let it run for as much as 10 minutes. So then I switched to the one time use sub procedure below to update through a recordset. Same issue.

I am able to update the field manually one at a time via the linked list in MS Access. I can update the field via datasheet and dialog in SharePoint.

SharePoint 2010
MS Access 2013

Does anyone have any ideas?

Option Compare Database
Option Explicit

Public Sub UpdateDataPlateDates()

On Error GoTo err_trap
    Dim db As DAO.Database: Set db = CurrentDb()
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer: i = 1
    Dim vDate As Variant
    Dim sNum As String

    strSQL = "SELECT TML.[SERIAL NUMBER], TML.[DATA PLATE DATE] FROM [Tool Master List] AS TML WHERE (((TML.[DATA PLATE DATE]) Is Null));"

    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    With rst
        If Not (.BOF And .EOF) Then
            .MoveLast: .MoveFirst
            Do Until .EOF
                sNum = ![SERIAL NUMBER].Value
                vDate = DLookup("[ACCEPT DATE]", "Tool information", "[SERIAL NUMBER]='" & sNum & "'")
                Debug.Print i, sNum, vDate
                If Not (IsNull(vDate) Or IsEmpty(vDate)) Then
                    vDate = CDate(vDate)

                    .Edit

                        ![DATA Plate Date] = vDate  '//FAILS-LOCKS UP RIGHT HERE WITHOUT and ERROR
                    .Update
                End If

                .MoveNext

                i = i + 1

                sNum = vbNullString
                vDate = Null
                DoEvents
            Loop
        End If
        .Close
    End With
    Set rst = Nothing
    Set db = Nothing
exit_sub:
    Exit Sub
err_trap:
    Debug.Print Err.Number, Err.Description
    Stop
    Resume


End Sub

I should have added that there are NO Workflows running against the SharePoint list. — acr_scout

Ryan Wildry Ryan Wildry · Accepted Answer · 2015-07-08T15:21:13

Is it possible the item you are trying to update is being edited by a user? Have you considered linking the SharePoint table and performing an update query instead?

MS Access Update Query to SharePoint List Locking up, no errors

1 Answers