【実務・中級編】【中級】DoCmd.TransferSpreadsheetのインポートエラーテーブルを監視する自動例外処理 – Access VBA解析バイブル

アクセス開発の現場における「Excelインポート地獄」と向き合う

こんにちは。業務システムのアーキテクトとして数々の修羅場をくぐってきた私だが、現場から最も頻繁に聞こえてくる悲鳴の一つがこれだ。

「定例のExcelインポートが、理由も分からないまま一部のデータを取りこぼして完了してしまう」

`DoCmd.TransferSpreadsheet` は非常に手軽で強力なメソッドだ。一行のコードで数万行のExcelデータをAccessのテーブルへ流し込める。しかし、実務で使われるExcelファイルが「美しくクリーンなデータ」である奇跡など滅多にない。型違い、桁あふれ、あるいは突然挿入された文字列混じりのセル。

Accessは優秀（あるいはお節介）なので、インポート時に型エラーや制約違反が発生すると、勝手にエラーのレコードを切り捨て、何食わぬ顔で処理を完了させ、テーブルの名前の末尾に「_ImportErrors」という不気味な残骸を生成する。

このエラーテーブルを放置し、現場の担当者が「インポート終わりました！」と笑顔で報告し、後日重大なデータ欠損が発覚する――。この悪夢のようなアンチパターンを、私のプロジェクトでは絶対に許容しない。

今回は、`TransferSpreadsheet` の挙動の裏側を完全に掌握し、エラーテーブルの生成を検知した瞬間に「どの行の、どの列で、何が起きたのか」を自動でログ化・通知する、プロダクションクオリティの堅牢な自動例外処理アーキテクチャを伝授しよう。

—

なぜ雑なエラーハンドリングでは現場で通用しないのか？

素人が書くコードはこうだ。

‘ 【悪手】エラーを握りつぶす、または結果を確認しない最悪のコード
On Error Resume Next
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, “T_Target”, “C:\Data\input.xlsx”, True
On Error GoTo 0
‘ インポートが終わったからヨシ！とする無責任な設計

これの何が問題か？
1. サイレント・フェイル（沈黙の失敗）： VBAの実行時エラーとしては捕捉されない。VBA自体は「正常終了」扱いになるため、エラーログすら残らない。
2. 証拠隠滅： 万が一「_ImportErrors」テーブルができていたとしても、同名のインポート処理を再実行した瞬間、そのエラーテーブルは上書き消滅する。
3. 監査証票の欠如： 「どのデータの何行目がなぜ弾かれたのか」を業務担当者が追跡できず、データ整合性の担保が法的に求められる現代の業務において致命傷となる。

プロのエンジニアであれば、「インポート処理の直後に、同名の例外テーブルの有無をプログラム側で能動的にスキャンし、存在すれば即座に中身を解析して安全な監査ログテーブルへ退避させ、例外テーブルを速やかにクリーンアップする」というライフサイクル管理を実装しなければならない。

—

堅牢なインポート例外監視アーキテクチャの全体像

今回構築するモジュールのロジックはこうだ。

1. 事前クリーンアップ： 過去に生成された不要なエラーテーブル (`[対象テーブル名]_ImportErrors`) が残っていれば事前に削除する。
2. インポート実行： `TransferSpreadsheet` を実行する。
3. 事後スキャン： `CurrentDb.TableDefs` を走査し、エラーテーブルが生成されたか判定する。
4. ログ抽出と退避： エラーテーブルが存在する場合、そのレコードをごっそり取得し、専用のシステムログテーブル (`T_System_ImportLog`) に「いつ、どのテーブルで、どんなエラーが起きたか」のメタデータと共に書き込む。
5. 例外テーブルの破棄： 次回実行時のコンフリクトを防ぐため、エラーテーブルを即座にドロップする。

—

プロダクションコード実装

以下のコードは、エラーハンドリング、トランザクション、オブジェクトの解放（メモリリーク対策）まで考慮した、そのまま本番環境に投入できるクラスレスの標準モジュール実装だ。

Option Compare Database
Option Explicit

‘ ==============================================================================
‘ 概要: Excelファイル的安全インポート＆エラー監視・ログ自動退避プロシージャ
‘ 備考: DoCmd.TransferSpreadsheetの隠れたエラーテーブルを検知し、永続ログ化する
‘ ==============================================================================
Public Sub SafeImportWithExceptionLog(ByVal targetTableName As String, ByVal excelFilePath As String)

Dim db As DAO.Database
Dim errorTableName As String
Dim logTableName As String
Dim rsErr As DAO.Recordset
Dim rsLog As DAO.Recordset
Dim errCount As Long
Dim isErrorTableExists As Boolean

Set db = CurrentDb()
errorTableName = targetTableName & “_ImportErrors”
logTableName = “T_System_ImportLog” ‘ 事前に作成しておくべき永続ログテーブル

On Error GoTo ErrorHandler

‘ ————————————————————————–
‘ 1. 事前準備: 過去の遺物であるエラーテーブルが残存していれば削除
‘ ————————————————————————–
Call DropTableIfExists(db, errorTableName)

