Access Update文の副問い合わせ

質問

以下のAのSQLをAccessから実行すると「実行時エラー3073 更新可能なクエリであることが必要です。」エラーが表示されます。
一時、待避としてBのような処理をしていますが、かなり処理速度がかかります。
何とかAのような1文で処理できないでしょうか？

'A================================================
Update PV_PRINT pv Set 勉強日 = (SELECT max(日付) as 日 FROM 基本記録テーブル where 項目2 between 3011 and 3014 and pv.USER_NUM = 基本記録テーブル.ID GROUP BY ID);

'B================================================
stSQL = "SELECT max(日付) as 日, ID FROM 基本記録テーブル where 項目2 between 3011 and 3014 GROUP BY ID;"
Set TBL1 = CurrentDb.OpenRecordset(stSQL)

Do Until TBL1.EOF
stSQL = "UPDATE PV_PRINT "
stSQL = stSQL & " SET PV_PRINT.勉強会日 = " & Format(TBL1![日], "yyyymmdd")
stSQL = stSQL & " WHERE PV_PRINT.USER_NUM = " & TBL1![ID] & ";"
CurrentDb.Execute stSQL
TBL1.MoveNext
Loop

Set TBL1 = Nothing

回答No.4

CHRONOS_0

テーブルに書き込む必要がありますか
基本記録テーブルから日付の最大を取り出したクエリとPV_PRNTをIDで結合するクエリを作れば
欲しいレコードセットは得られますけど

お礼

ワークテーブルを作成してTryしてみます。

回答No.3

noname#140971

お詫びと訂正：GROUP BY 句は無駄で不必要。

個別にユーザを特定した条件でもって最大値を求めています。
で、GROUP BY 句は無駄で不必要でした。
５：３０で帰宅をあせって投稿したのでお許しのほどを・・・。

なお、自宅からですので未テストですが・・・。

お礼

ありがとうございます。
今回は関数を作成せずに処理したいのでワークテーブル、クエリーを使用した方がいいのかなっと感じています。

回答No.2

noname#140971

[UserNum]_[勉強日]
_______1___2008/10/02
_______2___2008/10/02

２回目のテスト結果はコピペミスでした。
当然に’2008/10/02’に更新されます。

回答No.1

noname#140971

基本記録テーブル: テーブル

[ID]_[User_Num]_[日付______]_[項目
001__________1___2008/10/01___3011
002__________1___2008/09/30___3012
003__________2___2008/10/01___3010
004__________2___2008/10/02___3014

PV_PRINT: テーブル

[UserNum]_[勉強日]
_______1___2008/10/01
_______2___2008/10/02

基本記録テーブル: テーブル

[ID]_[User_Num]_[日付______]_[項目
001__________1___2008/10/01___3011
002__________1___2008/09/30___3012
003__________2___2008/10/01___3010
004__________2___2008/10/02___3014

PV_PRINT: テーブル

[UserNum]_[勉強日]
_______1___2008/10/01
_______2___2008/10/02

[イミディエイト]
? CnnExecute("Update PV_PRINT Set 勉強日 = DBMax('日付', '基本記録テーブル', '項目 between 3011 and 3014 AND UserNum=' & UserNum & ' GROUP BY UserNum')")
True

複文を使わない邪道な回答です。
結局は, DBMax関数の中で複文を実行しています。
ですから、どこまで処理速度が改善されるかは保証の限りではありません。
ただ、SQL文が一文になるだけとも言えます。

Public Function DBMax(ByVal strField As String, _
　　　　　　　　　　　ByVal strTable As String, _
　　　　　　　　　　　Optional strWhere As String = "", _
　　　　　　　　　　　Optional ReturnValue = 0) As Variant
On Error GoTo Err_DBMax
　　Dim N
　　Dim strQuerySQL As String
　　Dim rst　　　　 As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　strQuerySQL = "SELECT MAX(" & strField & ") FROM " & strTable
　　If Len(strWhere) > 0 Then
　　　　strQuerySQL = strQuerySQL & " WHERE " & strWhere
　　End If
　　With rst
　　　　 .Open strQuerySQL, _
　　　　　　　 CurrentProject.Connection, _
　　　　　　　 adOpenStatic, _
　　　　　　　 adLockReadOnly
　　　　 If Not .BOF Then
　　　　　　 .MoveFirst
　　　　　　 N = Nz(.Fields(0), "")
　　　　 End If
　　End With
Exit_DBMax:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBMax = IIf(N <> "", N, ReturnValue)
　　Exit Function
Err_DBMax:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBMax)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBMax
End Function

＜＜テスト用関数＞＞

Public Sub ErrMessage(ByVal CnnErrors As ADODB.Error, ByVal strSQL As String)
　　 MsgBox "ADOエラーが発生しましたので処理をキャンセルします。" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & CnnErrors.Description & Chr$(13) & _
　　　　　　"・Err.Number=" & CnnErrors.Number & Chr$(13) & _
　　　　　　"・SQL State=" & CnnErrors.SQLState & Chr$(13) & _
　　　　　　"・SQL Text=" & strSQL, _
　　　　　　vbExclamation, " ADO関数エラーメッセージ"
End Sub

Public Function CnnExecute(ByVal strSQL As String) As Boolean
On Error GoTo Err_CnnExecute
　　Dim isOK As Boolean
　　Dim cnn　As ADODB.Connection

　　isOK = True
　　Set cnn = CurrentProject.Connection
　　With cnn
　　　　.Errors.Clear
　　　　.BeginTrans
　　　　.Execute strSQL
　　　　.CommitTrans
　　End With
Exit_CnnExecute:
On Error Resume Next
　　cnn.Close
　　Set cnn = Nothing
　　CnnExecute = isOK
　　Exit Function
Err_CnnExecute:
　　isOK = False
　　If cnn.Errors.Count > 0 Then
　　　　ErrMessage cnn.Errors(0), strSQL
　　　　cnn.RollbackTrans
　　Else
　　　　MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(CnnExecute)", _
　　　　　　　 vbExclamation, " 関数エラーメッセージ"
　　End If
　　Resume Exit_CnnExecute
End Function

※余り、改善されないかも・・・？もしかしたら、されるかも・・・？
※そんないい加減な回答です。