[ACCEPTED]-Operation must use an updatable query. (Error 3073) Microsoft Access-ms-access

Since Jet 4, all queries that have a join 13 to a SQL statement that summarizes data 12 will be non-updatable. You aren't using 11 a JOIN, but the WHERE clause is exactly 10 equivalent to a join, and thus, the Jet 9 query optimizer treats it the same way it 8 treats a join.

I'm afraid you're out of luck 7 without a temp table, though maybe somebody 6 with greater Jet SQL knowledge than I can 5 come up with a workaround.

BTW, it might 4 have been updatable in Jet 3.5 (Access 97), as 3 a whole lot of queries were updatable then 2 that became non-updatable when upgraded 1 to Jet 4.

--

I had a similar problem where the following 3 queries wouldn't work;

update tbl_Lot_Valuation_Details as LVD
set LVD.LGAName = (select LGA.LGA_NAME from tbl_Prop_LGA as LGA where LGA.LGA_CODE = LVD.LGCode)
where LVD.LGAName is null;

update tbl_LOT_VALUATION_DETAILS inner join tbl_prop_LGA on tbl_LOT_VALUATION_DETAILS.LGCode = tbl_prop_LGA.LGA_CODE 
set tbl_LOT_VALUATION_DETAILS.LGAName = [tbl_Prop_LGA].[LGA_NAME]
where tbl_LOT_VALUATION_DETAILS.LGAName is null;

However using DLookup 2 resolved the problem;

update tbl_Lot_Valuation_Details as LVD
set LVD.LGAName = dlookup("LGA_NAME", "tbl_Prop_LGA", "LGA_CODE="+LVD.LGCode)
where LVD.LGAName is null;

This solution was originally 1 proposed at https://stackoverflow.com/questions/537161/sql-update-woes-in-ms-access-operation-must-use-an-updateable-query

The problem defintely relates to the use 13 of (in this case) the max() function. Any 12 aggregation function used during a join 11 (e.g. to retrieve the max or min or avg 10 value from a joined table) will cause the 9 error. And the same applies to using subqueries 8 instead of joins (as in the original code).

This 7 is incredibly annoying (and unjustified!) as 6 it is a reasonably common thing to want 5 to do. I've also had to use temp tables 4 to get around it (pull the aggregated value 3 into a temp table with an insert statement, then 2 join to this table with your update, then 1 drop the temp table).

Glenn

There is no error in the code. But the error 8 is Thrown because of the following reason.

 - Please check weather you have given Read-write permission to MS-Access database file.

 - The Database file where it is stored (say in Folder1) is read-only..? 

suppose 7 you are stored the database (MS-Access file) in 6 read only folder, while running your application 5 the connection is not force-fully opened. Hence 4 change the file permission / its containing 3 folder permission like in C:\Program files all most all 2 c drive files been set read-only so changing this 1 permission solves this Problem.

I know my answer is 7 years late, but here's 4 my suggestion anyway:

When Access complains 3 about an UPDATE query that includes a JOIN, just 2 save the query, with RecordsetType property set to Dynaset (Inconsistent Updates).

This 1 will sometimes allow the UPDATE to work.

Thirteen years later I face the same issue. DISTINCTROW 22 did not solve my problem, but dlookup did.

I 21 need to update a table from an aggregate 20 query. As far as I understand, MS Access 19 always assumes that de junction between 18 the to-update table and the aggregate query 17 is one-to-many., even though unique records 16 are assured in the query.

The use of dlookup 15 is:

DLOOKUP(Field, SetOfRecords, Criteria)

Field: a string that identifies the field 14 from which the data is retrieved.

SetOfRecords: a string 13 that identifies the set o record from which 12 the field is retrieved, being a table name 11 or a (saved) query name (that doesn’t require 10 parameters).

Criteria: A string used to restrict 9 the range of data on which the DLookup function 8 is performed, equivalent to the WHERE clause 7 in an SQL expression, without the word WHERE.

Remark

If 6 more than one field meets criteria, the 5 DLookup function returns the first occurrence. You 4 should specify criteria that will ensure 3 that the field value returned by the DLookup 2 function is unique.

The query that worked 1 for me is:

UPDATE tblTarifaDeCorretagem 
   SET tblTarifaDeCorretagem.ValorCorretagem = 
    [tblTarifaDeCorretagem].[TarifaParteFixa]+
    DLookUp(
     "[ParteVariável]",
     "cstParteVariavelPorOrdem",
     "[IdTarifaDeCorretagem] = " & [tblTarifaDeCorretagem].[IdTarifaDeCorretagem]
    );

I would try building the UPDATE query in 11 Access. I had an UPDATE query I wrote myself 10 like

UPDATE TABLE1
SET Field1 = 
(SELECT Table2.Field2
 FROM Table2
 WHERE Table2.UniqueIDColumn = Table1.UniqueIDColumn)

The query gave me that error you're 9 seeing. This worked on my SQL Server though, but 8 just like earlier answers noted, Access 7 UPDATE syntax isn't standard syntax. However, when 6 I rebuilt it using Access's query wizard 5 (it used the JOIN syntax) it worked fine. Normally 4 I'd just make the UPDATE query a passthrough 3 to use the non-JET syntax, but one of the 2 tables I was joining with was a local Access 1 table.

This occurs when there is not a UNIQUE MS-ACCESS 16 key for the table(s) being updated. (Regardless 15 of the SQL schema).

