Access VBAを掌握する極限の知見：DAOとADOのハイブリッド設計がもたらす極限のパフォーマンス
開発現場でよく見かける光景がある。「とりあえずADOで全部繋いでSQL Serverを叩く」「いや、Accessなんだから全部DAOでローカルをいじるべきだ」。
議論はいつも平行線をたどる。しかし、プロのアーキテクトの答えは一つだ。
「適材適所。DAOのローカル処理スピードと、ADOのトランザクション・リモート操作能力を結婚させろ」
今回は、Accessのローカル一時テーブルをDAOで極限まで高速に料理し、その結果をADOで安全にSQL Serverなどの外部データベースへ一括書き戻す、実務最強のハイブリッド・アーキテクチャを伝授する。
—
なぜ「純血主義」は破綻するのか？
まず、なぜ片方の技術だけでは実務の壁にぶ돌かるのかを理解してほしい。
1. 「すべてADO」の罠
ADO（ActiveX Data Objects）はリモートDB（SQL Server等）との通信やトランザクション制御において最強だ。しかし、Accessのローカル環境において、ADOのレコードセット（`Open`によるクライアントサイドカーソル等）を操作すると、ネットワーク経由ではないにしろ、無駄なCOMオーバーヘッドとロックの競合が発生する。特に数万件のループ処理や複雑な一時的データの加工において、DAOの足元にも及ばないほど遅い。
2. 「すべてDAO」の限界
DAO（Data Access Objects）はAccessのエンジン（Jet/ACE）と一体化しており、ローカルテーブルの操作においては神速のパフォーマンスを誇る。しかし、ODBC経由で外部のSQL ServerをDAOで直接叩こうとすると、リンクテーブル経由での暗黙のフェッチや、トランザクションの分離レベル、スケーラビリティの面で致命的なボトルネックを露呈する。さらに、リモートDBのネイティブなストアドプロシージャや高度なSQL構文を扱えない。
答え：ハイブリッド設計という「最適解」
この課題を解決する唯一にして最大の正解が、以下のパイプラインだ。
1. 外部DBからのデータ取得・準備: ADOを用いてSQL Serverから必要なデータを高速にフェッチする。
2. ローカルでの爆速加工（DAO）: 取得したデータをAccess内のローカル一時テーブルに流し込み、DAOのレコードセットやトランザクションを用いて、メモリ上でインデックスをフル活用した秒速のデータ加工・集計を行う。
3. 外部DBへの一括書き戻し（ADO）: 加工済みのクリーンな結果セットを、ADOのトランザクションを用いてSQL Serverへ安全にバルクインサート（あるいは更新）する。
このデータフローをマスターすれば、あなたの作るAccessアプリは見違えるほど軽快になり、同時にエンタープライズレベルの堅牢性を手に入れる。
—
実装アーキテクチャ：プロダクションコード
百聞は一見にしかず。現場でそのまま使える、堅牢性とパフォーマンスを極限まで高めたモジュールを提示する。エラーハンドリング、オブジェクトの明示的な解放（メモリリークの根絶）、トランザクション制御のすべてが組み込まれている。
前提条件
- 参照設定に `Microsoft DAO 3.6 Object Library`（または最新のACE）および `Microsoft ActiveX Data Objects x.x Library` が追加されていること。
- ローカルに作業用の一時テーブル `tmp_WorkBuffer` が存在すること。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 模範的ハイブリッド処理プロシージャ
‘ 外部SQL Serverからデータを取得 -> DAOでローカル加工 -> ADOで結果を書き戻す
‘ =========================================================================
Public Sub ExecuteHybridPipeline()
‘ — ADO関連変数 —
Dim cnExt As Object ‘ ADODB.Connection
Dim cmdExt As Object ‘ ADODB.Command
Dim rsExt As Object ‘ ADODB.Recordset
Dim connStr As String
‘ — DAO関連変数 —
Dim db As DAO.Database
Dim rsLocal As DAO.Recordset
‘ — 制御・カウンタ変数 —
Dim lngProcessCount As Long
On Error GoTo ErrorHandler
‘ 1. 外部DB接続文字列の構築 (SQL Serverの例)
connStr = “Provider=SQLOLEDB;Server=192.168.1.50;Database=EnterpriseDB;Uid=app_user;Pwd=secret_password;”
Set cnExt = CreateObject(“ADODB.Connection”)
cnExt.CommandTimeout = 60
cnExt.Open connStr
‘ 2. DAOの初期化とローカル一時テーブルの清掃
Set db = CurrentDb()
db.Execute “DELETE FROM tmp_WorkBuffer;”, dbFailOnError
‘ 3. ADOを使用して外部からデータを取得し、DAOでローカルへ高速書き込み
‘ ※大量データを扱うため、サーバー側からストリーム状に取得する
Set cmdExt = CreateObject(“ADODB.Command”)
With cmdExt
Set .ActiveConnection = cnExt
.CommandText = “SELECT TargetID, SourceVal1, SourceVal2 FROM vw_UnprocessedData”
.CommandType = 1 ‘ adCmdText
Set rsExt = .Execute
End With
‘ ローカルテーブルをDAOでオープン（テーブル直接参照はバッファ効率が最高）
Set rsLocal = db.OpenRecordset(“tmp_WorkBuffer”, dbOpenTable, dbDenyWrite)
‘ トランザクション開始（DAOのバルクインサート高速化）
db.BeginTrans
lngProcessCount = 0
Do While Not rsExt.EOF
rsLocal.AddNew
‘ 外部データのマッピング
rsLocal!TargetID = rsExt.Fields(“TargetID”).Value
‘ ここでDAO側で簡単なビジネスロジック（演算など）を適用
rsLocal!CalculatedVal = Nz(rsExt.Fields(“SourceVal1”).Value, 0) Nz(rsExt.Fields(“SourceVal2”).Value, 1)
rsLocal.Update
lngProcessCount = lngProcessCount + 1
rsExt.MoveNext
Loop
db.CommitTrans
rsLocal.Close
rsExt.Close
‘ — ここから先：加工されたローカルデータをADOで外部へ書き戻す —
‘ 外部DB側のトランザクション開始
cnExt.BeginTrans
‘ ADO経由で外部の確定用テーブルへUPSERT（またはINSERT）を実行
‘ ※パフォーマンスのため、パラメータクエリを使用する
Dim sqlUpdate As String
sqlUpdate = “INSERT INTO tbl_ResultMaster (TargetID, FinalValue, ProcessedDate) VALUES (?, ?, ?)”
Set cmdExt = CreateObject(“ADODB.Command”)
With cmdExt
Set .ActiveConnection = cnExt
.CommandText = sqlUpdate
.CommandType = 1 ‘ adCmdText
‘ パラメータの定義（型安全性の確保）
.Parameters.Append .CreateParameter(“p1”, 3, 1, , 0) ‘ adInteger
.Parameters.Append .CreateParameter(“p2”, 5, 1, , 0) ‘ adDouble
.Parameters.Append .CreateParameter(“p3”, 135, 1, , 0) ‘ adDBTimeStamp
End With
‘ 加工済みのローカルテーブルを再びDAOで読み込む
Set rsLocal = db.OpenRecordset(“SELECT TargetID, CalculatedVal FROM tmp_WorkBuffer”, dbOpenSnapshot)
Dim currentTimestamp As Date
currentTimestamp = Now()
Do While Not rsLocal.EOF
cmdExt.Parameters(“p1”).Value = rsLocal!TargetID
cmdExt.Parameters(“p2”).Value = rsLocal!CalculatedVal
cmdExt.Parameters(“p3”).Value = currentTimestamp
cmdExt.Execute
rsLocal.MoveNext
Loop
‘ 外部DBのトランザクション確定
cnExt.CommitTrans
MsgBox “ハイブリッド処理が正常に完了しました。処理件数: ” & lngProcessCount & “件”, vbInformation, “アーキテクチャ・成功”
GoTo Cleanup
ErrorHandler:
‘ DAOトランザクションのロールバック
If Not db Is Nothing Then
On Error Resume Next
db.Rollback
End
‘ ADOトランザクションのロールバック
If Not cnExt Is Nothing Then
If cnExt.State = 1 Then cnExt.RollbackTrans
End If
MsgBox “致命的なエラーが発生しました: ” & Err.Description, vbCritical, “アーキテクチャ・異常終了”
Cleanup:
‘ — 厳格なオブジェクト解放（メモリリークの完全防止） —
On Error Resume Next
If Not rsLocal Is Nothing Then rsLocal.Close: Set rsLocal = Nothing
If Not rsExt Is Nothing Then rsExt.Close: Set rsExt = Nothing
If Not cmdExt Is Nothing Then Set cmdExt = Nothing
If Not cnExt Is Nothing Then
If cnExt.State = 1 Then cnExt.Close
Set cnExt = Nothing
End If
Set db = Nothing
End Sub
—
現場で差が出る：プロのエンジニアが守るべき3つの鉄則
上記のコードを見ればわかる通り、プロの仕事とは「動くコードを書くこと」ではなく「破綻しない構造を作ること」だ。実務で絶対に外してはならないポイントを解説する。
1. DAOの `dbOpenTable` とトランザクションの組み合わせ
ローカルテーブルに書き込む際、`dbOpenDynaset` や `dbOpenSnapshot` ではなく `dbOpenTable` を使い、かつ `dbBeginTrans` で囲みなさい。Jet/ACEエンジンは、テーブル直接オープン時のトランザクション内バッチ書き込みにおいて、類まれな爆速を発揮する。数千件のループであっても、一瞬で処理が完了するはずだ。
2. ADOパラメータクエリによるインジェクション防御と型安全
外部DBへ書き戻す際、SQL文を文字列結合（`”INSERT INTO … VALUES (” & val & “)”`）で組み立てる素人がいるが、これは業務アプリケーションにおける最大の罪悪である。必ず `ADODB.Command` と `Parameters.Append` を使いなさい。型が厳密にバインドされるため、SQLインジェクションを防ぐだけでなく、SQL Server側の実行プランキャッシュ効率も劇的に向上する。
3. 多重の例外処理（ロールバック）の担保
ハイブリッド設計の最大の難所は、「Access側（DAO）は成功したが、外部DB側（ADO）でコケた時」の状態整合性だ。
上記のコードでは、エラー発生時にDAOとADOの双方のトランザクションを独立してロールバックし、さらに `On Error Resume Next` を駆使してクリーンアップ処理（メモリ解放）を確実に行う構造にしている。片方のコネクションが宙ぶらりんになるバグを完全に排除しているのだ。
—
まとめ：Accessの限界を超えるために
Accessは「おもちゃのDB」と揶揄されることがある。しかし、それは使う人間の設計が未熟な場合の話だ。
DAOの持つローカル・エンジンへの深い親和性と、ADOの持つ堅牢な外向きの接続能力。この二つを正確に理解し、一時テーブルという滑走路を介してデータをバトンタッチするハイブリッド設計を手に入れた瞬間から、あなたの作るシステムは、もはや「ただのAccessアプリ」ではなくなる。
現場のエンジニアよ、古い設計思想を捨て、極限のパフォーマンスと美しさを兼ね備えたアーキテクチャを実装せよ。
