【Access VBA極致】DoCmd.TransferSpreadsheetを捨てろ。DAOとExcel Rangeの直結による「爆速」エクスポート術
Access開発の現場で、一度は通る道がある。
「クエリの結果をExcelに書き出す」というありふれた要件だ。
多くの開発者が迷わず `DoCmd.TransferSpreadsheet` を叩く。だが、もし君が「世界最高峰の業務自動化」を目指しているのなら、その選択は今すぐ捨てるべきだ。
なぜか？ `DoCmd.TransferSpreadsheet` は、ファイルI/Oのオーバーヘッドが大きく、フォーマットの柔軟性も皆無だからだ。数万件のデータを吐き出すたびに、PCがフリーズしたような挙動を見せるのは、君のコードがボトルネックになっている証拠である。
今日は、DAOの `QueryDef` をハブに、Excelの `Range` オブジェクトへメモリ経由で直接データを叩き込む、プロフェッショナルな実装手法を伝授する。
—
なぜDAO経由のRecordset操作が「最強」なのか
理由はシンプルだ。「中間ファイル」を生成しないからだ。
1. メモリ内展開: `QueryDef` でコンパイルされたSQLを `Recordset` として開く。
2. 一括転送: `Range.CopyFromRecordset` を用いることで、セル一つひとつに書き込むような低速なループを回避する。
3. ライフサイクル管理: `QueryDef` を動的に生成して即座に破棄することで、データベースの肥大化（IDの無駄な消費やロック）を防ぐ。
—
実践：プロダクションレベルの高速エクスポートコード
以下のコードは、保守性と堅牢性を担保したテンプレートだ。そのまま君のライブラリに組み込んでほしい。
‘ —————————————————————————
‘ 概要: QueryDefで作成したクエリをExcelへ爆速エクスポートする
‘ 備考: DoCmd.TransferSpreadsheetは使用せず、メモリベースで転送する
‘ —————————————————————————
Public Sub ExportQueryToExcel(ByVal strQueryName As String, ByVal strFilePath As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim xlApp As Object ‘ バインディングの柔軟性を考慮
Dim xlWb As Object
Dim xlWs As Object
Set db = CurrentDb
‘ 1. QueryDefの存在確認とRecordsetの取得
On Error Resume Next
Set qdf = db.QueryDefs(strQueryName)
If Err.Number <> 0 Then
MsgBox “指定されたクエリが見つかりません。”, vbCritical
Exit Sub
End If
On Error GoTo 0
Set rs = qdf.OpenRecordset(dbOpenSnapshot) ‘ スナップショットで読み取り専用かつ高速に
If rs.EOF Then
MsgBox “出力対象のデータが存在しません。”, vbExclamation
GoTo Cleanup
End If
‘ 2. Excelアプリケーションの起動
Set xlApp = CreateObject(“Excel.Application”)
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Sheets(1)
‘ 3. ヘッダーの書き込み
Dim i As Integer
For i = 0 To rs.Fields.Count – 1
xlWs.Cells(1, i + 1).Value = rs.Fields(i).Name
Next i
‘ 4. データの一括転送 (ここが最速のポイント)
xlWs.Range(“A2”).CopyFromRecordset rs
‘ 5. 保存とクリーンアップ
xlWb.SaveAs strFilePath
xlWb.Close SaveChanges:=False
xlApp.Quit
Cleanup:
If Not rs Is Nothing Then rs.Close: Set rs = Nothing
If Not qdf Is Nothing Then Set qdf = Nothing
Set xlWs = Nothing: Set xlWb = Nothing: Set xlApp = Nothing
Set db = Nothing
End Sub
—
匠のこだわり：設計上の注意点
1. `dbOpenSnapshot` の呪文
DAOでクエリを開く際、デフォルトの `dbOpenDynaset` を使ってはならない。編集不要なエクスポート処理において、レコードロックのオーバーヘッドを付与するのは愚行だ。必ず `dbOpenSnapshot` を指定せよ。
2. `CopyFromRecordset` の制限を理解する
このメソッドは非常に高速だが、「OLEオブジェクト型」や「ハイパーリンク型」のフィールドでエラーを吐くことがある。もしこれらのデータが含まれる場合は、事前にクエリ側で `Nz()` や `CStr()` を使い、型を文字列や数値にキャストしておくのがプロの作法だ。
3. 疎結合（Late Binding）の採用
コード内で `Object` 型を使用しているのは、Excelのバージョン差異による参照設定のトラブルを回避するためだ。現場に配布するツールであれば、環境依存を極限まで排除するのがリーダーの務めである。
—
まとめ：自動化の先にあるもの
「動けばいい」というレベルのコードは、いずれ技術的負債となって君の首を絞める。`QueryDef` を賢く使い、メモリとデータフローを制御する。この視点を持つだけで、君が作る業務ツールは「重い・遅い・止まる」という汚名を返上できるはずだ。
次は、このコードに「パラメータークエリを動的に構築する仕組み」を組み合わせて、ユーザーの入力を安全にSQLへ反映させる方法を論じよう。
君のコードが、現場の空気を変える武器になることを期待している。
