CODEWITHSUNDEEP
excelvbaadodbrecordset
user1756095
user1756095

Reputation: 83

ADODB - why is my recordset read-only?

In Excel, I'm using ADODB connection to build a recordset, fetching data from the worksheet in its own workbook as following:

Public Sub test()
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strSQL As String
    Dim k As Variant

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0;HDR=No;IMEX=1;Readonly=False"";"

    strSQL = "SELECT F1 FROM [Workbench$];"

    rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic
    rst.MoveFirst

    While Not rst.EOF
        rst("F1") = "NewValue"
        rst.Update
        rst.MoveNext
    Wend

End Sub

However, an error occurs while I run the code:

Run-time error '-2147217911 (80040e09)':
Cannot update. Database or object is read-only.

I have checked about permission of my opened workbook and it is ok (Full Control access for everyone).

What did I do wrong here?

Upvotes: 2

Views: 7193

Answers (2)

Ivo
Ivo

Reputation: 51

I fixed my problem by Use IMEX=0

Upvotes: 5

mrsnax
mrsnax

Reputation: 9

rst.Open strSQL, cnn, adOpenStatic, adLockOptimistic

replace to

rst.Open strSQL, cnn, 1, 3

Upvotes: -1

Related Questions