Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

vba - Operation must use an updatable query. (Error 3073)

I have written this query:

UPDATE tbl_stock1 SET 
tbl_stock1.weight1 = (
    select (b.weight1 - c.weight_in_gram) as temp 
    from
        tbl_stock1 as b,
        tbl_sales_item as c 
    where
        b.item_submodel_id = c.item_submodel_id 
        and b.item_submodel_id = tbl_stock1.item_submodel_id 
        and b.status <> 'D' 
        and c.status <> 'D'
    ), 
tbl_stock1.qty1 = (
    select (b.qty1 - c.qty) as temp1 
    from
        tbl_stock1 as b,
        tbl_sales_item as c 
    where 
        b.item_submodel_id = c.item_submodel_id 
        and b.item_submodel_id = tbl_stock1.item_submodel_id 
        and b.status <> 'D' 
        and c.status <> 'D'
    )
WHERE
    tbl_stock1.item_submodel_id = 'ISUBM/1' 
    and tbl_stock1.status <> 'D';

I got this error message:

Operation must use an updatable query. (Error 3073) Microsoft Access

But if I run the same query in SQL Server it will be executed.

Thanks, dinesh

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

I'm quite sure the JET DB Engine treats any query with a subquery as non-updateable. This is most likely the reason for the error and, thus, you'll need to rework the logic and avoid the subqueries.

As a test, you might also try to remove the calculation (the subtraction) being performed in each of the two subqueries. This calculation may not be playing nicely with the update as well.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...