2024年7月19日 / 最終更新日時 : 2024年7月19日 hakoniwa_gadget ACCESS開発事例

ACCESS VBA ADOでフィールド名を指定せずにレコードをコピーする機能

ADOでフィールド名を指定せずにレコードをコピーする機能

ACCESSでVBAを使ってテーブルのレコードを操作する際に利用するのがADOです。

例えば、テーブルAからテーブルBへ、レコードの値をコピーするような処理も作成可能です。
この際、テーブルAのどのフィールドの値を、テーブルBのどのフィールドにコピーするかを指定する必要があります。

しかし、フィールド数が多くなったりすると記述が大変ですし、テーブルのフィールド名が変わった場合には変更しなくてはなりません。
そこで今回は、ADOでフィールド名を指定せずにレコードをコピーする機能をご紹介します。

こんにちは。
はこにわガジェット （@hakoniwagadget） です。

ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。

フィールド名を指定せずにレコードをコピーする記述方法

最初に、ADOでフィールド名を指定せずにレコードをコピーする記述方法を示します。
※ 簡略化するため、ADOを利用するためのデータベース接続などの一部記述は省略しています。


 Dim rst1 As ADODB.Recordset
 Dim rst2 As ADODB.Recordset
 Dim fld As ADODB.Field

 rst1.Open "TMP_売上", cnn, adOpenKeyset, adLockReadOnly
 rst2.Open "TRN_売上", cnn, adOpenKeyset, adLockOptimistic

 rst2.AddNew
            
    For Each fld In rst1.Fields
                
       rst2(fld.Name) = rst1(fld.Name)
                
    Next fld
                
 rst2.Update

fldというレコードセットのフィールドオブジェクトを格納する変数を設定しています。
そこに、For Each fldでレコードセット内の全てのフィールドを指定して、rst1に格納したTMP_売上からrst2に格納したTRN_売上へレコードの値をコピーしています。

詳しい説明は以下をお読みください。

テーブルのレコードを他のテーブルにコピーする方法

まず、コードの解説の前に前提から。
ACCESSでテーブルのレコードを他のテーブルにコピーするには以下の2つの方法があります。

1.追加クエリを使う

2.ADOを使ってVBAで処理する

この内、簡単なのは1.追加クエリを使う方法です。
追加クエリであればVBAの複雑な記述を行わなくてもレコードのコピー処理を作成することが可能です。

追加クエリの詳しい使い方は以下の記事を参照ください。

追加クエリの作成方法

ADOを使ってレコード追加をするケース

一方で、ADOを使ってレコードの追加処理を行うシーンも多々あります。
基本は追加クエリを使った方が楽なのですが、追加クエリで対応できない複雑な処理を作成する場合にADOを利用することになります。

例えば、元となるテーブルのレコードを1レコードずつ判定し、結果によって追加処理の内容が変わる場合などです。
こうしたケースでは追加クエリでの対応が難しくなりますのでADOを使ってVBAで処理を記述することになります。

今回は以下のようなTMP_売上というテーブルを、条件に応じて全く同じフィールドレイアウトのTRN_売上というテーブルにコピーするケースを考えてみましょう。

用途としてはTMP_売上がcsvなどからデータをインポートする一時的なデータ格納テーブルでTRN_売上の方が本番のデータを蓄積するテーブルという使い分けです。
TMP_売上からTRN_売上へのコピー時に、TRN_売上に受注番号が同一のレコードであれば上書きし、なければ新規レコードを追加するという処理を作成します。

フィールド名を指定して記述したプロシージャ

では、まずは普通にフィールド名を指定する形で処理を作ってみましょう。


 Public Sub record_copy()

    '変数の宣言
    Dim cnn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
    Dim fld As ADODB.Field

    '変数にADOオブジェクトを代入
    Set cnn = CurrentProject.Connection
    Set rst1 = New ADODB.Recordset
    rst1.CursorLocation = adUseClient
    Set rst2 = New ADODB.Recordset
    rst2.CursorLocation = adUseClient
    
    'レコードセットを取得
    rst1.Open "TMP_売上", cnn, adOpenKeyset, adLockReadOnly
    rst2.Open "TRN_売上", cnn, adOpenKeyset, adLockOptimistic

    If rst1.RecordCount = 0 Then
    
        '対象レコードが無ければ処理終了
        Exit Sub
    
    End If
    
    rst1.MoveFirst
    
    Do Until rst1.EOF
    
        '受注番号で検索

        rst2.Filter = "受注番号 = '" & rst1!受注番号 & "'"
    
        If rst2.RecordCount = 0 Then
            
            '同一受注番号が無ければ追加
            rst2.AddNew
            
                rst2!受注番号 = rst1!受注番号
                rst2!処理日 = rst1!処理日
                rst2!顧客ID = rst1!顧客ID
                rst2!小計 = rst1!小計
                rst2!消費税率 = rst1!消費税率
                rst2!消費税 = rst1!消費税
                rst2!伝票合計 = rst1!伝票合計
                rst2!特記事項 = rst1!特記事項
                rst2!請求 = rst1!請求
                rst2!請求済 = rst1!請求済
                rst2!請求ID = rst1!請求ID
                rst2!削除 = rst1!削除
                
            rst2.Update
        
        Else
        
            '同一受注番号があれば更新
                rst2!処理日 = rst1!処理日
                rst2!顧客ID = rst1!顧客ID
                rst2!小計 = rst1!小計
                rst2!消費税率 = rst1!消費税率
                rst2!消費税 = rst1!消費税
                rst2!伝票合計 = rst1!伝票合計
                rst2!特記事項 = rst1!特記事項
                rst2!請求 = rst1!請求
                rst2!請求済 = rst1!請求済
                rst2!請求ID = rst1!請求ID
                rst2!削除 = rst1!削除
            
            rst2.Update
                    
        End If
    
        rst1.MoveNext
    
    Loop
    
    
    '終了処理
    rst1.Close: Set rst1 = Nothing
    rst2.Close: Set rst2 = Nothing
    cnn.Close: Set cnn = Nothing
    
    Debug.Print "テーブルコピー完了"

 End Sub

