【実務】テーブルの全フィールドを走査して空文字をNullに置換するクリーンアップツール
レガシーなAccessデータベースの運用において、最も根が深く、開発者を絶望させる悪魔の仕様。それが「空文字（`””`）と `Null` の混在」である。
SQLの `WHERE` 句、外部システム（SQL ServerやPostgreSQL等）との連携、そして何より不条理なクエリの結合ミス。これらを引き起こす元凶を、 DAO（Data Access Objects）のメタデータ駆動アプローチによって根絶する。
今回は、現場の生き血を吸ってきたシニアエンジニアに捧ぐ、テーブル定義を動的に解析し、テキスト系フィールドの空文字を問答無用で `Null` へ昇華させる極限のメンテナンススクリプトを公開する。
—
1. なぜ「空文字とNullの混在」はシステムを崩壊させるのか
Jet/ACEエンジン、そしてその現代版であるAccessデータベースにおいて、文字列型フィールドが許容する「値なし」の状態には2種類存在する。
1. `Null`（値が存在しない）
2. `Zero-Length String (ZLS)` （長さ0の文字列 `””`）
RDBの理論的観点から言えば、属性の欠損は一貫して `Null` で表現されるべきだ。しかし、Accessのフォームは未入力状態で `””` を突っ込み、SQLの集計関数（`Count`, `Sum` 等）は `Null` を無視する一方で `””` を有効なデータとして扱う。この非対称性が、インポートデータの不整合や、アップサイジング時の致命的なバグを生む。
全テーブル・全レコードを手作業でクレンジングするなど、エンジニアのすることではない。DAOを用いたメタデータ走査と、トランザクション制御による一括処理こそが、唯一にして至高の解である。
—
2. アーキテクチャの設計思想
今回のスクリプトで担保すべき要件は以下の4点である。
- 型安全性の担保: 対象となるのは `dbText`（短文字列）と `dbMemo`（長文字列）のみ。数値型や日付型、バイナリ型への無駄な干渉を避ける。
- メモリの極限最適化: DAOの `TableDef` や `Recordset` は、明示的にオブジェクトの参照を解放（`Set … = Nothing`）しなければ、Accessの肥大化（Bloat）とメモリリークを直撃する。
- トランザクションの死守: 途中でエラーが発生した際、中途半端なデータ汚染を防ぐため、DAOの `Workspace` レベルでトランザクションを張る。
- システムテーブルの除外: `MSys` や `~` で始まる内部システムテーブルを誤って破壊しないための厳格なガード条項。
—
3. 実装コード：CleanUp_EmptyStringsToNull
以下のコードを標準モジュールに配置し、実行する。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ データベース全体、あるいは指定テーブルの全テキストフィールドを走査し、
‘ 空文字(“”)をNullに置換する高信頼クリーンアップツール
‘ =========================================================================
Public Sub ExecuteDataCleanUp()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim wsp As DAO.Workspace
Dim targetTables As Collection
Dim tblName As Variant
Dim startTime As Double
startTime = Timer
Set wsp = DBEngine.Workspaces(0)
Set dbs = CurrentDb
‘ 処理対象外（システムテーブル等）を除外したテーブルリストの構築
Set targetTables = New Collection
For Each tdf In dbs.TableDefs
‘ システムテーブル（MSys…）やリンクテーブル、一時テーブルを除外
If (tdf.Attributes & dbSystemObject) = 0 And _
(tdf.Attributes & dbAttachedTable) = 0 And _
Left$(tdf.Name, 1) <> “~” Then
targetTables.Add tdf.Name
End If
Next tdf
If targetTables.Count = 0 {
MsgBox “処理対象となるローカルテーブルが存在しません。”, vbExclamation, “中断”
GoTo Cleanup_Exit
}
‘ ユーザーへの最終確認
If MsgBox(“データベース内の全ローカルテーブル(” & targetTables.Count & “件)を対象に、” & vbCrLf & _
“「空文字(長さ0)」を「Null」に置換します。” & vbCrLf & _
“この処理には時間がかかる場合があります。実行しますか？”, _
vbYesNo + vbCritical, “極限クリーンアップの実行”) <> vbYes Then
Exit Sub
End If
‘ トランザクション開始（データベース全体の整合性を守る）
wsp.BeginTrans
On Error GoTo Trans_Error
Dim processedCount As Long
processedCount = 0
For Each tblName In targetTables
Call CleanUpTable(dbs, CStr(tblName))
processedCount = processedCount + 1
Next tblName
‘ コミット
wsp.CommitTrans
MsgBox “クリーンアップが正常に完了しました。” & vbCrLf & _
“処理テーブル数: ” & processedCount & ” 件” & vbCrLf & _
“実行時間: ” & Format$(Timer – startTime, “0.00秒”), vbInformation, “完了”
GoTo Cleanup_Exit
Trans_Error:
‘ ロールバックによる完全な原状復帰
wsp.Rollback
MsgBox “エラーが発生したため、変更をすべて破棄しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description, vbCritical, “トランザクション異常終了”
Cleanup_Exit:
‘ オブジェクトの明示的解放（メモリリークの完全防止）
Set dbs = Nothing
Set wsp = Nothing
Set targetTables = Nothing
End Sub
‘ =========================================================================
‘ 個別テーブルのフィールド定義解析とレコード更新処理
‘ =========================================================================
Private Sub CleanUpTable(ByRef dbs As DAO.Database, ByVal tableName As String)
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim textFields As Collection
Dim fldName As Variant
Dim isModified As Boolean
Set tdf = dbs.TableDefs(tableName)
Set textFields = New Collection
‘ 1. テーブル定義から対象となる文字列型フィールドを特定
For Each fld In tdf.Fields
‘ dbText (文字列) または dbMemo (長文字列) のみ対象
‘ ※ 外部キーや主キーであっても、空文字が紛れ込んでいる可能性を考慮して除外はしない
If fld.Type = dbText Or fld.Type = dbMemo {
‘ 読み取り専用フィールドや自動採番（AppendOnly等）はスキップ
If (fld.Attributes & dbUpdatableField) <> 0 Then
textFields.Add fld.Name
End If
End If
Next fld
‘ 対象フィールドがなければ終了
If textFields.Count = 0 Then GoTo Table_Cleanup_Exit
‘ 2. テーブルを開く（ダイナセット、更新可能）
Set rst = dbs.OpenRecordset(“SELECT FROM [” & tableName & “]”, dbOpenDynaset, dbDenyWrite)
‘ レコードが存在しない場合はスキップ
If rst.EOF And rst.BOF Then GoTo Table_Cleanup_Exit
Do While Not rst.EOF
isModified = False
‘ トランザクション内での編集
rst.Edit
For Each fldName In textFields
‘ フィールドの値がNullではなく、かつ空文字である場合のみNullを代入
If Not IsNull(rst.Fields(CStr(fldName)).Value) Then
If rst.Fields(CStr(fldName)).Value = “” Then
rst.Fields(CStr(fldName)).Value = Null
isModified = True
End If
End If
Next fldName
If isModified Then
rst.Update
else
‘ 変更がない場合の無駄な書き込みロックをキャンセル
rst.CancelUpdate
End If
rst.MoveNext
Loop
Table_Cleanup_Exit:
‘ オブジェクトの厳格な解放
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
Set textFields = Nothing
Set tdf = Nothing
End Sub
—
4. チーフアーキテクトが解説するコードの急所
① `dbDenyWrite` による排他制御の回避と並行性
`OpenRecordset` の引数に `dbDenyWrite`（または必要に応じたロックモード）を適切に選択することで、マルチユーザー環境における無用な競合を防ぎつつ、バッチ処理中のパフォーマンスを最大化している。また、変更が発生しなかったレコードに対しては `rst.CancelUpdate` を明示的に呼び出すことで、Jetエンジンのダーティリード・ライトのオーバーヘッドを極限まで削ぎ落としている。
② `dbSystemObject` と `dbAttachedTable` のフィルタリング
実務環境において、Accessのシステムテーブルや、SQL Server等へのリンクテーブルに対して `Edit/Update` をループさせると、通信エラーやシステム破壊を引き起こす。メタデータの `Attributes` プロパティをビット演算 (`And`) で評価し、純粋なローカル・ベーステーブルのみを安全に抽出している点に注目してほしい。
③ 徹底的なオブジェクト参照の断ち切り（Memory Management）
VBAのガベージコレクションは頼りにならない。特に DAO の `Recordset` や `TableDef` をループ内で循環参照させたまま放置すると、VBAランタイムのメモリ空間が断片化し、最終的に「メモリ不足 (Out of memory)」エラーでプロセスがクラッシュする。
サブルーチンを細分化し、スコープを抜ける瞬間に必ず `Set … = Nothing` を実行する構造を徹底しているのはそのためだ。
—
5. まとめ：データ品質はインフラストラクチャである
「コードを書くだけ」のプログラマは、データの裏側にある物理構造の歪みを見落とす。しかし、真に堅牢なシステムを構築するアーキテクトは、データが格納される器の型定義から、メモリのライフサイクルまでを完全に支配する。
今回のクリーンアップツールを定期的（あるいはデータ移行のイニシャル・フェーズ）に組み込むことで、システム全体の信頼性は劇的に向上する。レガシーシステムの足枷となっている「見えないゴミ」を、このコードで一網打尽にしていただきたい。
