• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

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

0
Lucas
Asked:
Lucas
  • 4
  • 2
  • 2
  • +2
4 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
QlemoC++ DeveloperCommented:
Did you make sure the view is exactly the same, and pointing to exactly the same Access DB and table?
0
 
LucasAuthor Commented:
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?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
QlemoC++ DeveloperCommented:
"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?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" I can manually edit the data and i can run update query on the data. "
And you can do that on the system with a problem?  Also, Trusted Locations were present in A2007.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

1. When the table is linked, make sure you select fields that will provide a unique index.

2. Make sure the user has the appropriate security in SQL Server for the view.

Jim.
0
 
LucasAuthor Commented:
@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.
0
 
Anthony PerkinsCommented:
Please post the contents of the SQL Server VIEW.
0
 
LucasAuthor Commented:
my view is based only on 1 table

SELECT     OrderID, [Delivery Date] AS DeliveryDt, Delivered, EditedBy, DateLastEdited
FROM         dbo.Orders
0
 
LucasAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now