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

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レコードセットに対するメソッドを使ってまとめて上書き更新する方法についてお伝えしつつ、実行速度について検証をしていきたいと思います。

お題のデータベース
SQLのUPDATE文で更新する場合の実行時間
ADODBレコードセットオブジェクトのFindとUpdateメソッドでレコード更新
まとめ
連載目次：エクセルVBAでエクセルから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のプログラムの処理速度を速くするテクニックを紹介します。プログラムの実行時間を測定する方法も合わせてお伝えします。

すると…

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がセットされる

という点があります。

これが原因で

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

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

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

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

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

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

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

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

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

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