クエリを実行すると「更新可能なクエリであることが必要です」となる

Question

実現したいこと

会館管理プログラムを access で作成しています。
テーブル「申込」と「会場利用明細」が1対多でリレーションを貼っています。
1申込で複数日・複数部屋を申請することができるようになっています。
会場利用明細は使用日、時間帯、単価等のデータを扱います。
社内規定で請求日ではなく初回使用日で売上を集計する必要があります。データ項目"申込No”で
minを用いて初回使用日を特定できるとして、申込テーブルに項目を作っていなかったのですが、処理が重くなってしまい、初回使用日を申込テーブルに追加しました。

これまで入力していたデータから初回使用日を更新クエリで簡単に拾えると思っていたのですが・・・表題にあるエラーが表示されてしまうので、このエラーをなくしてデータを初回使用日を更新したく

発生している問題・分からないこと

Access ファイルは プログラムパートを 会場利用.accdb、データパートを 申込data.accdb として分割し、プログラムを複数の職員に配布、申込data.accdb はファイルサーバにおいてあります。

そこでまず更新クエリ
UPDATE 申込 SET 申込.初回使用日 = (SELECT Q_初回使用日.初回使用日
FROM Q_初回使用日
WHERE 申込.申込No = Q_初回使用日.申込No
);
/* Q_初解消日
SELECT 会場使用明細.申込No, Min(会場使用明細.使用日) AS 初回使用日
FROM 会場使用明細
GROUP BY 会場使用明細.申込No;
*/
を実行させたのですが、「更新可能なクエリであることが必要です。」というメッセージボックスが出ます。
少しググって、ネットワーク越しのデータにアクセスする場合は read only になることから、申込data.accdb のオプションから「ネットワーク上のドキュメントを信頼することを許可する」にチェックが入っていることは確認しました。
ネットワーク越しの制約がなにか影響しているのか？と思って、ファイルサーバ上で更新クエリを実行させても同じ症状です（エラーメッセージも同一）

エラーメッセージ

(1)更新クエリ：「更新可能なクエリであることが必要です。」
(2)vbaでsqlを実行させたとき　（update_test1）：
実行時エラー '-2147467259(80004005);'
更新可能なクエリであることが必要です。
(3) vbaで docmd.runsql を実行させたとき（update_test2）：
実行時エラー '3073':
更新可能なクエリであることが必要です。

該当のソースコード

UPDATE 申込 SET 申込.初回使用日 = (SELECT Q_初回使用日.初回使用日
    FROM Q_初回使用日
    WHERE 申込.申込No = Q_初回使用日.申込No
);
/* Q_初回使用日
SELECT 会場使用明細.申込No, Min(会場使用明細.使用日) AS 初回使用日
FROM 会場使用明細
GROUP BY 会場使用明細.申込No;
*/

Sub update_test()
  Dim rs As New ADODB.Recordset
  Dim cn As New ADODB.Connection

    Set cn = CurrentProject.Connection
    rs.Open "UPDATE 申込 INNER JOIN Q_初回使用日 ON 申込.申込No = Q_初回使用日.申込No SET 申込.初回使用日 = Q_初回使用日.初回使用日", cn
    rs.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Sub update_test2()
    DoCmd.RunSQL "UPDATE 申込 INNER JOIN Q_初回使用日 ON 申込.申込No = Q_初回使用日.申込No SET 申込.初回使用日 = Q_初回使用日.初回使用日"
End Sub

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

teratail で「更新可能なクエリであることが必要です」を検索したのですが、今回の問題に該当するＱＡは見つかりませんでした。
ググってみたところ、マイクロソフトの Microsoft build で
https://learn.microsoft.com/ja-jp/office/vba/access/concepts/miscellaneous/operation-must-use-an-updatable-queryerror-3073?f1url=%3FappId%3DDev11IDEF1%26l%3Dja-JP%26k%3Dk(jeterr40.chm5003073)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue
が見つけてます。ここで、「更新できないフィールドを更新するクエリを実行しようとしました。 たとえば、一対多リレーションシップの '一' 側のフィールドを更新するクエリを実行しようとしました。」とあります。今回の問題は、キー項目でもリレーションを貼った項目でもないので、該当しないと判断しています。

補足

なにか見落としているような気がするのですが・・・。何かヒントでも頂けるとありがたいです。

Comments

[sazi]

例えば、申込テーブルを直接更新する以下のようなクエリーではエラーにならないでしょうか？
UPDATE 申込 SET 申込.初回使用日=#2024/1/1#
WHERE 申込No=1

[sazi]

Q_初回使用日の内容も質問に追記して下さい。
多分、申込№で1:1にならないのが理由な気がします。

[maqua0319]

sazi さん
１）直接的に値を代入する更新クエリは実行できました。
２）Q_初回使用日はソースコード欄に書いてますが、
SELECT 会場使用明細.申込No, Min(会場使用明細.使用日) AS 初回使用日
FROM 会場使用明細
GROUP BY 会場使用明細.申込No;
と最小値を取っていますので、重複はないはずです。
まだデータ件数しか見てませんが、申込テーブルの件数とQ_初回使用日の件数は合致しています。

[maqua0319]

今
SELECT 申込.申込No, Q_初回使用日.初回使用日
FROM Q_初回使用日 RIGHT JOIN 申込 ON Q_初回使用日.申込No = 申込.申込No;
で確認してみました。データシートビューで初回使用日が空欄のものはありませんでした。
逆も試してみたのですが、同じく空欄は見当たりませんでした。

sqlを inner join にしても同じ症状です。
UPDATE 申込 INNER JOIN Q_初回使用日 ON 申込.申込No = Q_初回使用日.申込No SET 申込.初回使用日 = [Q_初回使用日].[初回使用日];

[winterboum]

該当のソースコード にある query と 「vba に書かれているsql」がまるで異なりますが、双方の関係はどうなってます？

Answer 1

• 集計クエリ（ GROUP BY 句付きの SELECT 文）が返すレコードセットは読み取り専用（編集不可）である。

• Access の仕様上、上記のクエリをネストしたクエリが返すレコードセットも同様に読み取り専用となる。

• したがって、そのレコードセットに含まれるレコードを更新、削除することは出来ない。

別解

• 集計クエリやサブクエリではなく、定義域集計関数によりグループごとの最小値を取得する。

UPDATE [申込] 
SET [申込].[初回使用日] = DMin("使用日","会場使用明細","[申込No]=" & [申込].[申込No])
WHERE [申込].[申込No] Is Not Null;

Comments

[maqua0319]

ご回答ありがとうございます。

私のほうでも、いくつかテストして見ました。
で、最初に vba でレコードセットを開いて更新しました。その際 sk.exeさんが指摘されたように dlookup関数を使用したところ、問題なく完了。
「うん！？、ということは・・・」で、
UPDATE 申込 SET 申込.初回使用日 = DLookUp("初回使用日","Q_初回使用日","申込No=" & [申込].[申込No]);
としたら、問題なく実行できました。
ただ、理由がよくわかっていなかったところ、こちらを見てみたら se.exe さんの回答を見て納得しました。
（Accessの仕様には納得できていませんが）

この土日、自宅でウンウン唸っていたのですが、半日で解決できて嬉しいです。
ありがとうございました。