Operation must use and updatable query. (Error 3073)-CodePudding

What is wrong with my logic below, is there any alternative to get calculatable column which will sum Balances for every unique KEY ?

Purpose: To create a summary column, calculation of Balances for each unique Key created ( Sort Code Source Account) which will be used later to define whether acc type is Dr/Cr.

Code: below code should create summary column , but every time it triggers error depicted in attached screenshot.

***' Creating Sum of Balances based on Key , to differtiate Dr/Cr Account types
DoCmd.RunSQL "ALTER TABLE Output_File ADD Summing varchar(255)"
DoCmd.RunSQL "UPDATE Output_File SET Summing =" _
         & "(SELECT SUM(Output_File.CURR_VALUE)" _
         & " FROM Output_File GROUP BY Output_File.`UNIQUE KEYS`)"***

Error: Error

CodePudding user response：

In MS Access SQL, update queries must be updateable or not read-only. Any use of subqueries renders the query read-only. See Allen Browne's Why is my query read-only? One workaround is to use domain aggregates such as DSum available in Access SQL and VBA.

Ideally, too, consider using a saved query and avoid the need of building long SQL statements in VBA and have to worry about line breaks and concatenations. Below assumes UNIQUE KEYS is a number field. If text field, wrap in single quotes. If a date field, wrap in hashtags/pound symbols.

SQL (save as stored query)

UPDATE Output_File o
SET Summing = DSum("CURR_VALUE", "Output_File", "`UNIQUE KEYS` = " & o.`UNIQUE KEYS`)

VBA

DoCmd.OpenQuery "mySavedQuery"     ' NO NEED TO CLOSE ACTION QUERIES

CurrentDb.Execute "mySavedQuery"   ' ALTERNATIVE CALL