Operation must use an updatable query Error 3073 Microsoft Access

Question

On some Microsoft Access queries  I get the following message   Operation must use an updatable query   Error 3073    I work around it by using temporary tables  but I m wondering if there s a better way   All the tables involved have a primary key  Here s the code   UPDATE CLOG SET CLOG NEXTDUE         SELECT H1 paidthru      FROM CTRHIST as H1     WHERE H1 ACCT   clog ACCT AND     H1 SEQNO             SELECT MAX SEQNO           FROM CTRHIST          WHERE CTRHIST ACCT   Clog ACCT AND          CTRHIST AMTPAID  gt  0 AND         CTRHIST DATEPAID  lt  CLOG UPDATED ON         WHERE CLOG NEXTDUE IS NULL

User · Answer

In essence  while your SQL looks perfectly reasonable  Jet has never supported the SQL standard syntax for UPDATE  Instead  it uses its own proprietary syntax  different again from SQL Server s proprietary UPDATE syntax  which is very limited  Often  the only workarounds  Operation must use an updatable query  are very painful  Seriously consider switching to a more capable SQL product   For some more details about your specific problems and some possible workarounds  see Update Query Based on Totals Query Fails

User · Answer

This occurs when there is not a UNIQUE MS-ACCESS key for the table s  being updated   Regardless of the SQL schema    When creating MS-Access Links to SQL tables  you are asked to specify the index  key  at link time   If this is done incorrectly  or not at all  the query against the linked table is not updatable  When linking SQL tables into Access MAKE SURE that when Access prompts you for the index  key  you use exactly what SQL uses to avoid problem s   although specifying any unique key is all Access needs to update the table   If you were not the person who originally linked the table  delete the linked table from MS-ACCESS  the link only gets deleted  and re-link it specifying the key properly and all will work correctly

User · Answer

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

User · Answer

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

User · Answer

In essence  while your SQL looks perfectly reasonable  Jet has never supported the SQL standard syntax for UPDATE  Instead  it uses its own proprietary syntax  different again from SQL Server s proprietary UPDATE syntax  which is very limited  Often  the only workarounds  Operation must use an updatable query  are very painful  Seriously consider switching to a more capable SQL product   For some more details about your specific problems and some possible workarounds  see Update Query Based on Totals Query Fails

User · Answer

Since Jet 4  all queries that have a join to a SQL statement that summarizes data will be non-updatable  You aren t using a JOIN  but the WHERE clause is exactly equivalent to a join  and thus  the Jet query optimizer treats it the same way it treats a join   I m afraid you re out of luck without a temp table  though maybe somebody with greater Jet SQL knowledge than I can come up with a workaround   BTW  it might have been updatable in Jet 3 5  Access 97   as a whole lot of queries were updatable then that became non-updatable when upgraded to Jet 4   --

User · Answer

I kept getting the same error until I made the connecting field a unique index in both connecting tables  Only then did the query become updatable   Philip Stilianos

User · Answer

The answer given above by iDevlop worked for me    Note that I wasn t able to find the RecordsetType property in my update query  However  I was able to find that property by changing my query to a select query  setting that property as iDevlop noted and then changing my query to an update query    This worked  no need for a temp table     I d have liked for this to just be a comment to what iDevlop posted so that it flowed from his solution  but I don t have a high enough score

User · Answer

There is no error in the code  But the error 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 you are stored the database  MS-Access file  in read only folder  while running your application the connection is not force-fully opened  Hence change the file permission   its containing folder permission like in C  Program files all most all c drive files been set read-only so changing this permission solves this Problem

User · Answer

I had a similar problem where the following 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 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 proposed at https   stackoverflow com questions 537161 sql-update-woes-in-ms-access-operation-must-use-an-updateable-query

User · Answer

I kept getting the same error until I made the connecting field a unique index in both connecting tables  Only then did the query become updatable   Philip Stilianos

User · Answer

A little late to the party      The three ways I ve gotten around this problem in the past are    Reference a text box on an open form DSum DLookup

User · Answer

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

