Operation must be updateable Query

[venkatesh2j]

Hi Guys,
I just made a ODBC connection from MS Access (2007) to PostGre server, however i came across some strange error "Operation must be updateable Query" for an simple Update statement. I have table called Geo it has a column Zone having values North,South,West & East.
Table: Geo
Zone
North
East
West
South

i want to update these values with 1,2,3 & 4 like
Update Geo set Zone=1 where Zone='North'
It's not working showing the same error.
I have created table in postGre under a schema Public and later linked it to Access thru ODBC connection.

[venkatesh2j]

Hi,
I have linked MS access with PostGreSQL DB through ODBC connection. Issue i am facing is when i make a link table in MS access from postGre i am not able update any record using update statement. A wierd thing i observed that while i was making the link table after selecting the tables from the list it was asking me to choose column which is unique in that table if i wont select anything, table will be linked but it will be not updateable it throws error: "Operation must be an updateable Query" though update statement's syntax was correct but when i selected anyone Unique column i was able to run Update Query, So why postGre asks an unique column in a table and is there any way where we can without specifying unique column name we can run update statement successfully

[NeoPa]

Please try to ensure you have all relevant information before posting. Finding out later that the question is no good and that no-one will answer it is not a good reason to post a new question. In fact we don't allow it here (New questions are fine but repeats are not - even if worded differently or with new information) as it causes too many problems.

Anyway...

I haven't used PostGreSQL myself, but I've used some ODBC links.

My best guess is that when you set up your link to the BE it needs that extra information to properly link up with the table. BE dbs are generally more fussy about geting a unique PK to work with. I find it quite believable that without that information the link can only be set up as R/O. It's unfortunate that the drivers appear to require you to specify that information when you make the original connection, but it's not unique. I've come across that before with other ODBC drivers.

For a better understanding you could try updating the data in the linked table directly. I expect you'll find that the data itself is non-updatable and this is not related to the query as such.