【テクニカル・上級編】【中級】DoCmd.TransferSpreadsheetの罠：インポート時のデータ型不一致を回避する「インポート定義」の活用 – Access VBA解析バイブル

DoCmd.TransferSpreadsheet の罠を穿つ：インポート定義と「型不一致」への終止符

Access VBAを長年扱ってきた者にとって、`DoCmd.TransferSpreadsheet` は「諸刃の剣」である。

Excelという、型定義が曖昧なサンドボックスから、リレーショナルデータベースという厳格な秩序の世界へデータを流し込む。この際、Accessが暗黙的に行う「先頭数行のサンプリングによる型推論」が、どれほど多くの現場で深夜の障害対応を招いてきたか。数値列に突如現れる「欠番」という文字列、日付列に混入する空文字。これらが引き起こす「フィールドの型不一致」エラーは、場当たり的な修正では決して根絶できない。

本稿では、この「推論」という不確定要素を排除し、インポートプロセスを完全に制御下に置くための、チーフアーキテクト級の設計指針を提示する。

—

1. 「暗黙の型推論」という技術的負債

Accessのインポートエンジンは、レジストリ（`TypeGuessRows`）に依存し、データの先頭8行（デフォルト）を見てフィールド型を決定する。しかし、現実の運用データは2,000行目に予期せぬ文字列が含まれているものだ。

`TransferSpreadsheet` メソッドには、テキストインポートにおける `TransferText` のような「インポート定義名」を指定する引数が存在しない。これが、Excelインポートが「不安定」と揶揄される最大の理由である。

シニアエンジニアが取るべき戦略は、以下の3手に集約される。

1. Staging Table（中継テーブル）戦略: すべての列を `Short Text` で受け入れ、SQLでキャストする。
2. Schema.ini の動的生成: Excelを一旦CSVとして扱い、定義ファイルを強制する。
3. MSysIMEX 隠しシステムの掌握: インポートスペックをシステムテーブルレベルで制御する。

今回は、最も堅牢かつ保守性の高い 「Staging Table ＋ 動的クエリ」 の手法に、Windows APIによるファイル制御を組み合わせた極限の解法を詳説する。

—

2. プロフェッショナル仕様のインポート・アーキテクチャ

単にインポートするのではない。OSのファイルハンドルを適切に扱い、メモリリークを抑え、トランザクションを保護する。これが「掌握」するということだ。

実装コード：堅牢なるインポートエンジン

以下のコードは、Excelを直接叩くのではなく、Accessのシステムバッファを最適化し、DAO（Data Access Objects）のライフサイクルを厳密に管理した例である。

Option Compare Database
Option Explicit

