Operation must use an updateable query error in MS Access

Question

I am getting an error message   Operation must use an updateable query  when I try to run my SQL  From my understanding  this happens when joins are used in update delete queries in MS Access  However  I m a little confused because I have another query almost identical in my database which works fine   This is my troublesome query   UPDATE  GS  INNER JOIN  Views  ON        Views  Hostname    GS  Hostname       AND   GS  APPID    Views  APPID       SET           GS  APPID    Views  APPID            GS   Name     Views   Name             GS  Hostname    Views  Hostname            GS   Date     Views   Date             GS   Unit     Views   Unit             GS   Owner     Views   Owner     As I said before  I am confused because I have another query similar to this  which runs perfectly  This is that query   UPDATE  Views  INNER JOIN  GS  ON   Views  APPID    GS  APPID  SET       GS  APPID    Views  APPID        GS   Name     Views   Name         GS   Criticial      Views   Criticial          GS   Unit     Views   Unit         GS   Owner     Views   Owner     What is wrong with my first query  Why does the second query work when the first doesn t

User · Accepted Answer

Whether this answer is universally true or not, I don't know, but I solved this by altering my query slightly.

Rather than joining a select query to a table and processing it, I changed the select query to create a temporary table. I then used that temporary table to the real table and it all worked perfectly.

User · Answer

I had the same problem exactly  and I can t remember how I fond this solution but simply adding DISTINCTROW solved the problem   In your code it will look like this   UPDATE DISTINCTROW  GS  INNER JOIN  Views  ON  lt - the only change is here       Views  Hostname    GS  Hostname       AND   GS  APPID    Views  APPID          I m not sure why this works  but for me  it did exactly what I needed

User · Answer

UPDATE  GS  INNER JOIN  Views  ON    Views  Hostname    GS  Hostname   AND   GS  APPID    Views  APPID   lt ------------ This is the difference  SET       GS  APPID    Views  APPID        GS   Name     Views   Name         GS  Hostname    Views  Hostname        GS   Date     Views   Date         GS   Unit     Views   Unit         GS   Owner     Views   Owner

User · Answer

This is a shot in the dark but try putting the two operands for the AND in parentheses  On   A   B  And  C   D

User · Answer

I had the same error when was trying to update linked table    The issue was that linked table had no PRIMARY KEY    After adding primary key constraint on database side and re linking this table to access problem was solved    Hope it will help somebody

User · Answer

There is no error in the code  but the error is thrown due to the following    - Please check whether 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

You have to remove the IMEX 1 if you want to update      quot IMEX 1  tells the driver to always read  quot intermixed quot   numbers  dates  strings etc  data columns as text  Note that this option might affect excel sheet write access negative  quot  https   www connectionstrings com excel

User · Answer

set permission on application directory solve this issue with me  To set this permission  right click on the App Data folder  or whichever other folder you have put the file in  and select Properties  Look for the Security tab  If you can t see it  you need to go to My Computer  then click Tools and choose Folder Options     then click the View tab  Scroll to the bottom and uncheck  Use simple file sharing  recommended    Back to the Security tab  you need to add the relevant account to the Group or User Names box  Click Add     then click Advanced  then Find Now  The appropriate account should be listed  Double click it to add it to the Group or User Names box  then check the Modify option in the permissions  That s it  You are done

User · Answer

I got this same error and using a primary key did not make a difference  The issue was that the table is a linked Excel table  I know there are settings to change this but my IT department has locked this so we cant change it  Instead  I created a make table from the linked table and used that instead in my Update Query and it worked  Note  any queries in your query that are also linked to the same Excel linked table will cause the same error so you will need to change these as well so they are not directly linked to the Excel linked table  HTH

User · Answer

To update records  you need to write changes to  mdb file on disk  If your web shared application can t write to disk  you can t update existing or add new records  So  enable read write access in database folder or move database to other folder where your application has write permission    for more detail please check    http   www beansoftware com ASP NET-FAQ Operation-Must-Use-An-Updateable-Query aspx

User · Answer

I used a temp table and finally got this to work  Here is the logic that is used once you create the temp table   UPDATE your table  temp SET your table value   temp value WHERE your table id   temp id

User · Answer

I was accessing the database using UNC path and occasionally this exception was thrown  When I replaced the computer name with IP address  the problem was suddenly resolved

[ms-access] "Operation must use an updateable query" error in MS Access

Examples related to ms-access