"Operation must use updateable query" in updates, backend DB would be fine with it

[Jeff Kowalczyk]

I'm having trouble using Access 2002 to update a table with records
from a summary query/select. Access complains about "Operation must use
updateable query". I've executed this type of query against the back-end
database's SQL console, and it works fine.

UPDATE orders
SET orderchargeasbilled =
(SELECT SUM(orderchargeasbilled)
FROM ordercharges
WHERE orders.orderid = ordercharges.orderid)
WHERE orderdate > #12/1/2003#

What can I do to suppress the "Operation must use updateable query"
error without resorting to a pass-through query? (I'm finding
it difficult to persist the connection info for pass-throughs)

Thanks.

This particular setup uses linked tables (postgres, but I
believe it would be the same were it sql server)

 

[John Vinson]

UPDATE orders
SET orderchargeasbilled =
(SELECT SUM(orderchargeasbilled)
FROM ordercharges
WHERE orders.orderid = ordercharges.orderid)
WHERE orderdate > #12/1/2003#

What can I do to suppress the "Operation must use updateable query"
error without resorting to a pass-through query?

JET, unlike SQL, flags all queries with any Totals operator whatsoever
as non-updateable (even when, as in this case, there's no logical
reason to do so).

The getaround is to use the DSum() Function to generate the sum:

UPDATE orders
SET orderchargeasbilled =
DSum("[orderchargeasbilled]", "[ordercharges]", "orders.orderid =" &
ordercharges.orderid)
WHERE orderdate > #12/1/2003#

 

[Jeff Kowalczyk]

JET, unlike SQL, flags all queries with any Totals operator whatsoever
as non-updateable (even when, as in this case, there's no logical
reason to do so). The getaround is to use the DSum() Function to
generate the sum:

Thanks, John. The DSUM approach is working, but I have a datatype
conversion error to overcome in the result. Here's the current query:

UPDATE orders SET
orders.customerchargeasbilled =
CDBL(FORMATNUMBER(DSUM("[orders]![customerchargeasbilled]",
"[ordercharges]![orderchargeasbilled]",
"[ordercharges]![orderid]=" & orders.orderid),2))
WHERE customerinvoiceid="99999"

