エクセルVBAでAccessデータベースの複数のレコードを上書き更新する場合の実行速度について


starting-point

photo credit: itmpa Crazy Golf via photopin (license)

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

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

前回はこちらの記事。

エクセルVBAでAccessデータベースの複数のレコードをまとめて上書き更新する方法
エクセルVBAでAccessデータベースを操作する方法についてのシリーズです。今回はSQLのUPDATE文を使ってAccessデータベースの複数レコードをまとめて上書き更新する方法についてお伝えします。

Accessデータベースの複数のレコードをまとめて上書き更新する方法についてお伝えしました。

更新処理については、SQLのUPDATE文を実行するという方法を採用していたのですが、実はこの方法、実行速度が遅いんじゃないの?という懸念があります。

というのも、レコードの追加についてはSQLのINSERT INTO文による方法よりも、ADODBレコードセットオブジェクトのAddNewとUpdateメソッドを使ったほうが圧倒的に実行速度が速かったんですね。

エクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度について
エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。今回はエクセルVBAでAccessデータベースに複数のレコードを追加する方法とその実行速度についてお伝えします。

ではレコードの更新についてはどうなんだ?本当にSQLで良いのか??

ということなんです。

今回は、エクセルVBAでAccessデータべースの複数のレコードをADODBレコードセットに対するメソッドを使ってまとめて上書き更新する方法についてお伝えしつつ、実行速度について検証をしていきたいと思います。

スポンサーリンク

お題のデータベース

お題のデータベースは前回同様こちらです。

Accessのデータベース

テーブル名は「成績表」、フィールドの構成は

|ID|名前|国語|数学|英語|

です。レコード数はわけあって9,000行を用意しています。

SQLのUPDATE文で更新する場合の実行時間

以下が前回使用したプログラムです。

Sub updateRecords()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet3")

Dim strFileName As String
strFileName = "test4.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 strSQL As String

Dim i As Long
i = 2
Do While ws.Cells(i, 1) <> ""

    strSQL = _
        "UPDATE 成績表 " & _
        "SET " & _
            ws.Range("C1").Value & "=" & ws.Cells(i, 3).Value & "," & _
            ws.Range("D1").Value & "=" & ws.Cells(i, 4).Value & "," & _
            ws.Range("E1").Value & "=" & ws.Cells(i, 5).Value & " " & _
        "WHERE ID=" & ws.Cells(i, 1).Value

    adoCn.Execute strSQL 'SQLを実行
    i = i + 1
Loop

adoCn.Close 'コネクションのクローズ
Set adoCn = Nothing  'オブジェクトの破棄

End Sub

以下記事を参考にタイマーをセットして実行してみましょう。

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

すると…

エクセルVBAでSQL実行でレコードを上書き更新した実行時間

15秒…んー、ちょっと遅い気がしますね。

ADODBレコードセットオブジェクトのFindとUpdateメソッドでレコード更新

さて、ではSQLのUPDATE文ではない方法で、Accessデータベースを上書き更新していきましょう。

上記13行目~29行目を以下に書き換えます。

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

Dim i As Long
i = 2

With adoRs
    .Open "成績表", adoCn, adOpenKeyset, adLockOptimistic 'レコードセットを開く

    Do While ws.Cells(i, 1) <> ""
        .MoveFirst 'カレントレコードを最初の行に移動
        .Find "ID=" & ws.Cells(i, 1).Value
            !国語 = ws.Cells(i, 3).Value
            !数学 = ws.Cells(i, 4).Value
            !英語 = ws.Cells(i, 5).Value
        .Update
        i = i + 1
    Loop

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

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

今回、ADODBレコードセットオブジェクトを使いますので、1行目と2行目でオブジェクトを生成して準備をしています。

レコードの上書きの場合も、書込みができるようにレコードセットオブジェクトをオープンする必要があります。その箇所が8行目ですね。ここのOpenメソッドについては、以下記事も参考ください。

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

Do While~Loopの中が、今回の新しい部分になります。

Findメソッドで検索したレコードをカレントレコードにする

レコードセットから特定のレコードを検索する場合は、Findメソッドを使います。

書き方は

ADODBレコードセットオブジェクト.Find 検索式

と書きます。

検索式は「フィールド名=値」の形式で文字列形式で指定をします。値は、数値、文字列、日付をとることができますが、文字列の場合は値をシングルクォーテーションで、日付の場合は値をパウンドで囲います。したがって、それぞれの検索式は

“フィールド名=数値”
“フィールド名=’文字列'”
“フィールド名=#日付#”

と指定することになります。

Findメソッドの検索方向と検索結果についての注意点

