ACCESS VBA 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レコードセットのフィールド名の指定に利用しています。
フィールド名を変数で指定する方法はこちらの記事に詳しく記載していますのでご参照ください。
上記の処理をループさせることで、rst1の全てのフィールドの値をrst2の同一のフィールドにコピーできます。
以上、ADOでフィールド名を指定せずにレコードをコピーする機能をご紹介しました。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私(@hakoniwagadget)にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。