かなり長いコードですね。
途中の’同一受注番号が無ければ追加、’同一受注番号があれば更新、の部分で受注番号に応じてレコードの追加もしくは更新処理を行っています。

この際に、


 rst2!処理日 = rst1!処理日

のような形でテーブルのフィールド名を1つずつ指定して値のコピー処理をしているのです。
この方法は単純で簡単ですがいくつか問題もあります。

一つは単純にコードの行数が増えるので書き間違えや、フィールドの記載漏れが発生しやすい点です。
フィールド数が多いテーブルでこの記述方法をとるとかなり時間がかかりますし、1フィールド漏れていてもミスに気付きにくくなります。

もう一つは元にしているテーブルのレイアウトが変わった際（フィールドの追加やフィールド名の変更時）にプロシージャも修正しなければいけない点です。
開発者が仕様を覚えていればテーブルレイアウト変更時に、プロシージャも合わせて変更できるのですが、開発してからしばらく時間が経っていたり、そもそも開発者が別の方に変わっていたりすると、この仕様を覚えておくことは難しいでしょう。

フィールド名を変数を使って指定したプロシージャ

そこで上記の課題を解決するために、今回のテーマであるフィールド名を個別に指定せずに行うよう、プロシージャを書き換えましょう。
以下が完成形になります。


 Public Sub record_copy()

    '変数の宣言
    Dim cnn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
    Dim fld As ADODB.Field

    '変数にADOオブジェクトを代入
    Set cnn = CurrentProject.Connection
    Set rst1 = New ADODB.Recordset
    rst1.CursorLocation = adUseClient
    Set rst2 = New ADODB.Recordset
    rst2.CursorLocation = adUseClient
    
    'レコードセットを取得
    rst1.Open "TMP_売上", cnn, adOpenKeyset, adLockReadOnly
    rst2.Open "TRN_売上", cnn, adOpenKeyset, adLockOptimistic

    If rst1.RecordCount = 0 Then
    
        '対象レコードが無ければ処理終了
        Exit Sub
    
    End If
    
    rst1.MoveFirst
    
    Do Until rst1.EOF
    
        '受注番号で検索

        rst2.Filter = "受注番号 = '" & rst1!受注番号 & "'"
    
        If rst2.RecordCount = 0 Then
            
            '同一受注番号が無ければ追加
            rst2.AddNew
            
                For Each fld In rst1.Fields
                
                    rst2(fld.Name) = rst1(fld.Name)
                
                Next fld
                
            rst2.Update
        
        Else
        
            '同一受注番号があれば更新
            For Each fld In rst1.Fields
            
                rst2.Update fld.Name, rst1(fld.Name)
            
            Next fld
                    
        End If
    
        rst1.MoveNext
    
    Loop
    
    
    '終了処理
    rst1.Close: Set rst1 = Nothing
    rst2.Close: Set rst2 = Nothing
    cnn.Close: Set cnn = Nothing
    
    Debug.Print "テーブルコピー完了"

 End Sub

今度はかなりすっきりしましたね。
先ほど、フィールド名を1つずつ指定して記述していた箇所を


             rst2.AddNew
            
                For Each fld In rst1.Fields
                
                    rst2(fld.Name) = rst1(fld.Name)
                
                Next fld
                
            rst2.Update

と書き換えています。


 For Each fld In rst1.Fields

では、For Each … Next構文でループ処理を作成し、変数fldにrst1、つまりTMP_売上テーブルの全てのフィールドを順次入れていく処理を行っています。
その後、


 rst2(fld.Name) = rst1(fld.Name)

で、rst2の各フィールドに対して、rst1の同一のフィールド名の値を転記しています。
fld.Nameという記述方法で、fldオブジェクトの名称を取得することができます。それをrst2(fld.Name)という形でrst2レコードセットのフィールド名の指定に利用しています。

フィールド名を変数で指定する方法はこちらの記事に詳しく記載していますのでご参照ください。

ADOでフィールド名を変数で指定する方法

上記の処理をループさせることで、rst1の全てのフィールドの値をrst2の同一のフィールドにコピーできます。

以上、ADOでフィールド名を指定せずにレコードをコピーする機能をご紹介しました。

ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。

そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私（@hakoniwagadget）にお任せください。

ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。

サービス一覧

最後までお読みいただき、ありがとうございました。

カテゴリー
ACCESS開発事例
タグ
ACCESS VBA-Tech

前の記事

ACCESS VBA ADODB.Recordsetとは
2024年7月16日
ACCESS関数辞典

次の記事

ACCESS DateValue関数で日付型データを作る方法
2024年7月23日