Operation must use an Updatable Query

[Bunky]

I have read the posts regarding trying to do an update linking to Excel files
and how it is now illegal. Fine. I am not touching Excel in anyway. I am
linking via OBDC to a file I create in our AS-400. I read and do some
counting and summing matching to a local collectionstbl and local Staff
table. Then pass the results to another query to do an update to a reporting
table that is local. I still get the above error when I try to run the 2nd
update query to a local table. Ideas are welcome!

Here is the code for the 1st query reading the AS-400 file.
PARAMETERS [Begin Date:] DateTime, [End Date:] DateTime;
SELECT Count(UniquePaymentstbl.ARVACT) AS CountOfARVACT,
Sum(UniquePaymentstbl.SumOfAmtPd) AS SumOfSumOfAmtPd, Collectiontbl.UserId,
Staff.Specialist
FROM (UniquePaymentstbl INNER JOIN Collectiontbl ON UniquePaymentstbl.ARVACT
= Collectiontbl.Arvact) INNER JOIN Staff ON Collectiontbl.UserId = Staff.User
WHERE (((UniquePaymentstbl.TruDate) Between [Begin Date:] And [End Date:])
AND ((Collectiontbl.Result) Is Not Null))
GROUP BY Collectiontbl.UserId, Staff.Specialist;

Here is the code for the second query.

UPDATE Reporttbl INNER JOIN [Cumlative-AS-400] ON Reporttbl.Splst =
[Cumlative-AS-400].Specialist SET Reporttbl.[AS-400Cnt] =
[Cumlative-AS-400].CountOfArvact, Reporttbl.[AS-400Sum] =
[Cumlative-AS-400].SumOfSumOfAmtPd;

Thanks for any and all assistance!

 

[Tom van Stiphout]

On Wed, 30 Dec 2009 08:12:01 -0800, Bunky

If I understand correctly the second query fails with the
"non-updatable" error. I also understand that [Cumlative-as-400] is
your AS-400 table. That table is readonly. Or perhaps it is query1 -
you could have been a bit more descriptive in this respect.
If all the above caveats are true, then indeed this update query will
fail, even though you're not trying to write to the as-400 table. Too
bad so sad Access (better: Jet) is not smart enough to handle this.
First thing to try is to use DISTINCTROW in query1.
If that fails, you basically have two options: run the update using
VBA recordsets, or first copy the as-400 data to a local Access table.

-Tom.
Microsoft Access MVP

 

[Dirk Goldgar]

I have read the posts regarding trying to do an update linking to Excel
files
and how it is now illegal. Fine. I am not touching Excel in anyway. I am
linking via OBDC to a file I create in our AS-400. I read and do some
counting and summing matching to a local collectionstbl and local Staff
table. Then pass the results to another query to do an update to a
reporting
table that is local. I still get the above error when I try to run the
2nd
update query to a local table. Ideas are welcome!

Here is the code for the 1st query reading the AS-400 file.
PARAMETERS [Begin Date:] DateTime, [End Date:] DateTime;
SELECT Count(UniquePaymentstbl.ARVACT) AS CountOfARVACT,
Sum(UniquePaymentstbl.SumOfAmtPd) AS SumOfSumOfAmtPd,
Collectiontbl.UserId,
Staff.Specialist
FROM (UniquePaymentstbl INNER JOIN Collectiontbl ON
UniquePaymentstbl.ARVACT
= Collectiontbl.Arvact) INNER JOIN Staff ON Collectiontbl.UserId =
Staff.User
WHERE (((UniquePaymentstbl.TruDate) Between [Begin Date:] And [End Date:])
AND ((Collectiontbl.Result) Is Not Null))
GROUP BY Collectiontbl.UserId, Staff.Specialist;

Here is the code for the second query.

UPDATE Reporttbl INNER JOIN [Cumlative-AS-400] ON Reporttbl.Splst =
[Cumlative-AS-400].Specialist SET Reporttbl.[AS-400Cnt] =
[Cumlative-AS-400].CountOfArvact, Reporttbl.[AS-400Sum] =
[Cumlative-AS-400].SumOfSumOfAmtPd;

Thanks for any and all assistance!

Unfortunately, the Jet/ACE database engine concludes that any query that
includes a GROUP BY clause, or that involves such a query in a join or
subquery, is not updatable. This is true even if logically there is no
reason that the query would not be updatable, as in your case.

Possible workarounds would include:

(1) Use a temporary or work table to receive the results of your totals
query, "Cumlative-AS-400". Then join that temporary table to your reporting
table to perform the update.

(2) Use DLookup in your update query to look up the values from the totals
query. Of course, this will be quite inefficient and slow. SQL might look
like:

UPDATE Reporttbl SET
[AS-400Cnt] =
DLookup("CountOfArvact", "[Cumlative-AS-400]",
"Specialist=" & Splst),
[AS-400Sum] =

DLookup("SumOfSumOfAmtPd", "[Cumlative-AS-400]",
"Specialist=" & Splst)

That assumes that Specialist is a numeric field; if it's a text field,
appropriate quotes must be placed around the Splst value in the criteria
argument to DLookup.

(3) If the "reporting table" exists for the sole purpose of driving reports,
change the reports' recordsource query to an appropriate query that joins
that table to the totals query and selects the required fields from both
sources.