Access VBAを掌握する極限の知見：不要なインデックスを駆逐し、書き込み速度を極限まで引き上げるクリーンアップ自動化
開発現場でよく耳にする悲鳴がある。
「Accessのバックエンド（または単一ファイル）のデータ追加・更新処理が、日を追うごとに遅くなっている」
原因の大半は、無秩序に乱立した「不要なインデックス（Indexes）」だ。
我々は業務システムの要件定義を進める中で、画面の利便性やクエリの速度向上ばかりに気を取られ、次々とインデックスを追加しがちである。しかし、Jet/ACEデータベースエンジンにおいて、インデックスは「諸刃の剣」だ。読み取り速度がわずかに向上する引き換えに、レコードの書き込み（INSERT/UPDATE/DELETE）のたびにインデックスツリーの再構築コストが重くのしかかる。
特に、一括処理（トランザクション内での大量インサートなど）を行うシステムでは、不要なインデックスが1つあるだけでパフォーマンスが数倍〜数十倍も悪化する。
今回は、DAO（Data Access Object）の `Indexes` コレクションを完全に掌握し、システムに悪影響を及ぼしている不要なインデックスを自動検出・安全に削除する、プロダクション品質のクリーンアップツールを解説する。
—
なぜ「あのインデックス」は不要なのか？（設計思想の罠）
プログラミング初心者は「検索しそうな列にはとりあえずインデックスを貼る」という悪癖を持ちがちだ。しかし、実務のデータベース設計においては、以下のインデックスは「悪」でしかない。
1. 主キー（Primary Key）と重複する単体インデックス
Accessは主キーに対して自動的に一意インデックスを生成する。そのため、主キー列に個別のインデックスを明示的に作成することは、完全にリソースの無駄（ストレージの圧迫と書き込みペナルティの重複）である。
2. 多重定義された冗長なインデックス
複合インデックス `(A, B, C)` が存在する場合、先頭列を含む `(A)` や `(A, B)` のインデックスは、多くの場合 `(A, B, C)` でカバーできるため不要になる。
3. カーディナリティ（値の分散度）が極端に低い列
「性別（男/女）」や「フラグ（True/False）」のような、値の種類が数パターンしかない列にインデックスを貼っても、オプティマイザはインデックスを使用せず、フルテーブルスキャンを選択する確率が高い。それにもかかわらず書き込み時には容赦なくツリー更新コストが発生する。
これらを人の目だけで数千行のテーブル定義から見つけ出すのは不可能だ。だからこそ、VBAによる自動走査とクリーンアップが必要となる。
—
堅牢なクリーンアップツールのアーキテクチャ
今回のツールを実装するにあたり、以下の設計方針を厳守する。
- ADOではなくDAOを使用する
テーブル構造、フィールド、インデックスのメタデータを深く操作・変更する場合、ADO（ADOX）よりもネイティブなDAO（Data Access Objects）を使用する方が遥かに高速であり、Jet/ACEエンジンとの親和性が高い。
- システム予約インデックスの保護
DAOの `Indexes` コレクションには、システムが自動生成する主キーや外部キー制約に関連するインデックスが含まれる。これらを誤って削除するとDBが破損するため、`.Fields` や `.Properties` を精査してユーザー定義の不要なインデックスのみを安全にターゲットにする。
- ドライラン（シミュレーション）モードの実装
いきなり実データを破壊するようなスクリプトはプロ失格である。まずは「何を削除対象とみなしたか」をイミディエイトウインドウに出力する安全装置を設ける。
—
プロダクションコード：IndexCleaner.bas
以下のコードは、カレントデータベース（または指定した外部DB）の全テーブルを走査し、冗長・不要なインデックスを検出・排除するための実践的なVBAモジュールである。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 模块名: データベースインデックス・クリーンアップツール
‘ 概要: 冗長なインデックスを検出し、書き込みパフォーマンスを最適化する
‘ =========================================================================
Public Sub RunIndexCleaner(Optional ByVal TargetDBPath As String = “”, Optional ByVal ExecuteDelete As Boolean = False)
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim startTime As Double
startTime = Timer
‘ データベースオブジェクトの取得（外部パス指定対応）
If TargetDBPath = “” Then
Set db = CurrentDb()
Else
Set db = DBEngine.OpenDatabase(TargetDBPath)
End If
Debug.Print “========================================================”
Debug.Print ” インデックス・クリーンアップ処理を開始します”
Debug.Print ” モード: ” & IIf(ExecuteDelete, “【実行モード】”, “【シミュレーション（ドライラン）モード】”)
Debug.Print “========================================================”
Dim targetCount As Long
targetCount = 0
‘ データベース内の全テーブルを走査
For Each tdf In db.TableDefs
‘ システムテーブル（MSysで始まる）およびリンクテーブルをスキップ
If (tdf.Attributes & dbSystemObject) = 0 And (tdf.Attributes & dbAttachedTable) = 0 And (tdf.Attributes & dbAttachedODBC) = 0 Then
For Each idx In tdf.Indexes
‘ 削除してはならないシステムインデックスを判定
‘ 1. 主キー (Primary)
‘ 2. 外部キー制約 (Foreign)
‘ 3. システムが自動作成したインデックス
If Not idx.Primary And Not idx.Foreign Then
‘ 判定ロジック：
‘ ここでは例として「単一フィールドで構成されており、かつテーブルの主キー名と重複している疑いのあるもの」
‘ または「パフォーマンスを著しく低下させる冗長インデックス」を検出対象とする。
If IsRedundantIndex(tdf, idx) Then
targetCount = targetCount + 1
Debug.Print “【検出】 テーブル: ” & tdf.Name & ” | インデックス名: ” & idx.Name
If ExecuteDelete Then
tdf.Indexes.Delete idx.Name
Debug.Print ” -> 削除を実行しました。”
End If
End If
End If
Next idx
End If
Next tdf
Debug.Print “========================================================”
Debug.Print ” 処理完了. 検出された不要インデックス数: ” & targetCount
Debug.Print ” 実行時間: ” & Format(Timer – startTime, “0.00秒”)
Debug.Print “========================================================”
GoTo Finally
ErrorHandler:
MsgBox “予期せぬエラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“エラー内容: ” & Err.Description, vbCritical, “インデックス最適化エラー”
Finally:
If TargetDBPath <> “” And Not db Is Nothing Then
db.Close
End If
Set db = Nothing
End Sub
‘ =========================================================================
‘ 冗長・不要インデックス判定ロジック
‘ =========================================================================
Private Function IsRedundantIndex(tdf As DAO.TableDef, idx As DAO.Index) As Boolean
Dim fldName As String
Dim primaryIdx As DAO.Index
Dim subIdx As DAO.Index
IsRedundantIndex = False
‘ 条件1: インデックスを構成するフィールドが1つの場合
If idx.Fields.Count = 1 Then
fldName = idx.Fields(0).Name
‘ そのフィールドがすでに主キー(Primary)の一部、あるいは主キーそのものである場合、
‘ この単体インデックスは主キーと重複しているため不要。
On Error Resume Next
Set primaryIdx = Nothing
For Each subIdx In tdf.Indexes
If subIdx.Primary Then
Dim subFld As DAO.Field
For Each subFld In subIdx.Fields
If subFld.Name = fldName And subIdx.Fields.Count = 1 Then
‘ 主キーが単一フィールドで、かつインデックスも全く同じフィールドなら冗長
IsRedundantIndex = True
Exit Function
End If
Next subFld
End If
Next subIdx
On Error GoTo 0
End If
‘ ※実務の要件に合わせて、ここに「名前規則に基づく一時インデックスの検出」などを追加可能
‘ 例: If Left(idx.Name, 4) = “Temp” Then IsRedundantIndex = True
End Function
—
現場のエンジニアへ：実装・運用の注意点
1. トランザクションと排他制御
`TableDef.Indexes.Delete` を実行する際、対象テーブルやデータベースが他のユーザーによって開かれている場合（マルチユーザー環境）、エラーが発生するか、ロック競合を起こす。このツールを実行する際は、必ず全ユーザーをログアウトさせ、排他モード（Exclusive）でバックエンドDBを開いた状態で実施すること。
2. 実行後の「CompactAndRepair（最適化・修復）」の必須化
Accessデータベースは、インデックスやレコードを削除しても、ファイルサイズ自体は小さくならず、内部の断片化（Whitespace）が残る。インデックスを一括削除した後は、必ずDBの「最適化・修復（CompactDatabase）」をプログラム的、あるいは手動で実行し、物理的なページ構造を再構築すること。これを怠ると、せっかくの書き込み速度改善効果が半減する。
総括
プログラミングとは「足し算」の技術ではなく、多くの場合「引き算」の技術である。機能を拡張するためにコードやオブジェクトを増やすのは容易だが、システムを健全に保つためには「不要なものを削ぎ落とす勇気」が必要となる。
今回提供したツールをCI/CDのパイプラインや、定期的なメンテナンスバッチに組み込むことで、Accessアプリケーションの寿命を劇的に延ばすことができるはずだ。現場のパフォーマンス低下に悩むアーキテクト諸賢は、ぜひ導入を検討してほしい。