‘ ————————————————————————–
‘ 2. インポート実行 (エラーが発生してもVBAは止まらない仕様に対応するため監視)
‘ ————————————————————————–
‘ ※型不一致やデータ切り捨てが発生すると、Accessは内部で勝手に [テーブル名]_ImportErrors を作る
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, targetTableName, excelFilePath, True

‘ ————————————————————————–
‘ 3. エラーテーブルの生成有無をスキャン
‘ ————————————————————————–
isErrorTableExists = CheckTableExists(db, errorTableName)

If isErrorTableExists Then
‘ エラーテーブルが存在する場合の例外処理フロー
Set rsErr = db.OpenRecordset(“SELECT FROM [” & errorTableName & “]”, dbOpenSnapshot)
Set rsLog = db.OpenRecordset(“SELECT FROM [” & logTableName & “] WHERE 1=0”, dbOpenDynaset) ‘ 構造のみ取得

errCount = 0

‘ トランザクションを開始してログ書き込みの原子性を担保
db.BeginTrans

Do Until rsErr.EOF
rsLog.AddNew
‘ メタデータとエラー内容を結合してログに退避
rsLog!LogTimestamp = Now()
rsLog!TargetTable = targetTableName
rsLog!SourceFile = excelFilePath

‘ Accessのエラーテーブルには通常 ‘TransferError’ や ‘Field’ などの固有列があるが
‘ 汎用的に扱うため、存在するフィールドを安全にマッピングする（例として主要列を想定）
If HasField(rsErr, “ErrorDescription”) Then rsLog!ErrorMessage = rsErr!ErrorDescription
If HasField(rsErr, “Field”) Then rsLog!ErrorField = rsErr!Field
If HasField(rsErr, “Row”) Then rsLog!ErrorRow = rsErr!Row

rsLog.Update
errCount = errCount + 1
rsErr.MoveNext
Loop

db.CommitTrans

‘ 検出レポートをイミディエイトウィンドウおよび必要に応じてメッセージ出力
Debug.Print “【警告】インポート中に ” & errCount & ” 件のエラーレコードが除外されました。”
MsgBox “インポートは完了しましたが、” & errCount & ” 件のデータ不整合（エラー）が検出され除外されました。” & vbCrLf & _
“詳細はログテーブル [” & logTableName & “] を確認してください。”, vbExclamation, “インポート例外警告”

‘ 役目を終えたエラーテーブルを削除
Call DropTableIfExists(db, errorTableName)

Else
Debug.Print “【正常】エラーなくインポートが完了しました。”
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:
If db.Transactions > 0 Then db.Rollback
MsgBox “予期せぬシステムエラーが発生しました: ” & Err.Description, vbCritical, “致命的エラー”
Resume CleanUp
End Sub

‘ ==============================================================================
‘ 補助関数群（テーブルの存在確認、存在時削除、フィールド有無判定）
‘ ==============================================================================
Private Function CheckTableExists(db As DAO.Database, tableName As String) As Boolean
Dim tdf As DAO.TableDef
CheckTableExists = False
For Each tdf In db.TableDefs
If tdf.Name = tableName Then
CheckTableExists = True
Exit For
End If
Next tdf
End Function

Private Sub DropTableIfExists(db As DAO.Database, tableName As String)
If CheckTableExists(db, tableName) Then
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, tableName
DoCmd.SetWarnings True
End If
End Sub

Private Function HasField(rs As DAO.Recordset, fieldName As String) As Boolean
On Error Resume Next
Dim fld As DAO.Field
Set fld = rs.Fields(fieldName)
HasField = (Err.Number = 0)
On Error GoTo 0
End Function

—

アーキテクトからの実践的アドバイス

1. 永続ログテーブル (`T_System_ImportLog`) の設計
コード内で使用している `T_System_ImportLog` は、あらかじめAccess内に設計しておく必要がある。最低限、以下のフィールドを持たせてほしい。

• `LogID` (オートナンバー / 主キー)
• `LogTimestamp` (日付/時刻)
• `TargetTable` (短テキスト)
• `SourceFile` (短テキスト)
• `ErrorMessage` (長テキスト)
• `ErrorField` (短テキスト)
• `ErrorRow` (長整数型)

2. Excel側のデータ型揺れ対策
`TransferSpreadsheet` は、Excelの先頭数行（デフォルトでは8行）のデータ型をスキャンしてAccess側の型を推測する。これが「日付カラムに文字が混ざっている」「数値列に空欄がある」といった理由で誤判定を起こし、エラーテーブル生成の引き金になる。
根本的な対策として、インポート元のExcelは「全列テキスト」等の厳格なフォーマットに統一させるか、あらかじめ一時的なテキスト型インポート用テーブルに全量流し込んでからSQL（INSERT INTO … SELECT）で型変換とバリデーションを行うステージング構成をとるのが、プロフェッショナルな開発の定石だ。

結びにかえて

「動けばいい」という妥協の産物は、必ず運用フェーズでシステムの信頼性を食いつぶす。
今回紹介した例外監視フローを組み込むことで、データの品質担保はもちろんのこと、システムが「どこでつまずいたか」を自ら語るようになる。

あなたの開発するAccessアプリケーションが、現場から絶賛される堅牢なシステムへと昇華することを期待している。