Updating Excel via Access error

[vanessa03]

I want to update a linked excel file in Access with a field from a linked SQL table. This is the query.
UPDATE BudgetUpload INNER JOIN dbo_IMITMIDX_SQL ON BudgetUpload.[Item No] = dbo_IMITMIDX_SQL.item_no
SET BudgetUpload.[Prod Cat] = dbo_IMITMIDX_SQL.prod_cat;

I get the error message - Cannot update'(expression)'; field not updateable.

Any help would be greatly appreciated. Thanks.

 

[Remou]

A bit if a guess - have you got a unique index on either table?

 

[vanessa03]

The IMITMIDX_SQL has an identity index. But the excel file doesn't have one.

 

[Remou]

Apparently it can no longer be done:

http://groups.google.ie/group/micro...nked+excel+table+from+access#7939bf2fd3b0b108

However, it should be easy enough to do this through ADO.

 

[Golom]

And here's the Official Microsoft version.

 

[PWise]

instead of a linked table use this syntex

UPDATE [Excel 8.0;IMEX=0;DATABASE=filepathandname].[workshertname$] AS BudgetUpload  
INNER JOIN dbo_IMITMIDX_SQL ON BudgetUpload.[Item No] = dbo_IMITMIDX_SQL.item_no 
SET BudgetUpload.[Prod Cat] = dbo_IMITMIDX_SQL.prod_cat;

 

[vanessa03]

Thanks to all!