パラメータクエリを実行後レコードセットに格納してテーブルに挿入

関連記事：レコードセットでなく、SQLで挿入する方法はこちら↓
ray88.hatenablog.com

■　以下のようなパラメータクエリ「Q_インサート」を作成したとします。
　パラメータはフィールド部分の担当者:[Offfiser]　と
　抽出条件部分の >=[price]　の部分
　このクエリにパラメータの値を渡し、実行後結果をレコードセットに格納。
　装入先のT_インサートテスト用テーブルについてもレコードセットに格納し
　レコードセットからレコードセットに値を挿入していきます。
f:id:ray88:20200111141924p:plain
※過去記事、「パラメータクエリの実行し結果をSQLでテーブルに挿入」では、
　　実行結果をそのまま挿入できますが、レコードセット同士で挿入する場合、
　　別名カラムについてはうまく参照できませんので、実際クエリを作成する
　　際は、クエリテーブルには別名カラムは作成せず変数に持たせるように
　　なるかと思います。今回は過去記事との動作比較用にそのままにしています。

■T_テストテーブルのフィルターをかけていない状態は以下
f:id:ray88:20200111143854p:plain
■挿入先テーブル T_インサートテスト用のテーブルは以下
f:id:ray88:20200111144015p:plain

■コードは以下
　ここでは単純にパラメータを”木村さん”、1500 としてますが、実務では変数で
　設定したり、フォームのコントロールで設定したりするようになります。

Sub testParam()

    Dim daoDb As DAO.Database
    Dim daoQd As DAO.QueryDef
    Dim strSQL As String
    Dim RS As Recordset
    Dim RS2 As Recordset
    Set daoDb = CurrentDb
    Dim strOffiser As String
    
    'まずクエリーのオブジェクトを取得します
    Set daoQd = daoDb.QueryDefs("Q_インサート用")
    
    With daoQd
      'クエリーのオブジェクトに対して設定を行います。"抽出する顧客ID" などがパラメータ名です
      .Parameters("[price]") = 1500
      .Parameters("[Offiser]") = "木村さん"      
      '設定後のクエリーオブジェクトを元にRecordsetを開きます
      Set RS = .OpenRecordset
    End With
    
    '別名カラムはうまく動作しないようなので挿入する値を変数に格納
    strOffiser = "木村さん"
    
    'レコード件数の確認。一旦最終行に移動しないとレコードがある場合「1」しか返ってこない
    RS.MoveLast
    
    Debug.Print RS.RecordCount
                    
    '対象レコードなければ処理終了。対象レコードが存在する場合は最初の行に戻す
    If RS.RecordCount = 0 Then
        MsgBox "対象レコードがありません"
        RS.Close
        Set RS = Nothing
        Exit Sub
    Else
        RS.MoveFirst
    End If
        
     'レコード挿入先のテーブルをレコードセットに格納する
    Set RS2 = daoDb.OpenRecordset("T_インサートテスト用", dbOpenDynaset)    
    
    Do Until RS.EOF
        RS2.AddNew
            With RS2
                    !ID = RS!ID
                    !代理店コード = RS!代理店コード
                    !タイトル = RS!タイトル
                    !著者 = RS!著者
                    !出版社 = RS!出版社
                    !価格 = RS!価格
                    !年月 = RS!年月
                    !キャンセルフラグ = RS!キャンセルフラグ
                    
                    '別名カラムだけ、変数を使って挿入
                    !担当者 = strOffiser
                    
                    'これを忘れたら更新されない
                    .Update
            End With
        '次の行に進む（これを忘れると無限ループ!)
        RS.MoveNext
      Loop
      
      '終了処理
      RS.Close: RS2.Close
      Set RS = Nothing: Set RS2 = Nothing
      
End Sub

■実行結果
　パラメータの値もちゃんと反映されています。
f:id:ray88:20200111151533p:plain

ray88’s diary

お仕事で困ったとき用の自分用の覚書

パラメータクエリを実行後レコードセットに格納してテーブルに挿入