Excel adodb Operation must use an updateable query

[drag1c]

Hi,

I've encoutered really strange error in both microsoft access query design and in excel vba adodb.

Updateable query looks like this:

strSQL = "UPDATE tbWorkplanPNs " & _
                        "SET tbWorkplanPNs.Version = ( " & _
                        "SELECT TOP 1 tbWorkplanRevision.Version " & _
                        "FROM tbWorkplanRevision " & _
                        "WHERE tbWorkplanRevision.Workplan = tbWorkplanPNs.Workplan " & _
                        "ORDER BY tbWorkplanRevision.[Current Date] DESC " & _
                        ") WHERE tbWorkplanPNs.Workplan = '" & .myvalue.Value & "'"

            conn.Execute strSQL

For some strange reason, error is "Operation must use an updateable query."

I've tested this query in Microsoft Access and I can view it in "Datasheet view" but when I try to run it from Access Objects, it gives the same error.
My Assumption is that Access have issues when using parameters like TOP 1 and DESC, so it might require Temporary table with sorted data.

Could anyone provide answer? If that's so, could anyone create that kind of query or fix this one, so in future I can look for a way to do it myself

 

[Micron]

Your assumption is incorrect. A subquery is not updatable so you will have to devise a workaround - perhaps a staging table or update a recordset, or a function that retrieves the sub value(s). Perhaps the tables are not correctly related, given that you need the subquery. Can you not just join them and use the TOP predicate (and perhaps criteria as well)?

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

 

[drag1c]

Your assumption is incorrect. A subquery is not updatable so you will have to devise a workaround - perhaps a staging table or update a recordset, or a function that retrieves the sub value(s). Perhaps the tables are not correctly related, given that you need the subquery. Can you not just join them and use the TOP predicate (and perhaps criteria as well)?

Microsoft Access tips: Why is my query read-only?

List of reasons why a Microsoft Access query will yield non-editable results.

allenbrowne.com

Well, to make it work, I need to use DESC and TOP 1, because of the latest date.
I will see some workaround with temporary tables which I will drop at end.

Thank you sir.