Running DAO sql on Excel data (1 Viewer)

[CedarTree]

Hello. Just playing around with running queries on Excel data (using DAO). I can create and use a recordset with no issues. But I get error 3073 (updateable query) when I try to execute this sql. Help please?

db.execute ("UPDATE [Sheet1$] SET [Done] = 1")

 

[CedarTree]

My larger code for reference...

Sub subCreateDaoRecordsetFromExcelSheet()
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim dbEngine As DAO.dbEngine
    Dim sPath As String
    Dim sql As String
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    sPath = ThisWorkbook.FullName
    Set dbEngine = New DAO.dbEngine
    Set db = dbEngine.OpenDatabase(sPath, False, True, "Excel 12.0 Xml;HDR=Yes;")
    
    sql = "SELECT * FROM [" & ws.Name & "$]"
    sql = sql + " WHERE [ID] = 100"
    sql = sql + " ORDER BY [Date]"
    Set rst = db.OpenRecordset(sql)
    If Not rst.EOF Then
        rst.movefirst
        While Not rst.EOF
            Debug.Print rst("ID"), rst("Date"), rst("Type")
            rst.MoveNext
        Wend
    End If
    
    rst.Close
    Set rst = Nothing
    
    sql = "UPDATE [" & ws.Name & "$]"
    sql = sql + " SET [Done] = 1"
    db.Execute (sql)
    
    db.Close
    Set db = Nothing
    Set dbEngine = Nothing
    
End Sub

 

[CJ_London]

So far as I am aware you cannot use DAO to update existing data in an excel table. In access you can update an excel table manually, but not by using sql. Never tried it from excel.

Might help if you actually specified a table, including headers

A quick search suggests using ADO might work, but no idea if that is fake news

 

[MadPiet]

Hello. Just playing around with running queries on Excel data (using DAO). I can create and use a recordset with no issues. But I get error 3073 (updateable query) when I try to execute this sql. Help please?

db.execute ("UPDATE [Sheet1$] SET [Done] = 1")

Before you make yourself crazy trying it in code, you might want to try creating a linked sheet in your database and running the update query against that linked table. Super quick, and you'll know immediately if it works. (I don't think it does. I want to say MSFT lost a lawsuit about it, and it hasn't worked in 25 years or so.)

 

[arnelgp]

Let assume your ID column is in Column A, Date in column B, Type in column C and Done In Column D
without using DAO.Recordset:

Sub UpdateDoneColumnOnSheet1()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
   
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
    ' Remove any existing filters
    If ws.AutoFilterMode Then ws.AutoFilterMode = False
   
    ' Apply AutoFilter on ID Column (Column A)
    Set rng = ws.Range("A1").CurrentRegion
    rng.AutoFilter Field:=1, Criteria1:=100 ' Filter column 1 (ID) for 100
   
    ' Update visible cells only (excluding header)
   
    ' Column D is the Done column
    On Error Resume Next
    ws.Range("D2:D" & lastRow).SpecialCells(xlCellTypeVisible).Value = 1
    On Error GoTo 0
   
    ' Remove filter
    ws.AutoFilterMode = False
    Set rng = Nothing
    Set ws = Nothing
End Sub

 

[The_Doc_Man]

Before you make yourself crazy trying it in code, you might want to try creating a linked sheet in your database and running the update query against that linked table. Super quick, and you'll know immediately if it works. (I don't think it does. I want to say MSFT lost a lawsuit about it, and it hasn't worked in 25 years or so.)

I know that MS has been the target of a LOT of lawsuits because of their sometimes light-fingered approach to technology they wanted. When MadPiet mentioned it, it rang a bell so I looked it up. The lawsuit regarding Excel was 20+ years ago (2005) and was related to a patent infringement violation. MS used technology created by a fellow named Carlos Armando Amado to do Excel spreadsheet updates in versions from Ac95 through Ac2002.

Here's a link:

Microsoft to Pay $8.9m in Patent Suit

Despite the fact that Microsoft holds an extensive patent portfolio containing in excess of 10,000 issued and pending United States patents, a court found that it did not have the right to link Access and Excel using a method invented by Brazilian programmer Carlos Armando Amado.

betanews.com

There is another lawsuit pending that probably will fail (but who knows) regarding the "forced upgrade to Win11".

 

[isladogs]

Before you make yourself crazy trying it in code, you might want to try creating a linked sheet in your database and running the update query against that linked table. Super quick, and you'll know immediately if it works. (I don't think it does. I want to say MSFT lost a lawsuit about it, and it hasn't worked in 25 years or so.)

Yes MS lost a lawsuit about this and removed the ability to edit linked Excel worksheet in A2002.
However, if you set IMEX=0 in the connection string instead of the default IMEX=2, then all linked Excel worksheets become editable again.
See my article

Edit Linked Excel Data from Access

This article describes two methods of making linked Excel files directly editable in Access

isladogs.co.uk