‘ Windows API: 一時ファイルパスの取得（レガシー環境でも安定動作を保証）
Private Declare PtrSafe Function GetTempPath Lib “kernel32” Alias “GetTempPathA” ( _
ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long

”’

Public Sub RobustExcelImport(ByVal sourcePath As String, ByVal targetTable As String)
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim strStagingTable As String: strStagingTable = “tmp_Import_Staging”

‘ CurrentDbは呼び出しのたびに新しいインスタンスを生成するため、
‘ 変数に保持してオブジェクトのライフサイクルを管理する。
Set db = CurrentDb

‘ 1. 前処理：既存の中継テーブルを削除
On Error Resume Next
db.Execute “DROP TABLE ” & strStagingTable, dbFailOnError
On Error GoTo Err_Handler

‘ 2. インポート実行
‘ ここでの罠：Excelの1行目がデータ型を決定してしまうのを防ぐため、
‘ IMEX=1 (Import Mode) を意識したいが、TransferSpreadsheetでは限界がある。
‘ そのため、あえて「リンクテーブル」を経由するか、全列テキストのテーブルを事前に用意する。

‘ ここでは「全列テキスト」の定義を持つ空テーブルを事前に作成しておく手法を推奨する。
‘ ※ 実行時にSELECT INTOで作成すると型推論に依存するため、DDLで定義する。
CreateStagingTable db, strStagingTable

‘ 3. TransferSpreadsheetによる流し込み
‘ Range引数を活用し、データの範囲を限定することでオーバーヘッドを削減する。
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
strStagingTable, sourcePath, True

‘ 4. 本番テーブルへのアトミックな移行（トランザクション処理）
‘ データ整合性を担保するため、Workspaceレベルでのトランザクションを推奨。
DBEngine.BeginTrans

‘ 型変換を伴う追加クエリ（例：CDate, CLng, CCur等で明示的にキャスト）
‘ これにより、不正なデータが含まれていた場合にここでトラップできる。
Dim strSql As String
strSql = “INSERT INTO ” & targetTable & ” ( フィールド1, フィールド2 ) ” & _
“SELECT CStr([F1]), CVDate([F2]) FROM ” & strStagingTable

db.Execute strSql, dbFailOnError

DBEngine.CommitTrans
Debug.Print “Import Completed Successfully.”

Exit_Handler:
‘ オブジェクトの明示的解放（メモリリークの防止）
‘ Accessはこれを怠ると、LDBファイルが残り続け、破損の原因となる。
If Not db Is Nothing Then
db.Close
Set db = Nothing
End If
Exit Sub

Err_Handler:
If DBEngine.Workspaces(0).IsolateIME Then ‘ トランザクション中か判定（擬似）
‘ 実際にはエラーフラグ等で判定
End If
DBEngine.Rollback
MsgBox “Critical Error: ” & Err.Description, vbCritical, “System Architect”
Resume Exit_Handler
End Sub

Private Sub CreateStagingTable(db As DAO.Database, tableName As String)
‘ 全ての列をVARCHAR（Short Text）で定義することで、インポート時の型エラーを物理的に回避する。
Dim ddl As String
ddl = “CREATE TABLE ” & tableName & ” (” & _
“F1 TEXT(255), ” & _
“F2 TEXT(255), ” & _
“F3 TEXT(255));”
db.Execute ddl
End Sub

—

3. 深淵なる知見：なぜ `CurrentDb` ではなく `DBEngine(0)(0)` か

多くのリファレンスでは `CurrentDb` が使われるが、パフォーマンスを極限まで追求するシニアエンジニアは、その挙動の違いを理解している。

`CurrentDb` は呼び出されるたびにデータベースのコピー（コレクションのリフレッシュ）を作成する。ループ内で `CurrentDb.Execute` を繰り返すのは、自殺行為に近い。一方、`DBEngine(0)(0)` は開いているデータベースの直接的なポインタを指すが、こちらはリフレッシュされないため、テーブル定義の変更直後などに最新の状態を反映していないリスクがある。

本稿のコードで `Set db = CurrentDb` としているのは、「一度だけリフレッシュされた最新のインスタンスを変数に固定し、スコープ内で使い回す」 という、安全と速度を両立させた最適解である。

—

4. レガシー保守とモダン化への視点

Excelインポートのトラブルは、多くの場合「ExcelをDBとして扱おうとする傲慢さ」から生じる。Access VBAにおけるインポートの本質は、「不浄な外部データを、いかにして清浄なリレーショナルモデルへ変換するか」 というクレンジングプロセスに他ならない。

インポート定義を動的に切り替えたい場合、`DoCmd.TransferText` であれば `MSysIMEXSpecs` テーブルを直接SQLで書き換えるという禁忌に近い手法も存在するが、Excelにおいては上記のような 「Staging Table戦略」 が最も保守性が高い。

結論

`DoCmd.TransferSpreadsheet` のエラーに翻弄されているうちは、まだAccessに「使われている」段階だ。
インポート定義という概念を、単なるGUIの設定値ではなく、「DDLによるスキーマ固定と、SQLによる明示的キャスト」 へと昇華させること。それが、堅牢なシステムを構築するチーフアーキテクトの思考法である。

このアプローチを採用することで、データ型不一致によるランタイムエラーは、もはや過去の遺物となるだろう。