DoCmd.TransferSpreadsheetの「闇」を解く：型不一致を回避するエンジニアの矜持
Accessを「単なるデスクトップデータベース」と侮る者は、その真のポテンシャルと、背後に潜む地獄を見誤っている。
特に、現場で最も頻発する「`DoCmd.TransferSpreadsheet` によるインポートエラー」。Excelの1行目に混入した未知の文字列一つで、構築した数万行のインポート処理は脆くも崩れ去る。なぜなら、Accessのインポートエンジンは、最初の数行をスキャンしてデータ型を推論するという、極めて安直かつ危険なアルゴリズムで動いているからだ。
今日は、この「推論」という脆弱性に終止符を打ち、堅牢なデータパイプラインを構築するための極限の知見を授ける。
—
1. 悲劇の根源：Accessの「自動推論」を殺す
Accessがインポート時に型を決定する際、列内の「最初の8行」をサンプリングする。もし8行目までに数値以外の文字列が混入していれば、Accessはその列を「テキスト型」と断定する。逆に、数値が並んでいれば「数値型」とみなす。
この仕様に依存したシステムは、ある日突然、経理部が送りつけた「数値列にメモが混入したExcel」によって、`3011`や`3274`のエラーコードと共に沈黙する。これを防ぐ唯一の解は、「インポート定義（Specification）」の強制適用である。
2. インポート定義のコード化とXML管理
手動でインポートウィザードを叩いて定義を作るなど、プロの所業ではない。インポート定義を `MSysIMEXSpecs` および `MSysIMEXColumns` テーブルに直接書き込むか、あるいは定義をエクスポートしたXMLを読み込む戦術を採るべきだ。
‘ インポート定義を指定して実行する極限のパターン
Public Sub ImportExcelRobust(strPath As String, strTable As String, strSpecName As String)
On Error GoTo Err_Handler
‘ 外部定義(Spec)を使用することで、Accessの型推論を完全に無効化する
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
strTable, strPath, True, , strSpecName
Exit Sub
Err_Handler:
‘ ここでエラーログをWindowsイベントログ、あるいは専用テーブルへ排出し、
‘ システムの生存を維持する
Debug.Print “Error ” & Err.Number & “: ” & Err.Description
End Sub
3. 事前バリデーションの「防御的アーキテクチャ」
それでもなお、外部から送られてくるExcelは予測不能だ。`TransferSpreadsheet` を呼ぶ前に、ADOでスキーマをスキャンし、型不一致を事前に検知する「門番（Gatekeeper）」を実装せよ。
‘ ADOを使ってExcelをデータベースとして接続し、型を検証する
Public Function ValidateExcelSchema(strPath As String) As Boolean
Dim conn As Object
Dim rs As Object
Set conn = CreateObject(“ADODB.Connection”)
‘ Microsoft.ACE.OLEDB.12.0を使用
conn.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & _
strPath & “;Extended Properties=””Excel 12.0 Xml;HDR=YES;IMEX=1;”””
conn.Open
‘ スキーマ情報を取得し、想定外の型が含まれていないかチェック
Set rs = conn.OpenSchema(20) ‘ 20 = adSchemaColumns
Do Until rs.EOF
‘ ここで列名と型を照合し、期待するデータ型と一致するかを確認
‘ 不一致があれば、即座に例外をスローしてプロセスを停止させる
If rs(“COLUMN_NAME”).Value = “ID” And rs(“DATA_TYPE”).Value <> 3 Then ‘ 3 = adInteger
ValidateExcelSchema = False
GoTo Cleanup
End If
rs.MoveNext
Loop
ValidateExcelSchema = True
Cleanup:
rs.Close: conn.Close
Set rs = Nothing: Set conn = Nothing
End Function
—
4. パフォーマンスとメモリ管理の極意
Access VBAにおいて、`Set Object = Nothing` を怠る者は、メモリリークという時限爆弾を抱えて寝ているに等しい。特に、`DAO.Database` や `ADO.Recordset` を多用するインポート処理では、スコープを最小化し、明示的な解放を徹底すること。
- オブジェクトの明示的解放: `Nothing`代入は儀式ではない。COM参照カウントを確実にデクリメントし、ガベージコレクションを促す必須のオペレーションだ。
- DoCmdのラップ: 大規模インポート時は、`Application.Echo False` で画面描画を停止し、再計算をオフにする。これだけで、数万件の処理速度は劇的に向上する。
5. 最後に：伝説のエンジニアとして
Accessは、レガシーな技術ではない。「データの整合性を担保する」というデータベースの本質を、最も泥臭く、かつ最も速く実現できる開発環境だ。
インポートエラーに遭遇したとき、Accessのせいにしてはいけない。それはあなたのコードが「未知のデータ」に対して無防備だったという証明に過ぎない。インポート定義を制御し、事前検証で門を閉ざす。この徹底した防御的プログラミングこそが、システムを「壊れないインフラ」へと昇華させる唯一の道である。
さあ、コードを書き換えろ。あなたの作ったシステムが、次の10年も安定して稼働するために。
