エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について


みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。

前回の記事はこちら。

【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はデータベースにレコードを追加するAddNewメソッドを使ったシンプルな方法についてお伝えします。

エクセルVBAでAccessデータベースにレコードを追加する方法についてお伝えしました。

前回は、たった一行だけの追加でしたが、実際にエクセルからレコード追加をする場合は複数行を一気に追加したいはず…

ということで、今回はエクセルVBAでAccessデータベースに複数のレコードを追加する方法についてお伝えします。

あと大量のデータを追加する際に参考となる実行速度についても二つのレコード追加方法のパターンで検証をしています。

スポンサーリンク

前回のおさらい:レコードを1行追加する

まずは前回のおさらいから。

プログラムはこちらでした。

Sub addRecord()

Dim strFileName As String
strFileName = "test2.accdb"

Dim adoCn As Object 'ADOコネクションオブジェクト
Set adoCn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続

Dim adoRs As Object 'ADOレコードセットオブジェクト
Set adoRs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成

With adoRs
    .Open "都道府県", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く

    .AddNew
        !登録日 = #2016/09/12#
        !都道府県 = "岩手県"
        !推計人口 = 1284384
    .Update

    .Close 'レコードセットのクローズ

End With

adoCn.Close 'コネクションのクローズ

Set adoRs = Nothing
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

実際にレコードを追加する部分は13行目~24行目のWithのブロック内ですね。

まず、14行目でレコードセットオープンしますが、その際に「都道府県」という名前のテーブルを指定しつつ、adOpenKeysetおよびadLockOptimisticオプションを指定して書き込みができるようにしています。

実際に追加をしているのはAddNewメソッドとUpdateメソッドの間ですね。現在の位置のフィールドは「ADODBレコードセットオブジェクト!フィールド名」で表現できますので、そこに値を代入していけばよいということになります。

この処理を複数にしていきたいということなのですが…簡単ですよね。

Accessのテーブルに複数のレコードを追加する

例として以下のようなシートがあります。

エクセルVBAからAccessテーブルに追加するデータ

これを前回と同様、Accessデータベース上のテーブル「都道府県」に挿入していきます。「都道府県」のフィールドは

|ID|登録日|都道府県|推計人口|

という構成です。

このテーブルに先ほどのシートのデータを一気に追加していきます。

冒頭のプログラムの13行目~24行目の部分を以下のように修正してみました。

'ワークシートとイテレータの準備
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")

Dim i As Long
i = 2

With adoRs
    .Open "都道府県", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く

    Do While ws.Cells(i, 1).Value <> ""
        .AddNew
            !登録日 = Date '今日の日付
            !都道府県 = ws.Cells(i, 2).Value
            !推計人口 = ws.Cells(i, 4).Value
        .Update
        i = i + 1
    Loop

    .Close 'レコードセットのクローズ

End With

Do While文でシートの2行目から最終行までを回して、それぞれ2列目の都道府県名と4列目の推計人口をフィールドに代入します。登録日は今日の日付ですので、Date文で取得できますね。

実行しますと

エクセルVBAでAccessテーブルに複数レコードを追加

このように一気にレコードを追加することができました。

レコード追加の実行速度を検証する

前回記事でチラリと

ところで、SQLにちょっと詳しい方は「SQLのINSERT文を使ってもいいのでは?」と思われるかも知れません。

もちろん、その方法でも目的を達成することができるのですが、エクセルVBAからレコードを追加する場合、今回のADODBレコードセットオブジェクトに対するAddNewメソッド、Updateメソッドの方法のほうが処理が速いのです。

などとお伝えしたのですが、これを機に検証をしてみたいと思います。

まず以下の記事を参考に、実行速度を測れるようにタイマーをセットしつつ

遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法
エクセルVBAの実行速度が遅い・重いって時ありませんか?そんな時にエクセルVBAのプログラムの処理速度を速くするテクニックを紹介します。プログラムの実行時間を測定する方法も合わせてお伝えします。

実行速度の差が分かるように1000倍回すようにして測定をしたいと思います。これで47,000行の処理になります。

ADODBレコードセットオブジェクトのメソッドを使う場合の実行速度

