DAOとADOのハイブリッド設計：ローカル一時テーブルと外部DBの連携アーキテクチャ
レガシーシステムの最前線において、Accessは依然として「最強のデスクトップ・フロントエンド」としての生存戦略を歩んでいる。しかし、数百万レコード規模のトランザクション処理や、厳格な排他制御が求められる外部RDBMS（SQL Server等）との連携において、純粋なDAO（Data Access Objects）の直叩きや、非効率なADO（ActiveX Data Objects）によるレコードセット走査は、ネットワーク帯域とクライアントのメモリを確実に窒息させる。
真にスケーラブルなシステムを構築するためには、「ローカルでの爆速処理はDAOに委ね、外部RDBMSへの確実な永続化とセットベースのバルク処理はADOに託す」という、ハイブリッドアーキテクチャの極限を理解していなければならない。
今回は、DAOのJet/ACEエンジンが持つインメモリ処理の優位性と、ADOが持つOLE DB経由の強力なコマンド実行力を融合させた、実務における最高峰のデータフロー設計を解説する。
—
1. なぜハイブリッドなのか？ アーキテクチャの本質的優位性
大規模なデータ処理をAccessで行う場合、以下の2つの致命的なアンチパターンが存在する。
1. ADOによるローカルレコードセットの頻繁な操作
- ADOは外部DB接続に最適化されているが、AccessのローカルJet/ACEエンジンとの親和性はDAOに劣る。特にCursorTypeやLockTypeの選定を誤ると、ローカル一時テーブルであっても不必要なロック競合やメモリ肥大化を招く。
2. DAOによる外部SQL Serverへの直接クエリ
- ODBC経由でDAOから直接SQL Serverのテーブルを操作すると、Jetエンジンがオプティマイザとして介在するため、本来SQL Server側で実行されるべき複雑な実行計画が歪められ、ネットワークを不必要にスパムする。
究極のデータフロー
[外部DB (SQL Server)]
│
│ ① ADO：必要最小限のデータセットを非同期/高速バルク取得
▼
[Access ローカル一時テーブル]
│
│ ② DAO：インデックスを利かせた秒速のレコード加工・突合・演算
▼
[結果データ]
│
│ ③ ADO：トランザクション制御下での確実な一括書き戻し (Bulk Insert/Update)
▼
[外部DB (SQL Server)]
この分離統治こそが、現場のエンジニアが習得すべき唯一無二のパターンである。
—
2. 実装：ハイブリッド・データパイプラインの構築
以下に、実務の現場でそのまま稼働させられるプロダクションレベルのコードを示す。エラーハンドリング、オブジェクトのライフサイクル管理、そしてメモリの断片化を防ぐための明示的な解放処理を徹底している。
Option Compare Database
Option Explicit
‘ ==============================================================================
‘ 外部DB連携ハイブリッド・プロセッサ
‘ 概要: SQL Serverからデータを取得し、DAOによるローカル一時テーブルでの高速演算後、
‘ 再びADOを用いてトランザクション制御下で結果を書き戻す。
‘ ==============================================================================
Public Sub ExecuteHybridDataPipeline()
‘ 接続文字列（環境に合わせて変更）
Const CONN_STR As String = “Provider=SQLOLEDB.1;Data Source=ServerName;Initial Catalog=DBName;Trusted_Connection=yes;”
Dim cnn As Object _ ‘ ADODB.Connection
Dim cmd As Object _ ‘ ADODB.Command
Dim rsSource As Object _ ‘ ADODB.Recordset
Dim dbLocal As DAO.Database
Dim tdLocal As DAO.TableDef
Dim rsLocal As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strTempTableName As String
On Error GoTo ErrorHandler
‘ 1. 【ADO層】外部DBから初期データを取得するための接続確立
Set cnn = CreateObject(“ADODB.Connection”)
cnn.CommandTimeout = 60
cnn.Open CONN_STR
‘ 2. 【DAO層】ローカル一時テーブルの動的生成
‘ ※実務ではランダムサフィックスを付与してマルチユーザ環境での衝突を防ぐ
Set dbLocal = CurrentDb
strTempTableName = “tmp_ProcessBuffer_” & Format(Now, “yyyymmddhhnnss”)
‘ DAOを使ったテーブル定義のコードレス生成
Set tdLocal = dbLocal.CreateTableDef(strTempTableName)
With tdLocal
.Fields.Append .CreateField(“ID”, dbLong)
.Fields.Append .CreateField(“TargetCode”, dbText, 50)
.Fields.Append .CreateField(“CalculatedValue”, dbDouble)
End With
dbLocal.TableDefs.Append tdLocal
‘ 高速化のためのインデックス付与（DAOならではの強み）
Dim idx As DAO.Index
Set idx = tdLocal.CreateIndex(“PrimaryKey”)
idx.Fields.Append idx.CreateField(“ID”)
idx.Primary = True
idx.Unique = True
tdLocal.Indexes.Append idx
‘ 3. 【ADO → DAO】外部データを取得し、DAOレコードセットでローカルへ高速バルク流し込み
Set rsSource = CreateObject(“ADODB.Recordset”)
rsSource.Open “SELECT ID, TargetCode, RawValue FROM vw_SourceView WHERE Processed = 0”, cnn, 3, 1 ‘ adOpenStatic, adLockReadOnly
Set rsLocal = dbLocal.OpenRecordset(strTempTableName, dbOpenTable, dbAppendOnly)
Do While Not rsSource.EOF
rsLocal.AddNew
rsLocal!ID = rsSource!ID
rsLocal!TargetCode = rsSource!TargetCode
‘ ローカルでの複雑な初期演算をここでインメモリ処理
rsLocal!CalculatedValue = Nz(rsSource!RawValue, 0) 1.1 ‘ 例: 係数掛け合わせ
rsLocal.Update
rsSource.MoveNext
Loop
‘ ここでADO/DAOのソース側レコードセットを即座に破棄（メモリ最適化）
rsSource.Close
Set rsSource = Nothing
rsLocal.Close
Set rsLocal = Nothing
‘ 4. 【DAO層】ローカルテーブル内での高度なデータ加工・突合（DAOクエリの真骨頂）
‘ 例としての集計・更新クエリの実行
dbLocal.Execute “UPDATE [” & strTempTableName & “] SET CalculatedValue = CalculatedValue + 100 WHERE TargetCode LIKE ‘A%’;”, dbFailOnError
‘ 5. 【ADOトランザクション層】処理結果を外部DBへ書き戻し
cnn.BeginTrans
‘ ADO Commandオブジェクトを使用した効率的な一括反映
Set cmd = CreateObject(“ADODB.Command”)
Set cmd.ActiveConnection = cnn
‘ DAOで加工し終わったローカルテーブルをADO経由で走査して外部へ書き戻す
Set rsLocal = dbLocal.OpenRecordset(strTempTableName, dbOpenSnapshot)
Do While Not rsLocal.EOF
cmd.CommandText = “UPDATE TargetTable SET ProcessedValue = ?, Status = 1 WHERE ID = ?”
cmd.Parameters.Append cmd.CreateParameter(, 5, 1, , rsLocal!CalculatedValue) _ ‘ adDouble
cmd.Parameters.Append cmd.CreateParameter(, 3, 1, , rsLocal!ID) _ ‘ adInteger
cmd.Execute
‘ パラメータコレクションのクリア（メモリリーク防止の鉄則）
Set cmd.Parameters = Nothing
rsLocal.MoveNext
Loop
cnn.CommitTrans
‘ 6. 【クリーンアップ】一時テーブルとオブジェクトの完全消去
rsLocal.Close
Set rsLocal = Nothing
dbLocal.TableDefs.Delete strTempTableName
MsgBox “ハイブリッド・パイプライン処理が正常に完了しました。”, vbInformation
Exit Sub
ErrorHandler:
‘ 異常系トランザクションロールバック
If Not cnn Is Nothing Then
If cnn.State = 1 Then cnn.RollbackTrans
End If
‘ エラー時のローカル一時テーブル残留を防ぐ安全策
On Error Resume Next
If Not rsLocal Is Nothing Then rsLocal.Close
If Not rsSource Is Nothing Then rsSource.Close
If Not dbLocal Is Nothing Then
dbLocal.TableDefs.Delete strTempTableName
End If
MsgBox “致命的なエラーが発生しました: ” & Err.Description, vbCritical
-End Sub
—
3. チーフアーキテクトが教える「極限の知見」と実務の罠
上記のコードを単なる「動くサンプル」として終わらせず、エンタープライズ環境で破綻させないための設計思想を解説する。
① オブジェクトのライフサイクルと「メモリ断片化」の回避
VBAのガベージコレクション（特にCOMコンポーネントの参照カウント）は完璧ではない。
特に `ADODB.Command` をループ内で使用する際、`Parameters.Append` を繰り返すと、VBAランタイム内部でメモリの断片化（Heap Fragmentation）が発生し、長時間稼働時にAccessが沈黙するという現象を引き起こす。
上記のコードで `Set cmd.Parameters = Nothing` をループの都度実行しているのは、このメモリリークを物理的に遮断するための必須のテクニックである。
② マルチユーザ環境における一時テーブルの衝突回避
複数名が同時にこのVBAを実行した場合、`tmp_ProcessBuffer` のような固定名の一時テーブルは「既に存在します」エラーを引き起こす。
これを防ぐため、タイムスタンプや `Environ(“UserName”)`、さらにGUID生成APIなどを組み合わせ、一意な物理テーブル名を動的に生成・破棄する設計がマストとなる。
DAOの `TableDefs.Delete` は、実行時に即座にJet/ACEエンジンのカタログから消去されるため、ローカルDBの肥大化（Bloat）を防ぐ意味でも極めて有効である。
③ トランザクション境界の設計
外部RDBMSへの書き戻しフェーズ（ステップ5）において、DAOではなくADOのトランザクション（`cnn.BeginTrans`）を使用している点に注目してほしい。
Accessのローカルエンジン（DAO）のトランザクションは、外部ODBC経由の更新に対して完全な分散トランザクション（MS DTC等）を強要され、パフォーマンスが劇的に低下するか、環境によってはサポートされない。
「ローカルは自由奔放に高速処理、外部への確定時のみADOで厳格にACID特性を担保する」というこの割り切りが、実務システムを安定させる唯一の解である。
—
総括
Access VBAはレガシーな言語と侮られがちだが、DAOとADOの特性を極限まで理解し、適材適所で組み合わせたハイブリッド設計を行うことで、モダンなクライアント・サーバシステムに匹敵する堅牢かつ高速なデータパイプラインを構築できる。
コードの行数を減らすことではなく、「どこでメモリを消費し、どこでネットワークコストが発生しているか」を常に頭脳のCPUでシミュレーションすること。それこそが、真のエンジニアリングである。
