asked on

operation must be an updateable query

I have created a view on SQL server that is linked to my ms access db. I can open the linked view as a table and make edits on it without any issues.

I can run an update query and the data will be updated on SQL server. However, when i put my app on another user's computer and they do the same steps, the update fails. It gives me the "operation must be an updateable query".

What gives? Here is my query. It works on my box.

CurrentDb.Execute "update dbo_vwLoadDelivery set delivered = -1 " & _
                        ", deliverydt= '" & rs!LoadDeliveryDt & "'" & _
                        ", editedby = '" & Environ("username") & "'" & _
                        ", datelastedited = '" & Now() & "'" & _
                        " where orderid = '" & rs!OrderId & "'"

Open in new window

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

One possibility ... Is this Access 2010? If so, the DB must be in a Trusted Location, or ... functions like Environ() (and many others) will fail ...

mx

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lucas

ASKER

This is ms access 2007. The linked table points to a view on sql server. I can manually edit the data and i can run update query on the data. I put my ms access app on another computer and the person cannot run the query, but they can manually update the data.

Maybe i should try a pass-thru query?

Qlemo

"I have created a view on SQL server that is linked to my ms access db." - I read that such that the table is a Access one. Obviously not.
Is orderid a (unique) key? Are you certain both users see the same view and tables on MSSQL?

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lucas

ASKER

@Olemo, when i link the view to ms access it asks me what field is the PK. Orderid is the PK.

@Mx, yes i can open the view in access on the problem machine and edit the data.

@Jim, i link it and pick orderid as the PK. There is only one login for the sql server and that user has correct permissions on the tables / views.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lucas

ASKER

my view is based only on 1 table

SELECT OrderID, [Delivery Date] AS DeliveryDt, Delivered, EditedBy, DateLastEdited
FROM dbo.Orders

Lucas

ASKER

It was my fault. The view wasn't linked properly on the user's machine. I corrected it and now it works fine.

Thanks guys.