【実務で大活躍】Access VBAでDBを最適化する方法｜肥大化対策

Accessを業務で使っていると、気づかないうちにDBが肥大化し動作が重くなる問題に直面することがあります。

ファイルサイズが増えて起動も処理も遅くなってきた経験も…

私自身、実務で運用しているAccessシステムでDB肥大化によるトラブルを何度も経験してきました。

本記事ではAccess VBAでDBを最適化する方法を実装例付きでわかりやすく解説します。

なぜDBが肥大化するのか

レコード操作で履歴が蓄積して肥大化

Accessはクエリの実行やレコードの更新・削除で内部に「履歴」がたまります。

普通にシステムを使っているだけでも、気づかないうちに大量の履歴が蓄積されていきます。

実際に私の業務DBでは気づいたらファイルサイズが5倍に膨れ上がっていたこともありました…

VBA自動処理でさらに肥大化

ForやDo Untilなどのループを使った自動処理では、履歴がさらに増加します。

結果として、バックアップ容量も増え動作遅延や起動時間の長さの原因になります。

DB肥大化を防ぐために効率的な開発・最適化が求められるのです！

肥大化の基本的な対策方法

DBの肥大化を防ぐ基本的な方法はいくつかあります。

データベースの最適化／修復

Accessには不要な「履歴」を削除してくれる、データベースの最適化／修復機能があります。

データベースの最適化／修復の手順は次の通りです。

1. 最適化したいAccessファイルを開く
2. 「データベース」タブにある「データベースの最適化／修復」をクリック

なんとこれだけでファイルサイズが軽くなることもあります。

最小限のVBAで開発を行う

ForやDo Untilなどのループ文を多用するとDB肥大化が一気に進むことがあります。

Accessの最適化機能は便利ですが、肥大化を加速させない設計が何より重要です。

ただ動くだけでは不十分。将来的な肥大化を防ぐ設計が現場では求められます！

VBAでDBを自動最適化する方法

VBAを使うことで別ファイルのDBを自動で最適化し、起動速度やバックアップ容量の改善が可能です。

最適化を行う基本コード

VBAでDB最適化を行う基本的なコードは次の通りです。

DBEngine.CompactDatabase dbPath, tmpFile

• dbPath：最適化したいAccessファイルのパス
• tmpFile：一時的に作成する最適後のファイル名

このコードだけで手動作業から解放されます！

実務で使えるVBAコード

Public Sub DB最適化()

    Dim dbPath As String

    Dim tmpFile As String

    dbPath = "C:\Users\user\Documents\MyDB.accdb"

    tmpFile = Replace(dbPath, ".accdb", "_tmp.accdb")

    DBEngine.CompactDatabase dbPath, tmpFile

    Kill dbPath

    Name tmpFile As dbPath

    MsgBox "DBの最適化が完了しました。", vbInformation, "DB最適化完了"

End Sub

これだけで別ファイルのDBを簡単に最適化することができます。

このVBAが行っていることは次の通りです。

ボタン一つで実行可能なため汎用性もばっちりです！

VBA最適化でよくあるエラーと対策

Accessファイルのパスが間違っている

最適化したいAccessファイルのパスが間違っているとエラーになります。

dbPath = "C:\Users\user\Documents\MyDB.accdb"

ファイルパスはフルパスで正しく設定しましょう。

同じフォルダ内のファイルを指定する場合は、次のようにも書けます。

dbPath = CurrentProject.Path & "\" & "MyDB.accdb"

私もパス設定ミスを何度かやってしまいました💦

指定したファイルが開かれている

最適化対象のファイルが他のユーザーやプロセスで開かれているとエラーになります。

実行前に必ず閉じていることを確認しましょう。

元ファイルをKillできていない。

Kill dbPath

Name tmpFile As dbPath

最適化後に元のファイルが残ったままだと同一ファイル名でエラーになります。

Name は上書きできないためKillで元ファイルを削除してからリネームする必要があります。

Killするときは元DBだけを指定します！

まとめ

今回はAccess VBAでDBを最適化する方法を解説しました。

DBを最適化することでファイルサイズを軽くし、容量の圧迫や動作遅延を防ぐことができます。

定期的に最適化を実行して、DBの肥大化を防ぎましょう。

あわせて読みたい

【完全保存版】Access SQLの基本解説と実践例まとめ Accessで業務システムを作るうえで、SQL（クエリ）は避けて通れない重要なスキルです。 しかし「難しい」「実務でどう書けばいいかわからない」と感じる方も多いでしょ…