Findメソッドを実行すると、カレントレコードが検索式と一致したレコードがカレントレコードにセットされます。

ですから13行目~15行目により、カレントコードのそれぞれのフィールドの値が代入され、Updateメソッドをすることで、その内容が確定されます。

気を付けなくてはいけないこととして、Findメソッドはデフォルトの場合

  • カレントレコードから後のレコード方向に検索する
  • 最終行までに検索値が見つからなった場合は、EOFがセットされる

という点があります。

これが原因で

エクセルVBAで実行時エラー3021

このように「実行時エラー ‘3021’」というエラーが出ることがあります。

この原因について、例えばを使って説明をします。

レコードセットがID昇順に並んでいる状態で、”ID=1000″を検索した後に、”ID=500″を検索するとどうなるか?

  1. “ID=1000″をFindメソッドで検索すると、カレントレコードが”ID=1000″の位置にセットされる
  2. “ID=1000″のレコードについて、無事に上書き更新&確定される
  3. ルぎに”ID=500″をFindメソッドで検索すると、”ID=1000″以降が検索対象となるので、ヒットしない
  4. EOFがカレントレコードになる
  5. EOFには値は代入できないので、エラーが発生する

Findメソッドを使う場合は、常に最初の行から検索をするように工夫をする必要があります。

MoveFirstメソッドでカレントレコードを最初の行に移動する

そこで11行目にMoveFirstメソッドが存在しているわけです。

MoveFirstメソッドはその名の通り、レコードセットのカレントレコードを最初のレコードに移動するメソッドです。

同様に、カレントレコードを移動するMoveLast、MoveNext、MovePreviousというメソッドもありますので、合わせて覚えておくと良いです。

ADODBレコードセットオブジェクト.MoveFirst :最初のレコードの移動
ADODBレコードセットオブジェクト.MoveLast :最後のレコードに移動
ADODBレコードセットオブジェクト.MovePrevious :1つ前のレコードに移動
ADODBレコードセットオブジェクト.MoveNext :1つ次のレコードに移動

MoveFirstをすると実行速度が遅くなる

では上記プログラムを実行してみましょう。

エクセルVBAでFind、Updateメソッドでレコードを上書き更新した実行時間

…遅!

1分38秒もかかってしまいました。現時点では、SQLの方法のほうが良い、という結論になってしまいます。

本当でしょうか…?

実はこの実行速度の遅さについてはFindメソッド、Updateメソッドが遅いのではなくて、MoveFirstメソッドが悪さをしています。

感覚的にわかると思いますが、毎回振り出しに戻されて検索しなおしなので、そのほうが時間がかかりそうです。

レコードの並び方を工夫すればMoveFirstメソッドは不要

例えば、レコードセットのIDも、エクセルシートのIDも、両方ともID昇順に並び替えがされているとどうなりますか?

次に検索するIDは、必ずカレントレコードよりも後のレコードに存在することになりますから、MoveFirstメソッドが必要なくなります。

では、実際にエクセルシートのほうもIDで昇順並び替えをした上で、MoveFirstメソッドをコメントアウトして実行してみましょう。

エクセルVBAでFind、UpdateメソッドでMoveFirstを使わずにレコードを上書き更新した実行時間

…速!!

たったの1秒です。やっぱりレコード更新の際にもSQL文実行よりもADODBをごにょごにょのほうが速いんですね…!

まとめ

以上、エクセルVBAでADODBレコードセットのFindメソッドとUpdateメソッドを使って複数レコードを上書き更新する方法についてお伝えしました。

そしてMoveFirstメソッドさえ使わなければ、SQLのUPDATE文よりも実行速度が速いということを確認しました。

しかしよく考えてみましょう。レコードの更新を、そんなに大量に一気にする必要があるケースってありますか?更新するレコード数がによって

  • 更新するレコードがそれほど多くない場合:SQLのUPDATE文で、またはMoveFirstを使ってもADODBレコードセットで
  • 更新するレコード数が大量にある場合:MoveFirstを使わないようにADODBレコードセットで

と使い分けるという方法もあるでしょう。

さて、次回ですがレコードの並び方を工夫するということで、SQLのSELECT文でレコードを取り出してくる際に、並び順をソートした状態で取得する方法についてお伝えします。

エクセルVBAでAccessのデータをORDER BYで並び替えをして取り出す
エクセルVBAでAccessデータベースを操作する方法についてお伝えしています。今回はSQLのSELECT文で取得したデータの順番についての検証、並び替えて取得するORDER BY句の使い方についてです。

どうぞお楽しみに!

連載目次:エクセル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をコピーしました