accessでのINSERT,DELETEの書き方について教えてください

Question

初心者で申し訳ないのですが、
accessでのSQLで、UPDATE,INSERT,DELETEを組み合わせた書き方がよくわかりません。

マスターとトランザクションで、
１ 商品コードが被ったら更新、
２ マスター側に商品コードがなかったら追加、
３ 商品コードが被ってるけど、追加するデータが入ってなかったら削除
というプログラムです。

まず、INSERTするとテーブルのデータが全て追加されてしまうので、
１行ずつ追加したいこと。

次に、Ifでrs_ans! rs_transaction!としてるので、毎回、OpenとCloseを繰り返さなければならないのか。
（現在ここでエラーになります・・・）

そもそもIf文ではなくUnionで書けないのか・・・

色々とおかしな部分があるかと思いますが、教えてください。お願いします。

Sub Key()

Dim cnn As ADODB.Connection

Dim rs_master As ADODB.Recordset
Dim rs_ans As ADODB.Recordset
Dim rs_transaction As ADODB.Recordset
Dim aSQL As String

Set cnn = CurrentProject.Connection
Set rs_master = New ADODB.Recordset
Set rs_ans = New ADODB.Recordset
Set rs_transaction = New ADODB.Recordset


    rs_master.Open "master", cnn, adOpenKeyset, adLockOptimistic
    rs_ans.Open "ans", cnn, adOpenKeyset, adLockOptimistic
    
    Do Until rs_master.EOF
        rs_ans.AddNew
        
        rs_ans!商品コード = rs_master!商品コード
        rs_ans!商品名 = rs_master!商品名
        rs_ans!単価 = rs_master!単価
        
        rs_ans.Update
        rs_ans.MoveNext
        rs_master.MoveNext
    Loop
    
    
    rs_transaction.Open "transaction", cnn, adOpenKeyset, adLockOptimistic
    rs_ans.MoveFirst
    
    Do Until rs_transaction.EOF
        
        If rs_ans!商品コード = rs_transaction!商品コード Then
            aSQL = "UPDATE ans SET ans.商品名 = '" & rs_transaction!商品名 & "', ans.単価 = " & rs_transaction!単価 & "" & _
            " WHERE ans.商品コード = " & rs_transaction!商品コード & ""
            
            If rs_transaction!単価 = 0 And rs_transaction!商品名 Is Null Then
                aSQL = "DELETE FROM ans" & _
                " WHERE (" & rs_transaction!単価 & " = 0) & (" & rs_transaction!商品名 & " IS NULL)"
            End If
            
        ElseIf rs_ans!商品コード <> rs_transaction!商品コード Then
            aSQL = "INSERT INTO ans ( 商品コード, 商品名, 単価 ) SELECT transaction.商品コード, transaction.商品名, transaction.単価 " & _
            " FROM transaction WHERE ans.商品コード <> " & rs_transaction!商品コード & ""
        
        End If

        rs_ans.Close
        rs_ans.Open aSQL, cnn, adOpenKeyset, adLockOptimistic
        rs_transaction.MoveNext
    Loop
    
Set rs_ans = Nothing
rs_master.Close: Set rs_master = Nothing
rs_transaction.Close: Set rs_transaction = Nothing
cnn.Close: Set cnn = Nothing
    
End Sub

～追記～

masterにあるデータをtransactionのデータと比較して、処理し、
ansのテーブルに出力する流れです。
商品コード、商品名、単価が全てのテーブルにあります。

１度ansのテーブルにmasterのデータを全て書いて、
rs_ans!商品コード = rs_master!商品コード
rs_ans!商品名 = rs_master!商品名
rs_ans!単価 = rs_master!単価
（ ↑のループ部分です。 ）

それをtransactionのテーブルと比較し処理するのがいいかなと思っただけですので、
他によい方法があれば教えてください。

Comments

[kaz.Suenaga]

master、ans、transaction のそれぞれのテーブルの関係性を整理していただけますか。
追加、削除、更新の対象は ans ですか。

Answer 1

先に突っ込み的な話ですが、

• rs_ans.AddNew するなら、rs_ans.MoveNextは不要です
• せっかくRecordset（rs_ans）を開いている状態で、あえてSQLを発行して処理しようとするのはなぜでしょうか。

本題ですが、プランとして

• まず「マスター」と「トランザクション」を結合した、各レコードについて評価すればいいようなレコードセットを作る
• そのレコードセットの各行に対して、rs_ans に追加していく

方がスマートかと思いました。

SELECT * FROM master LEFT JOIN transaction ON master.商品コード = transaction.商品コード
WHERE transaction.商品コード IS NULL

で master にしかないデータ、

SELECT * FROM master LEFT JOIN transaction ON master.商品コード = transaction.商品コード
WHERE transaction.商品コード IS NOT NULL

で master 、transaction 両方にあるデータ、

SELECT * FROM transaction LEFT JOIN master ON transaction.商品コード = master.商品コード
WHERE master.商品コード IS NULL

で transaction にしかないデータ、になるので、それぞれのパターンでレコードセットを作成して処理すれば、削除せずにできる気がします。

Answer 2

Do Until rs_transaction.EOF
の所を下記のようなSQLにしたら出来るのでは？

下記のSQLは参考です。
実際の環境で動かしていないので実際に動かすと
構文エラーが出るかも知れません。

--削除処理
DELETE FROM ans
INNER JOIN transaction
ON ans.商品コード = transaction.商品コード
WHERE transaction.単価=0 AND transaction.商品名 Is Null

--更新処理
UPDATE ans
INNER JOIN transaction
ON ans.商品コード = transaction.商品コード
SET ans.単価=transaction.単価,ans.商品名 = transaction.商品名
WHERE transaction.単価<>0 OR transaction.商品名 Is Not Null

--追加処理
INSERT INTO ans
SELECT transaction.* FROM transaction
LEFT JOIN ans
ON ans.商品コード = transaction.商品コード
WHERE (transaction.単価<>0 OR transaction.商品名 Is Not Null) AND ans.商品コード Is Null

Comments

[makino]

ありがとうございます。

下記のように書いたところ、更新はされるのですが、
削除と追加がされない状態になりました。
書き方が間違ってますでしょうか？

　 rs_ans.MoveFirst
rs_ans.Close

Do Until rs_transaction.EOF
　　　aSQL = "DELETE FROM～
　　　aSQL = "INSERT INTO～
　　　aSQL = "Update～
　 rs_ans.Open aSQL, cnn, adOpenKeyset, adLockOptimistic
rs_transaction.MoveNext

Loop

[cry_4_the_moon]

Do Until rs_transaction.EOF
　中略
rs_transaction.MoveNext
を
aSQL = "DELETE FROM～
rs_ans.Open aSQL, cnn, adOpenKeyset, adLockOptimistic
aSQL = "INSERT INTO～
rs_ans.Open aSQL, cnn, adOpenKeyset, adLockOptimistic
aSQL = "Update～
rs_ans.Open aSQL, cnn, adOpenKeyset, adLockOptimistic
としたら使えるはずです。
紹介したSQLは、１行づつ処理するのではなく一括で処理を行えるようにしたものです。