When creating MS-Access 14 Links to SQL tables, you are asked to specify 13 the index (key) at link time. If this is 12 done incorrectly, or not at all, the query 11 against the linked table is not updatable

When 10 linking SQL tables into Access MAKE SURE 9 that when Access prompts you for the index 8 (key) you use exactly what SQL uses to avoid 7 problem(s), although specifying any unique 6 key is all Access needs to update the table.

If 5 you were not the person who originally linked 4 the table, delete the linked table from 3 MS-ACCESS (the link only gets deleted) and 2 re-link it specifying the key properly and 1 all will work correctly.

(A little late to the party...)

The three 2 ways I've gotten around this problem in 1 the past are:

1. Reference a text box on an open form
2. DSum
3. DLookup

I had the same issue.

My solution is to first 3 create a table from the non updatable query 2 and then do the update from table to table 1 and it works.

You can always write the code in VBA that 16 updates similarly. I had this problem too, and 15 my workaround was making a select query, with 14 all the joins, that had all the data I was 13 looking for to be able to update, making 12 that a recordset and running the update 11 query repeatedly as an update query of only 10 the updating table, only searching the criteria 9 you're looking for

    Dim updatingItems As Recordset
    Dim clientName As String
    Dim tableID As String
    Set updatingItems = CurrentDb.OpenRecordset("*insert SELECT SQL here*");", dbOpenDynaset)
    Do Until updatingItems .EOF
        clientName = updatingItems .Fields("strName")
        tableID = updatingItems .Fields("ID")
        DoCmd.RunSQL "UPDATE *ONLY TABLE TO UPDATE* SET *TABLE*.strClientName= '" & clientName & "' WHERE (((*TABLE*.ID)=" & tableID & "))"
        updatingItems.MoveNext
    Loop

I'm only doing this to 8 about 60 records a day, doing it to a few 7 thousand could take much longer, as the 6 query is running from start to finish multiple 5 times, instead of just selecting an overall 4 group and making changes. You might need 3 ' ' around the quotes for tableID, as it's 2 a string, but I'm pretty sure this is what 1 worked for me.

Mine failed with a simple INSERT statement. Fixed 1 by starting the application with 'Run as Administrator' access.

MS Access - joining tables in an update 1 query... how to make it updatable

1. Open the query in design view
2. Click once on the link b/w tables/view
3. In the “properties” window, change the value for “unique records” to “yes”
4. Save the query as an update query and run it.

In essence, while your SQL looks perfectly 10 reasonable, Jet has never supported the 9 SQL standard syntax for UPDATE. Instead, it uses 8 its own proprietary syntax (different again 7 from SQL Server's proprietary UPDATE syntax) which 6 is very limited. Often, the only workarounds 5 "Operation must use an updatable query" are 4 very painful. Seriously consider switching 3 to a more capable SQL product.

For some more 2 details about your specific problems and 1 some possible workarounds, see Update Query Based on Totals Query Fails.

I kept getting the same error until I made 3 the connecting field a unique index in both 2 connecting tables. Only then did the query 1 become updatable.

Philip Stilianos

I kept getting the same error, but all SQLs 4 execute in Access very well.

and when I amended the 3 permission of AccessFile.

the problem fixed!!

I give 2 'Network Service' account full control permission, this 1 account if for IIS

When I got this error, it may have been 6 because of my UPDATE syntax being wrong, but 5 after I fixed the update query I got the 4 same error again...so I went to the ODBC Data Source Administrator and 3 found that my connection was read-only. After 2 I made the connection read-write and re-connected 1 it worked just fine.

Today in my MS-Access 2003 with an ODBC 4 tabla pointing to a SQL Server 2000 with 3 sa password gave me the same error.
I defined 2 a Primary Key on the table in the SQL Server 1 database, and the issue was gone.

There is another scenario here that would 7 apply. A file that was checked out of Visual 6 Source Safe, for anyone still using it, that 5 was not given "Writeablity", either in the 4 View option or Check Out, will also recieve 3 this error message.

Solution is to re-acquire 2 the file from Source Safe and apply the 1 Writeability setting.

To further answer what DRUA referred to 18 in his/her answer...

I develop my databases 17 in Access 2007. My users are using access 16 2007 runtime. They have read permissions 15 to a database_Front (front end) folder, and 14 read/write permissions to the database_Back 13 folder.

In rolling out a new database, the 12 user did not follow the full instructions 11 of copying the front end to their computer, and 10 instead created a shortcut. Running the 9 Front-end through the shortcut will create 8 a condition where the query is not updateable 7 because of the file write restrictions.

Copying 6 the front end to their documents folder 5 solves the problem.

Yes, it complicates 4 things when the users have to get an updated 3 version of the front-end, but at least the 2 query works without having to resort to 1 temp tables and such.

check your DB (Database permission) and 4 give full permission

Go to DB folder-> right 3 click properties->security->edit-> give 2 full control & Start menu ->run->type 1 "uac" make it down (if it high)

The answer given above by iDevlop worked 11 for me. Note that I wasn't able to find 10 the RecordsetType property in my update 9 query. However, I was able to find that 8 property by changing my query to a select 7 query, setting that property as iDevlop 6 noted and then changing my query to an update 5 query. This worked, no need for a temp 4 table.

I'd have liked for this to just 3 be a comment to what iDevlop posted so that 2 it flowed from his solution, but I don't 1 have a high enough score.

I solved this by adding "DISTINCTROW"

so 1 here this would be

UPDATE DISTINCTROW CLOG SET CLOG.NEXTDUE