DoCmd.TransferSpreadsheetの罠を穿つ：データ型不一致を殲滅する「ステージング・テーブル」戦術
Access開発における「Excelインポート」は、業務自動化の要でありながら、最も脆い（Brittle）工程の一つだ。
多くの初級・中級者は、`DoCmd.TransferSpreadsheet`という手軽なメソッドを信じ、そして裏切られる。昨日まで動いていたツールが、ユーザーが入力した「たった1行の文字列混じりのデータ」によって、無慈悲な「フィールドのデータ型不一致」エラーを吐いて停止する。
なぜ、あなたのコードはこれほどまでに脆弱なのか？
伝説的なチーフアーキテクトの視点から、その根本原因と、プロフェッショナルが採用すべき「インポート定義（ステージング・テーブル）」を用いた堅牢な設計を伝授する。
—
1. 「8行の呪い」：なぜDoCmdは失敗するのか
`DoCmd.TransferSpreadsheet`が不安定な最大の理由は、Access（ACEエンジン）の「データ型推論」にある。
Accessはインポート時、Excelの先頭数行（デフォルトでは8行）をスキャンして列のデータ型を勝手に決定する。
- 最初の8行がすべて数値であれば、その列は「数値型」と見なされる。
- 9行目に「100-A」という文字列が現れた瞬間、Accessはパニックを起こし、インポートエラー、あるいはデータの消失（Null化）を引き起こす。
この挙動をレジストリ変更で回避しようとするのは三流だ。環境に依存する解決策は、配布先で必ず破綻する。我々プロフェッショナルは、「Accessに推論をさせない」設計を構築しなければならない。
—
2. 解決策：インポート定義としての「ステージング・テーブル」
テキストファイル（CSV）であれば「インポート定義（MSysIMEXSpecs）」を保存できるが、Excelの`TransferSpreadsheet`にはそれがない。
そこで、「ステージング・テーブル（一時受けテーブル）」をインポート定義の代わりとして機能させる。
戦略の骨子：
1. 物理的な「型」の固定: あらかじめ、全ての列を「短いテキスト（あるいは適切な型）」で定義した空のテーブルを用意しておく。
2. 直接取り込みの禁止: 本番テーブルに直接インポートせず、必ずこのステージング・テーブルを経由させる。
3. スキーマの強制: `TransferSpreadsheet`は、インポート先に既存テーブルを指定すると、そのテーブルの型に合わせてデータを流し込もうとする。これを利用する。
—
3. 実装：堅牢なるインポート・アーキテクチャ
以下に、実務でそのまま通用するプロダクションレベルのコードを示す。
このコードの肝は、「一時テーブルのクリーンアップ」「型定義の固定」「トランザクションを意識したデータ移行」にある。
‘——————————————————————————————
‘ @Description: Excelからデータを安全にインポートする
‘ @Param: strFilePath – インポート対象のExcelフルパス
‘ @Param: strSheetName – 対象シート名（末尾に$が必要）
‘——————————————————————————————
Public Sub ImportExcelRobustly(ByVal strFilePath As String, ByVal strSheetName As String)
On Error GoTo Err_Handler
Dim db As DAO.Database
Set db = CurrentDb
‘ — 1. ステージング・テーブル名の定義 —
‘ あらかじめデザインビューで全てのフィールドを「短いテキスト」等に設定したテーブル
Const STAGING_TABLE As String = “T_Import_Staging”
Const DEST_TABLE As String = “T_Production_Data”
‘ — 2. 前処理：ステージング・テーブルの初期化 —
‘ 以前のゴミが残っていると型不一致の元になる。
db.Execute “DELETE FROM ” & STAGING_TABLE, dbFailOnError
‘ — 3. インポート実行（DoCmdの罠を最小化） —
‘ HasFieldNames:=True により1行目をヘッダーとして扱う
‘ ここでSTAGING_TABLEのフィールド名とExcelのヘッダーが一致している必要がある
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
TableName:=STAGING_TABLE, _
FileName:=strFilePath, _
HasFieldNames:=True, _
Range:=strSheetName & “A:Z” ‘ 範囲を限定することで余計な空行読み込みを防止
‘ — 4. データクレンジングと本番移行 —
‘ ステージング（全てテキスト）から、本番（数値や日付）へ型変換しながら流し込む
‘ ここで不適切なデータ（数値列に文字がある等）をSQLの関数で除外・加工できる
Dim strSql As String
strSql = “INSERT INTO ” & DEST_TABLE & ” ( Field1, Field2, ImportDate ) ” & _
“SELECT Val(F1), CDate(F2), Now() FROM ” & STAGING_TABLE & ” ” & _
“WHERE IsNumeric(F1) = True;” ‘ 不正データはWHERE句で弾く
db.Execute strSql, dbFailOnError
MsgBox “インポート完了。正常に処理されました。”, vbInformation
Exit_Handler:
Set db = Nothing
Exit Sub
Err_Handler:
MsgBox “致命的エラー: ” & Err.Description, vbCritical, “Architecture Error”
Resume Exit_Handler
End Sub
—
4. ライフサイクルとパフォーマンスの重み
この設計が優れている理由は、単にエラーを防ぐからではない。「データのライフサイクル」を制御下に置いているからだ。
1. CurrentDbのキャッシュ管理:
`db.Execute`を使用している点に注目してほしい。`DoCmd.RunSQL`はUIを介するため遅く、確認ダイアログの制御が必要だが、`db.Execute`はエンジン直結で高速だ。また、`dbFailOnError`を指定することで、1レコードでも失敗すればロールバックする「原子性」を担保できる。
2. Range指定の重要性:
`Range:=strSheetName & “A:Z”`のように範囲を指定せよ。Excelユーザーは往々にして、データ範囲外のセルにメモを書いたり、書式を設定したりする。これがAccessには「データが存在する行」と誤認され、大量の空行インポートや型エラーを誘発する。
3. 「型」の最終防衛ライン:
本番テーブルに流し込む際の`INSERT INTO … SELECT`文こそが、データ整合性の最後の砦だ。`Val()`や`CDate()`、`Nz()`関数を駆使し、汚れたデータをクレンジングせよ。これが「業務自動化エンジニア」としての腕の見せ所である。
—
5. 結論：ツールを作るな、システムを構築せよ
`DoCmd.TransferSpreadsheet`を単体で使うのは、ただの「ツール」だ。
ステージング・テーブルを用意し、インポート、クレンジング、本番反映というパイプラインを構築して初めて、それは「システム」と呼べる堅牢さを手にする。
「ユーザーが間違ったデータを入れないように祈る」のは設計ではない。
「どんなゴミが投げ込まれても、システムを落とさず、不正なデータのみを論理的に排除する」。この思想こそが、Access VBAを掌握する者の証である。
明日からの開発では、その1行のインポートコードの背後に、鉄壁のステージング・エリアを構築してほしい。
