"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?

Intake

Answer 1

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.

Intake

Ah, so [Views] was a saved query, was it? Did it by chance do any aggregations (GROUP BY) or anything else that would have made its result set "not updateable"? – Pillar

I thin kthat was the issue. There was a grouping and even though the results were fine, the grouping through of Access. You learn a little every day! – Intake

Is there no other way than creating a table in place of the select query? – Decretal

Answer 2

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.

Transmute

This is a legitimate cause of this kind of problem, but I don't think it's the most common cause. I think the issue with a primary key is a more common cause. When I ran into it myself, it was because I had linked to a aggregate query which effectively removed the primary key. I could potentially get around this by putting the aggregate query into a temporary table, like the question asker ended up doing in his situation. – Maori

permission issue was in my case – Delozier

Answer 3

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 <- 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.

Galling

If anyone can expound on this answer as to the reason why DISTINCTROW works (even three yrs later), then it would be appreciated. – Marlanamarlane

It's because DISTINCTROW omits the output from joined view (or table) when you select or wants to update only fields from one table. If you don't use it, it will retrieve the eventual duplicated records from the non-updateable view, rendering the entire UPDATE statement non-updateable. My guess is that MSAccess would see that as an attempt to update data from a view. See: support.office.com/en-gb/article/… – Eady

Answer 4

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.

Skell

Actually it helped me today! :) The point is that I had two absolutely identical tables, both without Primary Keys (don't ask me about it, I also have no clue about the reason) and with one of them it worked flawlessly, with the other one I had the error. – Deformation

Answer 5

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

Ovaritis

While correct, this doesn't really add anything new. – Sabotage

Actually this is the correct answer for me, everything was working fine until i published the app, the installer installed the app on C: drive, which removed write permission, i just enabled write permission on the installation folder and every thing worked as expected – Suctorial

Answer 6

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.

Morn

Answer 7

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

Hogwash

Answer 8

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

Josiejosler

Answer 9

This is a shot in the dark but try putting the two operands for the AND in parentheses

On ((A = B) And (C = D))

Elson

Unfortunately, no success – Intake

try this link it's a similiar issue to yours. Hope it works. #9894853 – Elson

There approach is quite different – Elson

Answer 10

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.

Clinometer

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Gilkey

@Gilkey This one actually suggests an approach and is a valid answer, however if it works or not to solve the issue, that is something else to consider. – Libove

@Gilkey Review I do not agree that it is not a valid answer. I had clearly similar issue to the one described and the solution I provided worked for me. I wanted to share my gain knowledge with others, as it may help to solve their issues as well. I do not see the logic, why it is not a valid answer, can you explain it to more detail? – Clinometer

Answer 11

You have to remove the IMEX=1 if you want to update. ;)

"IMEX=1; tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative." https://www.connectionstrings.com/excel/

Jiles

Answer 12

This problem can be solved very easily by following steps. I think it is due to security permission which can be solved like this.

Step 1 : open the properties by right clicking on the folder in which you have placed the database file.

Step 2 : In the box that opens, click the Security tab and click on CREATOR OWNER and then click OK.

Step 3 : In the box that opens, click CREATOR OWNER again and then click ADD button.

Step 4 : In the box that opens, type the Everyone in the text box and then click

Step 5 : After that click on Everyone and grant all permissions.

I hope your problem will be The problem will be solved.

Ancestor

Answer 13

UPDATE [GS] INNER JOIN [Views] ON 
([Views].Hostname = [GS].Hostname) 
AND ([GS].APPID = [Views].APPID) <------------ 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];

Furnivall