Update query not working for database in the same file as the vba code

Question

I am new to writing sql in excel vba and I am trying to update certain values in a table range present in the same workbook as the vba code. However, I am encountering an error which says "Operation must use an updateable query". Could someone please help me resolve this. Following is the code I wrote:

Public Sub OperationOnCn()

Dim rs As Recordset
Dim conn As Connection
Dim strsql As String
Dim strConn As String

strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"


Set conn = CreateObject("ADODB.connection")
Set rs = CreateObject("ADODB.recordset")
wksInput.Range("A1").CurrentRegion.Name = "rngInput"

conn.Open strCon

strsql = "Update rngInput set Salary=10000 where Branch='Delhi'"
rs.Open strsql, conn

wksOutput.Range("A1:Q1000").ClearContents

For i = 1 To rs.Fields.Count

 wksOutput.Range("A1").Offset(0, i - 1).Value = rs.Fields(i - 1).Name

Next

wksOutput.Range("A2").CopyFromRecordset rs
rs.Close
conn.Close

End Sub

oratom oratom · Accepted Answer · 2016-04-20T14:41:37

Why are you using a Jet-Connection instead of writing directly from wksInput to wksOutput?

F.e. by using the Range.Copy - Method?

Beneath that, it seems to me, that you wanted to SELECT from wksInput and write to wksOuput, but your strsql is an UPDATE-Statement. So what should happen here, when you execute this?

Update query not working for database in the same file as the vba code

1 Answers