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

photo credit: how to clean a record via photopin (license)

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

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

前回の記事はこちら。

【エクセルVBAでAccess連携】データベースのテーブルにレコードを追加するシンプルな方法

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

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

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

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

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

前回のおさらい：レコードを1行追加する
Accessのテーブルに複数のレコードを追加する
レコード追加の実行速度を検証する
1. ADODBレコードセットオブジェクトのメソッドを使う場合の実行速度
2. INSERT INTO文でSQL実行をする場合の実行速度
まとめ
連載目次：エクセル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のテーブルに複数のレコードを追加する

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

これを前回と同様、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文で取得できますね。

実行しますと

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

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

前回記事でチラリと

ところで、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

こちらで実行をすると…

その実行速度は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

実行をしてみますと