Which indicates that it will be updating the proper two test rows, but
fails at the update commit with a datatype conversion error (DSUM returns
a variant). The [orders].[customerchargeasbilled] field is in a postgresql
backend (I've only seen this problem since trying DSUM for this query),
with the following ODBC linked table settings:

DataType Number
Field Size Decimal
Format Standard
Precision 18
Scale 4

The CDBL() and FORMATNUMBER() calls are just attempts to workaround the
error, but I get the same datatype conversion error for all three
combinations.

Any ideas? Thanks.

 

[John Vinson]

UPDATE orders SET
orders.customerchargeasbilled =
CDBL(FORMATNUMBER(DSUM("[orders]![customerchargeasbilled]",
"[ordercharges]![orderchargeasbilled]",
"[ordercharges]![orderid]=" & orders.orderid),2))
WHERE customerinvoiceid="99999"

I'm not familiar with postgresql, so this may not be the only issue -
but your DSum is improperly structured. The first argument should be a
text string containing only the name of the field to sum; the second
should be the name of the domain - which I'd guess should be a stored
Query joining the Orders table to the OrderCharges table; and the
third should be the WHERE clause, and would be ok if you changed the !
to a period.

 

[Jeff Kowalczyk]

UPDATE orders SET
orders.customerchargeasbilled =
CDBL(FORMATNUMBER(DSUM("[orders]![customerchargeasbilled]",
"[ordercharges]![orderchargeasbilled]",
"[ordercharges]![orderid]=" & orders.orderid),2))
WHERE customerinvoiceid="99999"

I'm not familiar with postgresql, so this may not be the only issue -
but your DSum is improperly structured. The first argument should be a
text string containing only the name of the field to sum; the second
should be the name of the domain - which I'd guess should be a stored
Query joining the Orders table to the OrderCharges table; and the
third should be the WHERE clause, and would be ok if you changed the !
to a period.

I don't think it has to do with the postgresql backend, I changed the
update to a select and the DSUM return value is #Error. I guess I'm not
understanding DSUM domains then.

I have two tables, orders and ordercharges. They share a common field
orderid, which is the primary key of orders. In queries displaying the
orders datasheet view, the ordercharges table is usually the subdatasheet.

The query is intended to sum all the line item charges in ordercharges and
update customerchargeasbilled with the total, which will in turn become a
line item on a customerinvoice.

orders:
+-orderid
| customerinvoiceid (used to batch-bill multiple orders to customer)
| customerchargeasbilled --> SUM orderchargeasbilled for this
| (...)
|
| ordercharges:
| orderchargeid
+---orderid
orderchargeasbilled
(...)

I hope I can get DSUM to work for this purpose, in order to use
parameters in the non-dsum WHERE clause. As mentioned
previously, Postgresql and SQL server can both handle this query with
subselects where access complains about updateability (because of the
SUM), so there's always the escape hatch of using DAO code to build a
passthrough SQL string. Any advice on using DSUM in this scenario would be
greatly appreciated. Thanks.

 

[John Vinson]

I have two tables, orders and ordercharges. They share a common field
orderid, which is the primary key of orders. In queries displaying the
orders datasheet view, the ordercharges table is usually the subdatasheet.

In that case you must create a Query linking orders and ordercharges,
save it under a query name, and use that name as the Domain.

 

[Zeppo]

See the recent thread:
Sidney Linkers
Re: concatanate records
Friday, October 17, 2003 8:04 AM

for how to set up the Update statement to
simulate aggregate functions,ie. sum.
without using an aggregate in a subquery.

For native crosstabs and more on Server 2000 check
out RAC.Other utilities too.
www.rac4sql.net

 

[Jeff Kowalczyk]

See the recent thread:
Sidney Linkers
Re: concatanate records
Friday, October 17, 2003 8:04 AM
for how to set up the Update statement to
simulate aggregate functions,ie. sum.
without using an aggregate in a subquery.

I'm very much interested in this technique, Zeppo. I'm having a little
trouble visualizing the syntax of what you're describing, specifically
what the update's SET clause would be. Can you illustrate with an example,
if not for my specific schema then for something similar of your choosing?
Thanks.

---------------------

The query is intended to sum all the line item charges in ordercharges and
update customerchargeasbilled with the total, which will in turn become a
line item on a customerinvoice.

orders:
+-orderid
| customerinvoiceid (used to batch-bill multiple orders to customer)
| customerchargeasbilled --> SUM orderchargeasbilled for this
| (...)
|
| ordercharges:
| orderchargeid
+---orderid
orderchargeasbilled
(...)

--------------------

Think about Update where you can update a column
multiple times.
Create a new column in original table (alltext).
Create a table with distinct RecID's.Then join it to
original table by RecID.The alltext column will be
updated multiple times for each RecID resulting
in a concatenated list. Easy and fast.
This same technique can be used to update column(s)
with aggregate functions (sum,count...) where a subquery
would usually be used in set but is illegal in Access.
This is *the* workaround for that silliness

-----------------------

 

[Jeff Kowalczyk]

In that case you must create a Query linking orders and ordercharges,
save it under a query name, and use that name as the Domain.

I'm still not clear on the domain concept for this schema. I'm also
wondering how exactly you avoid performing the sum for all orders with a
related ordercharge, without a WHERE clause. By a query linking, do you
mean:

SELECT *, orders.customerinvoiceid
FROM ordercharges INNER
JOIN orders ON ordercharges.orderid = orders.orderid

Sorry, to ask for such spelling out, but I've had nowhere near as much
exposure to domain aggregate functions as I have had to JOINs and
subselects, I've never had to use them before, and help on domain
aggregates is pretty sparse. Thanks.

------------------------------

I have two tables, orders and ordercharges. They share a common field
orderid, which is the primary key of orders. In queries displaying the
orders datasheet view, the ordercharges table is usually the subdatasheet.

The query is intended to sum all the line item charges in ordercharges and
update customerchargeasbilled with the total, which will in turn become a
line item on a customerinvoice.

orders:
+-orderid
| customerinvoiceid (used to batch-bill multiple orders to customer)
| customerchargeasbilled --> SUM orderchargeasbilled for this
| (...)
|
| ordercharges:
| orderchargeid
+---orderid
orderchargeasbilled
(...)

-------------------------------

 

[Jeff Kowalczyk]

Zeppo, if you're able to elaborate on your technique for using joins
to workaround aggregate updates in Access, these are the types of queries
I can use on postgresql and sql server:

I would normally update like this (with appropriate parameter syntax)

UPDATE orders
SET customerchargeasbilled = (SELECT
SUM(orderchargeasbilled)
FROM ordercharges
WHERE orders.orderid = ordercharges.orderid)
WHERE orders.customerinvoiceid=[enter customerinvoiceid];

And then my result is accessed by:

SELECT orderid, customerchargeasbilled
FROM shipments
WHERE customererinvoiceid=[enter customerinvoiceid]

 

[John Vinson]

The query is intended to sum all the line item charges in ordercharges and
update customerchargeasbilled with the total, which will in turn become a
line item on a customerinvoice.

Why store the sum in the table AT ALL? Can't you base the invoice on a
query that sums it, or use the Sorting and Grouping features of the
report to do the sum?

 

[Jeff Kowalczyk]

Why store the sum in the table AT ALL? Can't you base the invoice on a
query that sums it, or use the Sorting and Grouping features of the
report to do the sum?

Yes, we are breaking relational rules there, but it is by choice. The
tradeoff is that the line-item ordercharges do not change after initial
entry, and by caching the sum in orders, our dozens of other queries are
much simpler to maintain, since the total is the number we need 99% of the
time. We're not using Access for the invoicing process (reports), its
just a data-entry front end. The only reason I'm troubling the group with
this is the access-specific updateable query issue, per the subject line.

What we will eventually need (barring a schema change) is a calculated
field and triggers on ordercharges updates, but we're just not there yet.

 

[Zeppo]

Zeppo, if you're able to elaborate on your technique for using joins
to workaround aggregate updates in Access, these are the types of queries
I can use on postgresql and sql server:

..
..

Something like this where a 1 to many relationship
is assumed between orders(1) and ordercharges(M).
It is assumed that initially customerchargeasbilled is 0
or some other appropriate number (ie. an appropriate starting
point.)
For each unique orderid the join and set statement simulate
the subquery (summing) by adding each orderchargeasbilled
value to the preceding sum of customerchargeasbilled
for the current orderid.

Update orders inner join ordercharges
on orders.orderid = ordercharges.orderid
SET customerchargeasbilled=customerchargeasbilled+orderchargeasbilled
WHERE orders.customerinvoiceid=[enter customerinvoiceid];

Yeah I work with servers that have updates developed on
planet earth too.Were was the Access Update developed?).

www.rac4sql.net

 

[Jeff Kowalczyk]

Update orders inner join ordercharges
on orders.orderid = ordercharges.orderid
SET customerchargeasbilled=customerchargeasbilled+orderchargeasbilled
WHERE orders.customerinvoiceid=[enter customerinvoiceid];

Thanks, Zeppo that works great. I wasn't sure what was the matter when the
test orders with null values in customerchargeasbilled didn't get updated.
Changing customerchargeasbilled to an initial value of 0 allowed the
total to update as intended.

I don't suppose there's any trick to reset the current value of
customerchargeasbilled to 0 ahead of the summed join trick? I doubt it,
since Access doesn't seem to allow multiple SQL statements per query, and
does check for duplicate output in the SET clause. In lieu of any one-step
technique, I'm just going to issue an UPDATE query to set it to 0 ahead of
time from VBA code.

Thanks again for the tip, and the help with the specific syntax.