Access VBAの深淵：DoCmd.TransferSpreadsheetを捨て、DAO.RecordsetでExcelへ「射出」する極意
Access開発に携わるエンジニアなら一度は直面する「肥大化したデータのエクスポート問題」。`DoCmd.TransferSpreadsheet` は確かに手軽だが、大規模データや複雑なクエリにおいては、そのブラックボックスな挙動ゆえにパフォーマンスの限界が露呈する。
真のシニアエンジニアは、OSのメモリ空間を意識し、DAOのパイプラインを直接叩くことで、Excelへのデータ転送を「作業」ではなく「射出」へと変える。今回は、`QueryDef` を起点とした、極めて高速かつメモリ効率の高いエクスポート手法を伝授する。
—
1. なぜ「TransferSpreadsheet」を避けるのか
`DoCmd.TransferSpreadsheet` は、内部でファイルI/Oを何度も往復し、Excel側のインスタンス管理が不透明であるため、大規模データではメモリリークや速度低下を引き起こす。
対して、DAO.Recordsetオブジェクトを配列化し、Excelの `Range.Value2` プロパティへ一括流し込み（バルクインサート）を行う手法は、単一のメモリ転送処理であるため、比較にならないほどの高速性を誇る。
—
2. 実装：DAO.QueryDefからExcelへの高速射出
以下のコードは、DAO.QueryDefのパラメーターを解決した結果を、メモリ上で配列化し、一気にExcelへ書き出すアーキテクチャである。
‘ 参照設定: Microsoft DAO 3.6 / Office Access Database Engine Object Library
‘ 参照設定: Microsoft Excel XX.0 Object Library
Public Sub ExportQueryDefToExcel(ByVal queryName As String, ByVal filePath As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim xlApp As Object ‘ 遅延バインディング推奨
Dim xlBook As Object
Dim xlSheet As Object
Dim varData As Variant
Set db = CurrentDb
Set qdf = db.QueryDefs(queryName)
‘ パラメータークエリの解決（必要に応じて値をセット）
‘ qdf.Parameters(0).Value = “Value”
Set rs = qdf.OpenRecordset(dbOpenSnapshot) ‘ Snapshotでメモリ消費を抑える
If rs.EOF Then GoTo Cleanup
‘ Recordsetから二次元配列へ一括転送
varData = rs.GetRows(rs.RecordCount)
‘ ※GetRowsは行と列が反転するため、データ量に応じて転送方式を最適化すること
Set xlApp = CreateObject(“Excel.Application”)
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Sheets(1)
‘ ヘッダーの書き込み
‘ …（省略）…
‘ 配列を一括でシートに書き込む（これが最速）
‘ Application.Transpose はデータ量が多いと遅延するため注意
xlSheet.Range(“A2”).Resize(UBound(varData, 2) + 1, UBound(varData, 1) + 1).Value = Application.Transpose(varData)
xlBook.SaveAs filePath
xlBook.Close False
Cleanup:
‘ オブジェクトの明示的解放はエンジニアの嗜み
If Not rs Is Nothing Then rs.Close: Set rs = Nothing
If Not qdf Is Nothing Then Set qdf = Nothing
If Not xlApp Is Nothing Then xlApp.Quit: Set xlApp = Nothing
Set db = Nothing
End Sub
—
3. シニアエンジニアが押さえるべき「最適化の要諦」
① `dbOpenSnapshot` の選定
`dbOpenDynaset` を使うと更新権限を持つためのオーバーヘッドが発生する。エクスポート専用であれば、必ず `dbOpenSnapshot` を指定せよ。これにより、レコードロックの管理コストが排除され、読み取り専用の高速パイプラインが構築される。
② `GetRows` と配列の転送
`rs.MoveNext` をループで回して `Cells(i, j).Value = …` とするコードは、現代のハードウェアに対しても「犯罪的」に遅い。Excelの `Range.Value` プロパティに二次元配列を直接突っ込むことで、Excel側のCOMインターフェース呼び出しを1回に集約できる。
③ COMオブジェクトの完全解放
`Set xlApp = Nothing` だけでは不十分なケースがある。特にレガシー環境では `xlApp.Quit` を明示し、かつGCを意識した参照カウントのゼロ化を徹底すること。Windows APIの `FindWindow` や `PostMessage` を使ってExcelプロセスを強制終了するハックが必要になる場面もあるが、まずはこの設計を遵守してほしい。
—
結論：システム間連携は「パイプライン」を意識せよ
AccessとExcelを繋ぐ際、多くの初心者は「AccessがExcelを操作する」という視点で考える。しかし、極限の自動化を目指す者は「AccessからExcelというメモリ空間へデータを射出する」というエンジニアリングを行うべきだ。
この手法をマスターすれば、数万件のレコードであっても、瞬時にファイル生成が完了するはずだ。技術の細部に宿る「パフォーマンスの真実」を見極め、あなたのシステムをより堅牢で軽快なものに進化させてほしい。
何かあれば、またコードの海で会おう。
