Operation must use an updateable query

[ajlheureux]

When I execute the following code:


DBAdapter_Global.DeleteCommand = New OdbcCommand("DELETE FROM [Users] WHERE UserInfo0 = '" & current_userid & "'", DBHandle)
Try
DBAdapter_Global.Update(DBDataSet_Users, "Users")
Catch
MsgBox(Err.Number & "" & Err.Description & "" & Err.LastDllError, MsgBoxStyle.Critical, "MEK Administrator")
DBDataSet_Users.RejectChanges()
Return False
End Try


I am getting the error: "Operation must use an updateable query".

I have two users on my computer, one Administrator and one User account.

The Administrator account can do this command with no issues.

The User account is getting the "Operation must use an updateable query" however is actually modifies the database.

I have checked the Security permissions on my .mdb file as well as the folder which contains the .mdb file and they are the same. Actually I have given Full Control to both users. If I make the User account a member of the Administrators group he no longer has the error but I can't leave it this way.

Is there any permissions besides the .mdb file and the folder which contains the .mdb file that would cause this error. Any thoughts would be greatly appreciated.


I have resolved the problem. In the background of the code it was writing to another database which did not have Write Access. Doh!!!

Amy

[cogitoergosum]

I have the same problem. I use access 2003, i controlled the permissions but there is no problem about them.

Here is the query (simplified)

UPDATE TBL2

INNER JOIN
(
SELECT CUSTOMERID, COUNT(PRODUCTNO) AS NUMBERofPRODUCTS FROM TBL2 GROUP BY CUSTOMER ID
) AS TBL1 ON
TBL1.CUSTOMERID = TBL2.CUSTOMERID

SET TBL2.QUANTITY = TBL2.QUANTITY / TBL1.NUMBERofPRODUCTS

Both TBL1 and TBL2 have the same numbers of rows.
Can anyone help?

[hspc]

this error is usually not related to the table structure
MS access needs to write to *.ldb file (lock file) when running a query that makes a change to data. (update , insert , delete)
make sure that the user has permissions to create the files in the same folder where the mdb resides.

[cogitoergosum]

i'm sure that the user has permissions to create the files in the same folder where the mdb resides :)

[hspc]

Is it a web or a desktop application ?

[cogitoergosum]

i use windows forms in vb.net 2003

[hspc]

try to change the query to :
UPDATE TBL2
SET TBL2.QUANTITY = TBL2.QUANTITY / (Select COUNT(*) from TBL2 as t2 where t2.CUSTOMERID=TBL2.CUSTOMERID)
this syntax is ok in sql server..not sure about MS Access

[Krzemo]

Reasons for error:

1) Database (.mdb file) is read only (read only attribute on file or only "read" permission on file). In cases of linked tables U should also check linked database.

2) User is not permited to create files in directory (hspc writes about it before). Check if that user can create any file in the same directory as ".mdb" file . In cases of linked tables U should also check linked database.

3) Query is not updateable sometimes when there is no primarykey/ unique key defined on table (is not bookmarkable).

4) The syntax of query looks suspicious. IMHO it should be as hspc writes or looks that way:

UPDATE tb1
SET tb1=tb1.a/tb2.b
FROM tb1
INNER JOIN tb2 ON tb1.key=tb2.key



Best regards,
Krzemo.

[cogitoergosum]

I used a temporary table for the inner sql..then i updated the table with temporary one..
i couldn't find a solution for the "Operation must use an updateable query" :( and i preferred temp table.