using pass-through query to update table

[bclmx]

[FONT=&quot]Hey All,

I need a little help with one of my update queries.
Pretty much all I want to do is update one table (in Access 2007) based on the results from my pass-through query that pulls data from SQL Server.

Please look at my update query below:

[/FONT][FONT=&quot]UPDATE
[SQL (2) Vendor cost export]
INNER JOIN
[Product Cost]
ON
([SQL (2) Vendor cost export].[Item id] = [Product Cost].Product_Id)
AND
([SQL (2) Vendor cost export].[Vendor id] = [Product Cost].Pharmacy)
SET [Product Cost].[Phamracy Product ID] = [SQL (2) Vendor cost export]![Vndr item id], [Product Cost].Cost = [SQL (2) Vendor cost export]![Std cost];

[SQL (2) Vendor cost export] is the pass through query that pulls data from SQL server and Product Cost is the table I am trying to update based on the results from pass-through query.

Access gives me an error message: "Operation must use an updatable query" when I try to run this.

Can you see any errors? Any help would be greatly appreciated.

Thanks

[/FONT]

 

[boblarson]

Change this part:

UPDATE
[SQL (2) Vendor cost export]

to this:

UPDATE
[Product Cost]

 

[bclmx]

Change this part:

UPDATE
[SQL (2) Vendor cost export]

to this:

UPDATE
[Product Cost]

[FONT=&quot]I see where you're heading but this is just a part of inner join. In T-SQL the order of the tables is irrelevant unless many tables are involved in the query like (10-15). BTW id did try it anyway, no luck though.[/FONT]

 

[boblarson]

[FONT=&quot]I see where you're heading but this is just a part of inner join. In T-SQL the order of the tables is irrelevant unless many tables are involved in the query like (10-15). BTW id did try it anyway, no luck though.[/FONT]

No, you don't see what I'm getting at. Your statement is saying you want to update the SQL table when in fact you are trying to update the other table. The stuff is all backwards. Access does that if you don't construct your Update queries carefully in the QBE grid, which is what I'm guessing you did.

So, saying UPDATE TABLEX is important if you are really trying to update TABLEY instead.

 

[MSAccessRookie]

I have two observations for you to consider:

1. I would like to expand on what boblarson said and indicate that since you are only updating the Table [Product Cost], that I would make it the first in the Join and the Join Links as well (See Example Below).
2. You are using special characters [FONT=&quot]("[", " ", "(", ")", and "]") that MS Access considers accptable, but other versions of SQL can find unfriendly. It is advisable not to use names with special characters.[/FONT]

[FONT=Courier New][COLOR=green][B]UPDATE [Product Cost] [/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]INNER JOIN [SQL (2) Vendor cost export] [/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]   ON ([Product Cost].Product_Id = [SQL (2) Vendor cost export].[Item id])[/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]   AND ([Product Cost].Pharmacy = [SQL (2) Vendor cost export].[Vendor id]) [/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]SET [Product Cost].[Phamracy Product ID] = [/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]       [SQL (2) Vendor cost export]![Vndr item id], [/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]   [Product Cost].Cost = [/B][/COLOR][/FONT]
[FONT=Courier New][COLOR=green][B]       [SQL (2) Vendor cost export]![Std cost]; [/B][/COLOR][/FONT]

 

[boblarson]

Also, there is a typo (possibly) -

SET [Product Cost].[Phamracy Product ID]

is that supposed to be Pharmacy and not Phamracy?