【中級〜上級】DoCmd.TransferSpreadsheetのインポートエラーテーブルを監視する自動例外処理の極意
Accessによる基幹システム運用において、外部からのExcelファイル取り込み（`DoCmd.TransferSpreadsheet`）は避けて通れない処理の一つだ。しかし、現場のユーザーが作成するExcelデータは、スキーマの不一致、型エラー、桁あふれ、そしてNULL制約違反の地雷原である。
定型処理の中でエラーが発生した際、Accessは親切心から「エラー テーブル」を自動生成する。だが、この自動生成されるテーブル名がクセ者だ。「ImportErrors」「テーブル名$」など、システム側で動的に命名されるこの一時テーブルを放置すれば、次回のインポート時に一意性制約の衝突やゴミデータの蓄積を招き、最悪の場合はデータベースの肥大化・破損を引き起こす。
本稿では、この自動生成されるエラーテーブルをVBAでリアルタイムに検知・監視し、失敗したレコードのトランザクションログを抽出・保全した上で、環境をクリーンに保つ極限の例外処理アーキテクチャを解説する。
—
1. Accessインポートエラーのメカニズムとアーキテクチャ上の罠
`DoCmd.TransferSpreadsheet`を実行した際、JET/ACEデータベースエンジンは、データの型変換や制約違反に遭遇すると、処理を中断せずに「スキップ可能なエラー」として処理を継続し、その詳細をローカルデータベース内に一時的なインポート エラー テーブルとして実体化させる。
ここでシニアエンジニアが直面する技術的課題は以下の3点である：
1. 動的なテーブル名の特定: ターゲットテーブル名に依存してエラーテーブル名が変化するため、ハードコーディングが不弁。
2. オブジェクトのライフサイクル管理: DAO/ADOを介した動的クエリの実行時に、`Recordset`や`QueryDef`の解放漏れによるメモリリーク。
3. トランザクションとエラーハンドリングの分離: VBAの `On Error` と、ACEエンジン内部のエラーハンドリングの非同期性。
これらを完全に制御下におくための実践的なコードベースを以下に提示する。
—
2. 実装：堅牢なエラー監視・ログ抽出自動化モジュール
以下のコードは、指定したExcelファイルのインポートを実行し、エラーテーブルの生成を監視。エラーレコードを独自のログテーブルへ退避させたのち、自動生成されたゴミテーブルを完全に消去するプロシージャである。
Option Compare Database
Option Explicit
‘ ==============================================================================
‘ 模範的インポート例外処理エンジン
‘
‘ 概要: Excelインポート時のエラーテーブルを動的に検知し、ログへ昇華・クリーンアップする
‘ 著者: チーフアーキテクト
‘ ==============================================================================
Public Sub ExecuteResilientImport(ByVal strExcelPath As String, ByVal strTargetTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strErrorTableName As String
Dim rsErr As DAO.Recordset
Dim rsLog As DAO.Recordset
Dim lngErrorCount As Long
On Error GoTo ErrorHandler
Set db = CurrentDb()
‘ 1. インポート実行（あらかじめ対象テーブルの構造とExcelの型が一致している前提）
‘ ※注意: 既存のエラーテーブルの残骸を事前にクリアしておく
Call Prv_CleanupStaleErrorTables(db, strTargetTable)
‘ 厳密なインポート処理の呼び出し
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, strTargetTable, strExcelPath, True
‘ 2. エラーテーブルの動的検知
‘ Accessはエラー発生時、[対象テーブル名$_ImportErrors] などの名前でテーブルを生成する
strErrorTableName = Prv_DetectErrorTable(db, strTargetTable)
If strErrorTableName <> “” Then
‘ エラーテーブルが存在する場合の例外フロー
MsgBox “インポート処理でデータ不整合が検出されました。” & vbCrLf & _
“エラー内容をシステムログに退避します。”, vbExclamation, “インポート警告”
Set rsErr = db.OpenRecordset(“SELECT FROM [” & strErrorTableName & “]”, dbOpenSnapshot)
Set rsLog = db.OpenRecordset(“T_System_Import_ErrorLog”, dbOpenTable)
lngErrorCount = 0
Do While Not rsErr.EOF
‘ 独自のログテーブル（T_System_Import_ErrorLog）へ構造化データを退避
rsLog.AddNew
rsLog!ErrorDate = Now()
rsLog!TargetTable = strTargetTable
rsLog!SourceFile = strExcelPath
rsLog!ErrorField = Nz(rsErr!Field, “Unknown”)
rsLog!ErrorRow = Nz(rsErr!Row, 0)
rsLog!ErrorMessage = Nz(rsErr!Message, “理由不明のエラー”)
rsLog.Update
lngErrorCount = lngErrorCount + 1
rsErr.MoveNext
Loop
‘ 3. 動的エラーテーブルの物理削除（データベース肥大化防止）
db.TableDefs.Delete strErrorTableName
MsgBox lngErrorCount & ” 件のレコードがインポートに失敗し、ログへ退避しました。”, vbCritical, “例外処理完了”
Else
MsgBox “インポートはエラーなく正常終了しました。”, vbInformation, “完了”
End If
CleanUp:
‘ オブジェクトの明示的解放（メモリリークの完全阻止）
If Not rsErr Is Nothing Then rsErr.Close: Set rsErr = Nothing
If Not rsLog Is Nothing Then rsLog.Close: Set rsLog = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
MsgBox “致命的なエラーが発生しました: ” & Err.Description, vbCritical, “システムエラー”
Resume CleanUp
End Sub
‘ — ヘルパー関数: エラーテーブルの動的検索 —
Private Function Prv_DetectErrorTable(ByRef db As DAO.Database, ByVal strBaseTable As String) As String
Dim tdf As DAO.TableDef
Dim strCandidate As String
‘ Accessの仕様上、エラーテーブルの命名規則はベーステーブル名を含む
For Each tdf In db.TableDefs
If InStr(1, tdf.Name, strBaseTable, vbTextCompare) > 0 And _
(InStr(1, tdf.Name, “_ImportErrors”, vbTextCompare) > 0 Or _
InStr(1, tdf.Name, “ImportErrors”, vbTextCompare) > 0) Then
Prv_DetectErrorTable = tdf.Name
Exit Function
End If
Next tdf
Prv_DetectErrorTable = “”
End Function
‘ — ヘルパー関数: 事前ゴミ掃除 —
Private Sub Prv_CleanupStaleErrorTables(ByRef db As DAO.Database, ByVal strBaseTable As String)
Dim tdf As DAO.TableDef
Dim i As Long
‘ コレクションの逆順ループによる安全な削除
For i = db.TableDefs.Count – 1 To 0 Step -1
Set tdf = db.TableDefs(i)
If InStr(1, tdf.Name, strBaseTable, vbTextCompare) > 0 And _
(InStr(1, tdf.Name, “_ImportErrors”, vbTextCompare) > 0 Or _
InStr(1, tdf.Name, “ImportErrors”, vbTextCompare) > 0) Then
db.TableDefs.Delete tdf.Name
End If
Next i
End Sub
—
3. チーフアーキテクトが解説するコードの急所
オブジェクトライフサイクルとメモリマネジメント
VBAのガベージコレクションは頼りにならない。`DAO.Recordset` や `Database` オブジェクトは、スコープを抜けただけでは即座にメモリから解放されないことが多々ある。特に長時間のバッチ処理やループ内では、`Set rs = Nothing` を明示的に行わないと、ACEエンジンのロック競合やメモリリーク（Heap Fragmentation）を引き起こす。上記のコードでは `CleanUp` ラベルを設け、いかなる例外パスを通ろうとも確実にオブジェクトを破壊する構造にしている。
テーブル定義（TableDefs）コレクションの逆順ループ削除
VBAでコレクション（`TableDefs` や `Fields` など）の要素を動的に削除する際、先頭からの順ループ（`For i = 0 To Count – 1`）を回すと、インデックスがずれて致命的な実行時エラーを引き起こす。必ず末尾から先頭へ向かう逆順ループ（Step -1）を使用すること。これはレガシー環境における鉄則中の鉄則である。
なぜインポートエラーを「ログテーブル」に昇華させるべきか
自動生成されるエラーテーブルをそのまま放置・ユーザーに閲覧させる運用はシステム破綻への第一歩である。エラーテーブルは構造が固定されておらず、Accessのバージョンやインポート方式によってカラム名が変わるリスクがある。
一度VBAで強固に型付けされたログテーブル（`T_System_Import_ErrorLog`）へパースして格納することで、後続のBIツールやダッシュボード、あるいは管理者への通知バッチからのクエリが安定する。
—
4. 総括
Access VBAにおけるシステム連携は、「動けばいい」という妥協が数ヶ月後のデータベース破損やパフォーマンス劣化という形で必ずツケを回す。
今回紹介した、「実行前の環境サニタイジング ➔ 安全な実行 ➔ 動的メタデータ検知 ➔ 構造化ログへの退避 ➔ 物理リソースの完全解放」という一連のライフサイクル制御を組み込むことで、現場の野良Excelデータに怯えない、極めて堅牢なエンタープライズ・アクセスの構築が可能となる。
プロフェッショナルたる者、コードの端々にまで意図を持たせよ。アーキテクチャの美しさは、例外処理の緻密さに宿る。