User · Answer

Since Jet 4  all queries that have a join to a SQL statement that summarizes data will be non-updatable  You aren t using a JOIN  but the WHERE clause is exactly equivalent to a join  and thus  the Jet query optimizer treats it the same way it treats a join   I m afraid you re out of luck without a temp table  though maybe somebody with greater Jet SQL knowledge than I can come up with a workaround   BTW  it might have been updatable in Jet 3 5  Access 97   as a whole lot of queries were updatable then that became non-updatable when upgraded to Jet 4   --

User · Answer

The problem defintely relates to the use of  in this case  the max   function  Any aggregation function used during a join  e g  to retrieve the max or min or avg value from a joined table  will cause the error  And the same applies to using subqueries instead of joins  as in the original code    This is incredibly annoying  and unjustified   as it is a reasonably common thing to want to do  I ve also had to use temp tables to get around it  pull the aggregated value into a temp table with an insert statement  then join to this table with your update  then drop the temp table    Glenn

User · Answer

I kept getting the same error  but all SQLs execute in Access very well   and when I amended the permission of AccessFile   the problem fixed    I give  Network Service  account full control permission  this account if for IIS

User · Answer

I would try building the UPDATE query in Access  I had an UPDATE query I wrote myself like  UPDATE TABLE1 SET Field1     SELECT Table2 Field2  FROM Table2  WHERE Table2 UniqueIDColumn   Table1 UniqueIDColumn    The query gave me that error you re seeing  This worked on my SQL Server though  but just like earlier answers noted  Access UPDATE syntax isn t standard syntax  However  when I rebuilt it using Access s query wizard  it used the JOIN syntax  it worked fine  Normally I d just make the UPDATE query a passthrough to use the non-JET syntax  but one of the tables I was joining with was a local Access table

User · Answer

I would try building the UPDATE query in Access  I had an UPDATE query I wrote myself like  UPDATE TABLE1 SET Field1     SELECT Table2 Field2  FROM Table2  WHERE Table2 UniqueIDColumn   Table1 UniqueIDColumn    The query gave me that error you re seeing  This worked on my SQL Server though  but just like earlier answers noted  Access UPDATE syntax isn t standard syntax  However  when I rebuilt it using Access s query wizard  it used the JOIN syntax  it worked fine  Normally I d just make the UPDATE query a passthrough to use the non-JET syntax  but one of the tables I was joining with was a local Access table

User · Answer

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

User · Answer

I know my answer is 7 years late  but here s my suggestion anyway     When Access complains about an UPDATE query that includes a JOIN  just save the query  with RecordsetType property set to Dynaset  Inconsistent Updates      This will sometimes allow the UPDATE to work

User · Answer

check your DB  Database permission  and give full permission  Go to DB folder-  right click properties- security- edit-  give full control   amp  Start menu - run- type  uac    make it down  if it high

User · Answer

There is another scenario here that would apply  A file that was checked out of Visual Source Safe  for anyone still using it  that was not given  Writeablity   either in the View option or Check Out  will also recieve this error message   Solution is to re-acquire the file from Source Safe and apply the Writeability setting

User · Answer

A little late to the party      The three ways I ve gotten around this problem in the past are    Reference a text box on an open form DSum DLookup

User · Answer

I kept getting the same error until I made the connecting field a unique index in both connecting tables  Only then did the query become updatable   Philip Stilianos

User · Answer

I had a similar problem where the following 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 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 proposed at https   stackoverflow com questions 537161 sql-update-woes-in-ms-access-operation-must-use-an-updateable-query

User · Answer

I know my answer is 7 years late  but here s my suggestion anyway     When Access complains about an UPDATE query that includes a JOIN  just save the query  with RecordsetType property set to Dynaset  Inconsistent Updates      This will sometimes allow the UPDATE to work

User · Answer