今回の、ADODBレコードセットオブジェクトのAddNewメソッドとUpdateメソッドを使う方法を試してみます。

Dim i As Long, j As Long

With adoRs
    .Open "都道府県", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く

    For j = 1 To 1000
        i = 2
        Do While ws.Cells(i, 1).Value <> ""
            .AddNew
                !登録日 = Date '今日の日付
                !都道府県 = ws.Cells(i, 2).Value
                !推計人口 = ws.Cells(i, 4).Value
            .Update
            i = i + 1
        Loop

    Next j
    .Close 'レコードセットのクローズ

End With

こちらで実行をすると…

エクセルVBAのADODBレコードセットオブジェクトのメソッドを使ったレコード追加の実行速度

その実行速度は13秒でした。

INSERT INTO文でSQL実行をする場合の実行速度

今度は、INSERT INTO文というレコードを追加するSQLを実行してレコードを追加する方法です。

参考までにプログラムの変更箇所はこちらです。

Dim strSQL As String 'SQL文

Dim i As Long, j As Long
For j = 1 To 1000

    i = 2

    Do While ws.Cells(i, 1).Value <> ""

        strSQL = "INSERT INTO 都道府県 (登録日,都道府県,推計人口) VALUES (#" & Date & "#,'" & ws.Cells(i, 2).Value & "'," & ws.Cells(i, 4).Value & ");"
        adoCn.Execute strSQL
        i = i + 1

    Loop

Next j

実行をしてみますと

エクセルVBAでINSERT INTO文実行でレコード追加をした場合の実行速度

なんと、実行速度が1分14秒もかかってしまいました。先の例の5.7倍です。

SQL文を作る部分も文字列の結合がなかなかやっかいですし、ADODBレコードセットオブジェクトのメソッドを使ったほうが断然良さそうですね。

まとめ

エクセルVBAでAccessデータベースに複数のレコードを追加する方法についてお伝えしました。

また、今回紹介したADODBレコードセットオブジェクトのAddNewおよびUpdateメソッドを使う方法とともに、SQLのINSERT INTO文で追加する方法の実行速度について調査し、メソッドを使う方法のほうが実行速度が速いことを確認しました。

さて、次回ですが今回のプログラムを発展させて、テーブルに存在しないデータのみを追加するプログラムを作っていきたいと思います。

【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
エクセルVBAでAccessデータベースを操作する方法についてシリーズでお伝えしています。今回はエクセルVBAでAccessデータベースに特定のデータが存在するかどうかを判定するプログラムを作ります。

どうぞお楽しみに!

連載目次:エクセルVBAでエクセルからAccessデータベースを操作する

エクセルと相性の良いデータベースとして第一候補として挙がるAccess。エクセルをインターフェース、AccessをデータベースとしてエクセルVBAを使って連携する方法とその様々なテクニックについてお伝えしていきます。
  1. なぜエクセルとAccessデータベースを連携するのが良いのか、またその際の注意点
  2. エクセルVBAでAccessデータベースと接続する最もシンプルなプログラム
  3. 【エクセルVBA&Access連携】SQL文でデータを抽出する最も簡単なプログラム
  4. 【エクセルVBAでAccess連携】SQLのSELECT,FROM,WHEREによる様々なデータ抽出方法
  5. 【エクセルVBAでAccess連携】SQLのJOINを使って複数のテーブルを組み合わせてデータを取り出す
  6. 【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法
  7. エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
  8. 【エクセルVBAでAccess連携】データベースに特定条件のデータが存在するかどうかを判定する
  9. エクセルVBAからAccessデータベースのレコードを呼び出して修正して上書き更新する方法
  10. エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
  11. エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について
  12. エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
  13. エクセルVBAでADODBレコードセットをSortメソッドで並び替えする方法
  14. エクセルVBAでAccessデータをグループで集計して抽出するGROUP BY句と集計関数の使い方
  15. エクセルVBAでAccessデータを集合関数による条件で抽出するHAVING句の使い方
  16. エクセルVBAでAccessデータベースからFormat関数によるSQL文で特定の日付で抽出
  17. エクセルVBAでのAccessデータベース操作にトランザクション処理を入れる

タイトルとURLをコピーしました