Update Excel worksheet using ADODB from external application-CodePudding

I've found lots of posts on this problem, but so far no solutions have helped.

I'd like to read and write data from/to an Excel worksheet from an external VBA application - so far it reads OK, but I get an error while trying to write values to the ADODB Recordset.

Here's the code:

Sub UpdateFromExcel()
'https://stackoverflow.com/questions/15620080/reading-data-using-oledb-from-opened-excel-file

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath
Dim sSQL As String

sSQL = "SELECT * FROM [Sheet1$A1:N10000]"
sPath = frmExcelSync.txtFilePath

oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
             "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"

oRS.Open sSQL, oConn, adOpenDynamic, adLockOptimistic


Do While Not oRS.EOF
    'ITERATE THROUGH EACH ROW HERE
    
    'ADD CODE ABOVE
    
'****GET AN ERROR HERE:****
    oRS.Update "Occurrence Name", "Test"
    oRS.MoveNext
Loop

oRS.Close
oConn.Close

End Sub

The error is

"Cannot update. Database or object is read-only".

I've tried different lock and cursor types, and I've tried editing the fields then using the .update method, but nothing has worked so far.

Any suggestions?

CodePudding user response：

your update statement is not correct. I believe you want to update the column "Occurrence Name" with the value "Test"

What you should write is.

Do While Not oRS.EOF
    oRS![Occurrence Name].value = "Test"


     oRS.MoveNext
    Loop

oRS.Update

CodePudding user response：

The problem seems to have gone away somehow. I tried a few different things (different spreadsheets) with mixed success then restarted the application - now it works.

No code changes at all.