Operation must use updateable query. 1

[jsteph]

In Access 2003 Jet Sql, I get an error:
"Operation Must use an updateable query".
I have searched google and the first dozen hits seem to all be saying it's a permissions issue.

In my situation it most definitely is not a permissions issue.

I'm tring to update a table using a subselect as the SET argument, ie:

UPDATE Customers c
SET c.LastOrder =
    (SELECT Max(ordDate) 
    FROM Orders o
    WHERE o.CustNum = c.CustNum
    GROUP BY o.CustNum)

There are no permissions issues, the db is not readonly, the tables are keyed properly, blah, blah, blah.

This works in sql server with the identical table structure.

Does Jet sql simply have this glaring weakness?

Without resorting to DMax or DAO recordset operations, how would one accomplish this in Access with pure sql?

The background is that I'm doing a 'downgrade' demo app--converting a sql-server backend to Access backend for a stand-alone demo app to be compiled used/tested on laptops.
Thanks for any help.
--Jim

 

[PHV]

Unfortunately, JetSQL doesn't like subselect in update queries.
What about this ?
UPDATE Customers
SET LastOrder = DMax("ordDate","Orders","CustNum=" & [CustNum])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

UPDATE Customers c inner join (Select custnum Max(ordDate)as maxorderdate
from orders GROUP BY CustNum)o on c.custnum =o.custnum Set c.lastorder = o.maxorderdate

 

[PWise]

sorry sb

UPDATE Customers  c 
inner join (Select custnum, Max(ordDate)as maxorderdate 
            from orders 
            GROUP BY CustNum)o 
on c.custnum =o.custnum 
Set c.lastorder = o.maxorderdate

 

[jsteph]

pwise,
Thanks, but that gives the same error.

PHV,
Thanks, I know the domain fcns will work so I'd prefaced that I wanted to avoid DAO or DMax functions.

My original app has almost 100% sql via pass-thru or pass-thru calls to stored procs, I'm trying to get this demo so there are as few conversions as possible.

I may have to resort to the dmax, but I was hoping to avoid it.
Thanks,
--Jim

 

[PHV]

Why not have a demo with MSDE or SQL Express ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FancyPrairie]

Try this:

Open your query in design view, select the Query Properties button (or right click and select Properties) and then change the Recordset Type property from Dynaset to Dynaset (Inconsistent Updates). Then run your query.

 

[jsteph]

PHV,
That's not a bad idea, but I wanted the delivery of this to be as 'no strings attached' as possible.

If I had my druthers I don't even want to have to install an access runtime--the target audience already has Office 2003 pro, and I've run into issues with the installation of MSDE or SQL-express, even on my own machine. Truth be told I've never even used MSDE.

My original design of this was to use as much 'standard sql' as possible--the sp's don't even use cursors--so that it would be as portable as possible (to Oracle, etc). The last thing I expected was to have Access' sql be the weak link.
--Jim

 

[jsteph]

FancyPrairie,
That's only for Select queries...the action queries don't have the option. I did try the various locking options to no different effect.
--Jim

 

[FancyPrairie]

You're right. But, the way I did it was to create a Select Query and then my update query joined with the select query. Something like this:

UPDATE Customers c
inner join qryCustNum
on c.custnum = qryCustNum.custnum
Set c.lastorder = qryCustNum.maxorderdate

 

[jsteph]

FancyPrairie,
Still the same error...

But I'm curious, does your original qryCustnum work for you, and is it a pure group-by query? If so, it might be a version or service pack issue on my end.

I assumed your qryCustnum was:

SELECT Orders.CustNum, Max(Orders.OrdDate) AS MaxOrderDate
FROM Orders
GROUP BY Orders.CustNum;

However, changing qryCustNum from a simple group by to a double-sub-select does work:

SELECT o1.CustNum, o1.OrdDate AS MaxOrderDate
FROM Orders AS o1
WHERE (o1.OrdNum In (Select o2.ordnum from orders o2 Where o2.custnum = o1.custnum AND  o2.orddate = (Select max(orddate) from orders o3 where o3.custnum = o2.custnum)));

Then doing the join you showed with qryCustNum using the second sql works...

But if I replace the joined query with the above double-sub-select statement I get the same error, so kind of back to square one again, but a star for you because I can use it and get this thing going.
Thanks,
--Jim