0

I am adding several records to a sharepoint list programmatically using to save time over using a form on the sharepoint as I sometimes have to add up to 50 entries at any given time. It was working fine, and then the macro was shared with other people in the region to use as well, and once that was done it's no longer working. I get the Row handle referred to a deleted row or a row marked for deletion error once it gets to rst.AddNew. I can get the recordset and display it all, and it matches with what is showing in the sharepoint list, so I'm not entirely sure why it has suddenly stopped working. I know looping through everything is not the most efficient way of doing it and would be better to write it all to an array but I was in a time crunch and brute force was easier to write in the moment.

Sub FRC_Upload()

Dim Connect As ADODB.Connection
Dim rst As ADODB.Recordset
Dim mySQL As String
Dim LastRow As Long
Dim MyWorkbook As Workbook


Set MyWorkbook = Workbooks("FRC Upload.xlsm")


LastRow = MyWorkbook.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row


Set Connect = New ADODB.Connection
Set rst = New ADODB.Recordset


mySQL = "SELECT * FROM [FRC];"


With Connect
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=URL for my sharepoint list;"
    .Open
End With

rst.Open mySQL, Connect, adOpenDynamic, adLockOptimistic


    For i = 2 To LastRow

        rst.AddNew
            rst.Fields("Title") = MyWorkbook.Sheets("Data").Range("A" & i)
            rst.Fields("MA") = MyWorkbook.Sheets("Data").Range("B" & i)
            rst.Fields("ScheduleDate") = MyWorkbook.Sheets("Data").Range("C" & i)
            rst.Fields("AccountNumber") = MyWorkbook.Sheets("Data").Range("D" & i)
            rst.Fields("WorkorderNumber") = MyWorkbook.Sheets("Data").Range("E" & i)
            rst.Fields("WorkOrderType") = MyWorkbook.Sheets("Data").Range("F" & i)
            rst.Fields("RescheduleClassification") = MyWorkbook.Sheets("Data").Range("G" & i)
            rst.Fields("Comments") = MyWorkbook.Sheets("Data").Range("H" & i)
    
    Next

    rst.Update


    If CBool(rst.State And adStateOpen) = True Then rst.Close
    If CBool(Connect.State And adStateOpen) = True Then Connect.Close

End Sub
DizturbdOne
  • 26
  • 2

2 Answers2

1

I got the error again today so tried the solution from Tim to no avail. After messing with it for most of the day, I tried doing just a direct INSERT statement and load only one new record into the list, which gave me a new error saying that it was unable to insert the record because the data for one of the fields wasn't allowed. Some of the fields are restricted to certain options, and turns out I had a typo in some of my data, and excel was giving the error about record pending deletion as a generic error rather than giving the error that data wasn't allowed. Once I corrected the typo's everything ran perfectly. Wanted to post the solution in case anyone else runs into this random error. Honestly if I had implemented data validation like I wanted to but didn't have time for this would not have been an issue.

DizturbdOne
  • 26
  • 2
0

If all you need to do is add new rows then use a query which just returns an empty recordset: eg. "SELECT * FROM [FRC] where 1=0;"

The empty recordset still has all the fields you need to add the new records, but no other records so you won't run into any contention issues.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • It started working again just fine once I increased the data set I was adding and I haven't had any problems since, but your answer makes a lot of sense so I've accepted it. as you said cannot have a conflict in the records if there are none. – DizturbdOne Mar 23 '22 at 17:34
  • got the error again today, loading an empty recordset did not fix the issue – DizturbdOne Apr 06 '22 at 19:15
  • I have no idea why that message might appear. You can always try performing an insert instead: https://stackoverflow.com/questions/66194957/sharepoint-vba-excel-addob-record-addnew – Tim Williams Apr 06 '22 at 19:19
  • I did do that actually which gave me a new error, which led me to the solution I just posted below. Some of the fields are restricted to certain options, and I had a typo in some of my data and Excel was throwing the pending deletion error as a generic error. – DizturbdOne Apr 06 '22 at 19:21
  • I did see your post after I made that comment - good to see you figured it out. – Tim Williams Apr 06 '22 at 19:41
  • Thank you for all the assistance, I was really beating my head against the wall with this one. – DizturbdOne Apr 06 '22 at 19:42