Field cannot be updated - VBA SQL

Question

I'm facing an issue when trying to update a Microsoft list. It goes well to SELECT and UPDATE some fields, but not to INSERT INTO.

Here is my code:

Sub SPListPushData()
    
    Const SiteUrl As String = "https://myperfectwebsite"
    Const ListName As String = "{123456789}"
    Dim Conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    
    Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;" & "DATABASE=" & SiteUrl & ";" & "LIST=" & ListName & ";"
    Conn.Open
    
    sql = "INSERT INTO [" & ListName & "] ([MyOrder],[Comment]) VALUES ('12345','Test')"
    Set rs = New ADODB.Recordset
    rs.Open sql, Conn, adOpenDynamic
    
    rs.Close
    Conn.Close
    
End Sub

The error I get is :

Execution error -2147217887 (80040e21): field MyOrder is not a field which could be updated

What am I doing wrong, please?

EDIT : Is it because the connection string use Microsoft.ACE.OLEDB ? I saw that it's a Readonly method, but looks strange as my UPDATE method works

Can the field MyOrder be updates? Looks like a PK to me, so maybe it's an auto_increment? — FunThomas
– FunThomas, Commented Mar 10 at 12:06
PK=Primary Key (the key that uniquely identifies a data row). But if you could update it manually, that's not your problem — FunThomas
– FunThomas, Commented Mar 10 at 12:15

Tim Williams · Accepted Answer · 2025-03-10 20:34:13Z

Here's an extension of my previous answer with added insert/update/delete steps.

The issue you're facing is connected to the IMEX setting you use when opening your connection, as explained here:
https://stackoverflow.com/a/28569981/478884

The MS article linked there is now a 404, but here's an archived verison: https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/194124

The possible settings of IMEX are:

 0 is Export mode
 1 is Import mode
 2 is Linked mode (full update capabilities)

'Use ADO to query a recordset from a SharePoint List
'Needs a VBA Project reference to "Microsoft ActiveX Data Objects x.x Library"
Sub SPListQuery()
    
    Const SiteUrl As String = "https://contoso.sharepoint.com/sites/siteA"
    
    Const listName As String = "TestList" 'List/Library Name or GUID
    
    Dim Conn As New ADODB.Connection, Conn2 As New ADODB.Connection
    Dim rs As ADODB.Recordset, sql As String, recsAffected As Long
    
    'open a couple of connections
    Set Conn = SPConnection(SiteUrl, listName, 2)  'Read, Change, Save Changes
    Set Conn2 = SPConnection(SiteUrl, listName, 0) 'Inserts
    
    '#### query all records for display
    sql = "SELECT * FROM [" & listName & "]"
    Set rs = New ADODB.Recordset
    rs.Open sql, Conn, adOpenStatic
    
    With ThisWorkbook.Worksheets("Results")
        .Cells.Clear 'clear any previous data
        RecordsetToWorksheet rs, .Range("A1")
    End With
    rs.Close
    
    '### insert a record (recordset method; works with connection IMEX=2)
    sql = "SELECT * FROM [" & listName & "] where false" 'opens an empty recordset
    rs.Open sql, Conn, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs.Fields("Title") = "My example title"
    'add any other fields here
    rs.Update
    rs.Close
    
    '### insert a record (SQL method; works with connection IMEX=0, 
    '       but does *not* work when IMEX=2)
    sql = "insert into [" & listName & "] ([Title]) values ('Special title')"
    
   'Conn.Execute sql, recsAffected   '<<<IMEX=2; fails with the same error you saw
    Conn2.Execute sql, recsAffected  'IMEX=0; works

    Debug.Print recsAffected & " record(s) inserted"
    
    '### update a record
    sql = "update [" & listName & "] t set t.Title = 'My new example title' " & _
           " where t.Title like 'My example title' "
    Conn.Execute sql, recsAffected
    Debug.Print recsAffected & " record(s) updated"
    
    '### delete some records
    sql = "delete FROM [" & listName & "] t where t.Title like '%updt%' "
    Conn.Execute sql, recsAffected
    Debug.Print recsAffected & " record(s) deleted"
    
    Conn.Close  'close the connections
    Conn2.Close
End Sub
    
'get a sharepoint connection
Function SPConnection(SiteUrl As String, listName As String, imex As Long) As ADODB.Connection
    Set SPConnection = New ADODB.Connection
    SPConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;" & _
                                    "IMEX=" & imex & ";RetrieveIds=Yes;" & _
                                    "DATABASE=" & SiteUrl & ";" & _
                                    "LIST=" & listName & ";"
    SPConnection.Open
End Function


'Copy data from a recordset `rs` to a worksheet, starting at `StartCell`
Sub RecordsetToWorksheet(rs As ADODB.Recordset, StartCell As Range)
    Dim f As ADODB.Field, i As Long
    For Each f In rs.Fields
            'Debug.Print f.Name, f.Type
            StartCell.Offset(0, i).Value = f.Name
            i = i + 1
        Next f
    If Not rs.EOF Then StartCell.Offset(1).CopyFromRecordset rs
End Sub

Collectives™ on Stack Overflow

Field cannot be updated - VBA SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related