Accessが「なぜか重い」の正体。QueryDefの断片化を撲滅する最適化術
こんにちは。Accessの迷宮を探索する皆さんの道標として、今日は少し「玄人好み」でありながら、避けては通れない最重要テーマについてお話しします。
「Accessファイル（ACCDB）を長く使っていると、中身は変わっていないのにどんどん肥大化する」
この現象、経験したことはありませんか？実はこれ、皆さんが作成している「動的SQL（パラメータークエリ）」の残骸が、幽霊のようにファイル内に居座り続けていることが原因かもしれません。
今回は、Accessのクエリ定義（QueryDef）の裏側を覗き込み、ファイル肥大化を防ぐための「クリーンアップ・アーキテクチャ」を伝授します。
—
1. なぜ「QueryDef」がファイル肥大化を招くのか？
VBAでSQLを組み立てる際、多くの人が以下のようなコードを書きます。
‘ よくある実装例（これが肥大化の温床！）
CurrentDb.CreateQueryDef(“TempQuery”, “SELECT FROM T_Sales WHERE ID = ” & Me.txtID)
このコードを実行すると、Accessは「TempQuery」という名前のオブジェクトを保存しようと試みます。もし同じ名前が存在すればエラーになりますから、削除してから再作成する……というサイクルを繰り返すと、Access内部では「削除されたはずの断片」がゴミとして残り続け、ファイルが異常に肥大化します。
Accessは一度割り当てた物理領域を簡単には解放しません。これが、システムが日に日に重くなる最大の理由です。
—
2. 肥大化を防ぐ「一時クエリ」の極意
解決策はシンプルです。「保存してはいけないものは、どこにも保存しない」こと。
QueryDefを明示的に作成・保存せず、`DAO.QueryDef`の「一時オブジェクト」を活用すれば、ファイルへの書き込みは最小限に抑えられます。
推奨される実装コード
以下のコードをコピーして、ご自身の環境に組み込んでみてください。これが「ファイルサイズを肥大化させない」プロの書き方です。
Public Sub RunDynamicSQL(strSQL As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
‘ 重要：名前を付けずに一時的なQueryDefオブジェクトを作成する
‘ これにより、ファイル（ACCDB）に物理的なゴミを残さない
Set qdf = db.CreateQueryDef(“”)
With qdf
.SQL = strSQL
.Execute dbFailOnError
End With
‘ メモリ上の参照を明示的に解放
Set qdf = Nothing
Set db = Nothing
End Sub
このコードのポイント
- `CreateQueryDef(“”)`: 名前を空文字にすることで、Accessは「これは保存不要な一時的なものだ」と判断し、メモリ内でのみ処理を行います。
- 物理的な書き込みゼロ: ファイルサイズを増やすことなく、高速にクエリを実行可能です。
—
3. すでに肥大化したファイルを救済する「定期掃除スクリプト」
もし、すでに「手遅れ」に近い状態なら、まずは一度「最適化」を行う必要があります。VBAからAccessの最適化機能（CompactDatabase）を自動実行するツールを紹介しましょう。
Sub CompactCurrentDatabase()
‘ ※注意：この処理は排他制御が必要なため、単独で実行してください
Dim strSource As String
Dim strDest As String
strSource = CurrentProject.FullName
strDest = Left(strSource, InStrRev(strSource, “\”)) & “Temp_Optimized.accdb”
‘ データベースの最適化を実行
DBEngine.CompactDatabase strSource, strDest
MsgBox “最適化が完了しました。Temp_Optimized.accdbを確認してください。”
End Sub
—
4. エンジニアとして次の一歩へ
ここまで理解できたあなたは、もう「マクロの記録」に頼る初心者ではありません。
- QueryDefは「保存」されるものと「実行」されるものを区別する
- 一時的に使うSQLは、ファイルに書き込ませない
この2点を守るだけで、あなたの作るAccessシステムは、5年後も10年後も軽快に動作し続けます。
「コードを書く」ということは、単に動くものを作るだけでなく、「システムという生き物をどうメンテナンスし続けるか」という設計思想を持つことです。ここをクリアすれば、Access VBAの基本はバッチリですよ。
何か不明点があれば、またいつでも聞いてください。皆さんの開発ライフが、より洗練されたものになることを願っています。
