Trying and failing to write item to a Sharepoint list

[PeteJohnston]

I've been given the task of putting a friendlier front end over a Sharepoint "database". Part of this involves importing test data from an existing Sharepoint list, about 4K records. I'm using liked tables thru Windows Sharepoint Services. If I open the table directly I can key the information in and create a new record. When I try to add them thru code it says it has done the update but actually it seems to do nothing. The test data needs to look recognizable to the UAT people and I don't fancy keying in 4000 records so VBA has to be the way. I'm doing a simple loop around the existing data with AddNew/Update to write the record.

    Set rst = db.OpenRecordset("Select * From tblLocalVersion Order By [ID]")
    If rst.RecordCount > 0 Then
        Set rstNew = db.OpenRecordset("qryAlternativeNew4", dbOpenDynaset)
        With rstNew
            Do While Not rst.EOF
                vStatus = SysCmd(acSysCmdSetStatus, "Inserting ID " & rst.Fields("ID"))
                .AddNew
                .Fields("Edit") = rst.Fields("Edit")
                .Fields("DateDueRequester") = CDate(Nz(rst.Fields("Due to Requester"), Date))
                .Fields("RequesterContact") = CLng(rst.Fields("Report Requester"))
                .Fields("Request Assigned To") = CLng(rst.Fields("Request Assigned To"))
....
                .Update
                rst.MoveNext
            Loop
        End With
    End If

I'm maybe just looking straight thru it but I can't see what I'm doing wrong.

All legal suggestions gratefully received

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Andrzejek]

Just a guess here, but you open your rstNew from the query (?) qryAlternativeNew4
Can you actually update the data in the query?
Do you want to update the table(s) instead?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PeteJohnston]

I tried changing the query to the base table but that did not help. Then I commented out all of the data setup stuff apart from the required fields and tried again. It successfully did the INSERT! I then went thru un-commenting the rest one by one. It did some Okay but failed on others where it didn't like the values. They were mainly value lists and fields than didn't like Null.

If Microsoft gave meaningful error messages instead of "File TMP0MEMO.TMP cannot be deleted" or "Numeric overflow" (on non-numeric fields) or even no error message at all but doesn't do the insert it would make our lives a lot easier!

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)