To further answer what DRUA referred to in his her answer     I develop  my databases in Access 2007   My users are using access 2007 runtime   They have read permissions to a database Front  front end  folder  and read write permissions to the database Back folder    In rolling out a new database  the user did not follow the full instructions of copying the front end to their computer  and instead created a shortcut    Running the Front-end through the shortcut will create a condition where the query is not updateable because of the file write restrictions     Copying the front end to their documents folder solves the problem     Yes  it complicates things when the users have to get an updated version of the front-end  but at least the query works without having to resort to temp tables and such

User · Answer

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

User · Answer

MS Access - joining tables in an update query    how to make it updatable   Open the query in design view Click once on the link b w tables view In the    properties    window  change the value for    unique records    to    yes    Save the query as an update query and run it

User · Answer

The problem defintely relates to the use of  in this case  the max   function  Any aggregation function used during a join  e g  to retrieve the max or min or avg value from a joined table  will cause the error  And the same applies to using subqueries instead of joins  as in the original code    This is incredibly annoying  and unjustified   as it is a reasonably common thing to want to do  I ve also had to use temp tables to get around it  pull the aggregated value into a temp table with an insert statement  then join to this table with your update  then drop the temp table    Glenn

User · Answer

To further answer what DRUA referred to in his her answer     I develop  my databases in Access 2007   My users are using access 2007 runtime   They have read permissions to a database Front  front end  folder  and read write permissions to the database Back folder    In rolling out a new database  the user did not follow the full instructions of copying the front end to their computer  and instead created a shortcut    Running the Front-end through the shortcut will create a condition where the query is not updateable because of the file write restrictions     Copying the front end to their documents folder solves the problem     Yes  it complicates things when the users have to get an updated version of the front-end  but at least the query works without having to resort to temp tables and such

User · Answer

In essence  while your SQL looks perfectly reasonable  Jet has never supported the SQL standard syntax for UPDATE  Instead  it uses its own proprietary syntax  different again from SQL Server s proprietary UPDATE syntax  which is very limited  Often  the only workarounds  Operation must use an updatable query  are very painful  Seriously consider switching to a more capable SQL product   For some more details about your specific problems and some possible workarounds  see Update Query Based on Totals Query Fails

User · Answer

The problem defintely relates to the use of  in this case  the max   function  Any aggregation function used during a join  e g  to retrieve the max or min or avg value from a joined table  will cause the error  And the same applies to using subqueries instead of joins  as in the original code    This is incredibly annoying  and unjustified   as it is a reasonably common thing to want to do  I ve also had to use temp tables to get around it  pull the aggregated value into a temp table with an insert statement  then join to this table with your update  then drop the temp table    Glenn

User · Answer

You can always write the code in VBA that updates similarly  I had this problem too  and my workaround was making a select query  with all the joins  that had all the data I was looking for to be able to update  making that a recordset and running the update query repeatedly as an update query of only the updating table  only searching the criteria 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      amp  clientName  amp     WHERE     TABLE  ID     amp  tableID  amp               updatingItems MoveNext     Loop   I m only doing this to about 60 records a day  doing it to a few thousand could take much longer  as the query is running from start to finish multiple times  instead of just selecting an overall group and making changes  You might need     around the quotes for tableID  as it s a string  but I m pretty sure this is what worked for me

User · Answer

You can always write the code in VBA that updates similarly  I had this problem too  and my workaround was making a select query  with all the joins  that had all the data I was looking for to be able to update  making that a recordset and running the update query repeatedly as an update query of only the updating table  only searching the criteria 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      amp  clientName  amp     WHERE     TABLE  ID     amp  tableID  amp               updatingItems MoveNext     Loop   I m only doing this to about 60 records a day  doing it to a few thousand could take much longer  as the query is running from start to finish multiple times  instead of just selecting an overall group and making changes  You might need     around the quotes for tableID  as it s a string  but I m pretty sure this is what worked for me

User · Answer

This occurs when there is not a UNIQUE MS-ACCESS key for the table s  being updated   Regardless of the SQL schema    When creating MS-Access Links to SQL tables  you are asked to specify the index  key  at link time   If this is done incorrectly  or not at all  the query against the linked table is not updatable  When linking SQL tables into Access MAKE SURE that when Access prompts you for the index  key  you use exactly what SQL uses to avoid problem s   although specifying any unique key is all Access needs to update the table   If you were not the person who originally linked the table  delete the linked table from MS-ACCESS  the link only gets deleted  and re-link it specifying the key properly and all will work correctly

