AccessVBA 大量データ処理時のパフォーマンス改善

某システムのデータをSQL-Serverへ移行するにあたり、数万件レベルのデータをAccessで加工して移行データとする案件で。
よくあるインデックス付与etc.に加え、パフォーマンスに大きな効果のあった施策について記します。

---

1. アーリー・バインディング

外部ExcelファイルをAccessのテーブルに取り込むにあたり、インポート機能を使わずVBAでデータ加工しながら取り込もうとするとき、よくネットなどに記されているのは

　　Dim xls As Object
　　Set xls = CreateObject("Excel.Application")
　　・・・

という、いわゆる**「レイト・バインディング」**。
ただ、該当処理で約1.2万件のデータを取り込むのに、さすがに53分はかかり過ぎやと業を煮やして！

いろいろ調べた結果、
・Excelアプリケーションのオブジェクトを、VBA「参照設定」に設定しておく
・VBA内では、直接設定したExcelアプリケーションのオブジェクトを参照する
という方法（アーリー・バインディング）で処理効率が向上するそうで。
※図はあくまでイメージであり、実際の画面とは異なります

　　Dim xls As Excel.Application
　　Set xls = New Excel.Application
　　・・・

これと、後述するWith記法の採用とで、11,575件のExcelデータ取り込み処理が
　53分　⇒　16.5分
へと大幅改善しました（約220%のパフォーマンス向上）。

---

2. 参照先オブジェクトのオプション指定

上記1.で指定したExcelアプリケーションオブジェクトについて、
・画面表示：なし
・再計算：手動
・画面更新：なし
という設定を加えると、さらに処理効率はアップするそうで。
（ExcelVBAを単体で動かす時も同じらしいですが）

　　Dim xls As Excel.Application
　　Set xls = New Excel.Application
　　・・・
　　xls.Visible = False　　　　　　　　　　　　' 画面表示：なし
　　xls.Calculation = xlCalculationManual 　' 計算方法：手動
　　xls.ScreenUpdating = false　　　　　　　　' 画面表示：なし
　　・・・

このケースでは、データ読み込みに当たり、
　4,232件：6分18秒　⇒　4分19秒（約46%）
　24,301件：40秒　⇒　26秒（約54%)
と、それぞれ大幅なパフォーマンス向上が実現できました。

---

3. With記法

いちおう知ってはいましたが、それまでせいぜい数千件レベルを扱うアプリしか作ってこなかった立場としては、そこまで大切やとは思ってませんで・・・
ともあれ、処理相手（＝この場合、Excelのワークシート）がデカければデカいほど、With記法やと処理効率が良くなるらしいです。

　　Dim xls As Excel.Application
　　Set xls = New Excel.Application
　　・・・
　　With xls
　　　　.Visible = False　　　　　　　　　　　　' 画面表示：なし
　　　　.Calculation = xlCalculationManual 　' 計算方法：手動
　　　　.ScreenUpdating = false　　　　　　　　' 画面表示：なし
　　・・・
　　　　.Close
　　End With