User · Answer

I had the same issue   My solution is to first create a table from the non updatable query and then do the update from table to table and it works

User · Answer

In essence  while your SQL looks perfectly reasonable  Jet has never supported the SQL standard syntax for UPDATE  Instead  it uses its own proprietary syntax  different again from SQL Server s proprietary UPDATE syntax  which is very limited  Often  the only workarounds  Operation must use an updatable query  are very painful  Seriously consider switching to a more capable SQL product   For some more details about your specific problems and some possible workarounds  see Update Query Based on Totals Query Fails

User · Answer

I had the same issue   My solution is to first create a table from the non updatable query and then do the update from table to table and it works

User · Answer

Since Jet 4  all queries that have a join to a SQL statement that summarizes data will be non-updatable  You aren t using a JOIN  but the WHERE clause is exactly equivalent to a join  and thus  the Jet query optimizer treats it the same way it treats a join   I m afraid you re out of luck without a temp table  though maybe somebody with greater Jet SQL knowledge than I can come up with a workaround   BTW  it might have been updatable in Jet 3 5  Access 97   as a whole lot of queries were updatable then that became non-updatable when upgraded to Jet 4   --

User · Answer

check your DB  Database permission  and give full permission  Go to DB folder-  right click properties- security- edit-  give full control   amp  Start menu - run- type  uac    make it down  if it high

User · Answer

I kept getting the same error  but all SQLs execute in Access very well   and when I amended the permission of AccessFile   the problem fixed    I give  Network Service  account full control permission  this account if for IIS

User · Answer

Since Jet 4  all queries that have a join to a SQL statement that summarizes data will be non-updatable  You aren t using a JOIN  but the WHERE clause is exactly equivalent to a join  and thus  the Jet query optimizer treats it the same way it treats a join   I m afraid you re out of luck without a temp table  though maybe somebody with greater Jet SQL knowledge than I can come up with a workaround   BTW  it might have been updatable in Jet 3 5  Access 97   as a whole lot of queries were updatable then that became non-updatable when upgraded to Jet 4   --

User · Answer

The problem defintely relates to the use of  in this case  the max   function  Any aggregation function used during a join  e g  to retrieve the max or min or avg value from a joined table  will cause the error  And the same applies to using subqueries instead of joins  as in the original code    This is incredibly annoying  and unjustified   as it is a reasonably common thing to want to do  I ve also had to use temp tables to get around it  pull the aggregated value into a temp table with an insert statement  then join to this table with your update  then drop the temp table    Glenn

User · Answer

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

User · Answer

There is another scenario here that would apply  A file that was checked out of Visual Source Safe  for anyone still using it  that was not given  Writeablity   either in the View option or Check Out  will also recieve this error message   Solution is to re-acquire the file from Source Safe and apply the Writeability setting

User · Answer

MS Access - joining tables in an update query    how to make it updatable   Open the query in design view Click once on the link b w tables view In the    properties    window  change the value for    unique records    to    yes    Save the query as an update query and run it

User · Answer

The answer given above by iDevlop worked for me    Note that I wasn t able to find the RecordsetType property in my update query  However  I was able to find that property by changing my query to a select query  setting that property as iDevlop noted and then changing my query to an update query    This worked  no need for a temp table     I d have liked for this to just be a comment to what iDevlop posted so that it flowed from his solution  but I don t have a high enough score

User · Answer

I kept getting the same error until I made the connecting field a unique index in both connecting tables  Only then did the query become updatable   Philip Stilianos

User · Answer

There is no error in the code  But the error 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 you are stored the database  MS-Access file  in read only folder  while running your application the connection is not force-fully opened  Hence change the file permission   its containing folder permission like in C  Program files all most all c drive files been set read-only so changing this permission solves this Problem

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

Examples related to ms-access