Excel×Access連携パート5【ExcelVBAでAccessデータベースを最適化】

1 Excel×Access連携パート5【ExcelVBAでAccessデータベースを最適化】
- 1.1 ﾃﾞｰﾀﾍﾞｰｽの最適化とは？
2 データベースの最適化と修復の目的

この記事を書いた人
最新の記事

taka

あることがキッカケでVBAを独学で勉強しました、今ではブログを通してVBAでできることを解説しつつ、VBAや他の言語の勉強、ブログ運営の勉強をしています(^^♪

Excel×Access連携パート5【ExcelVBAでAccessデータベースを最適化】

takaです、今回はいつも以上にニッチなお題です(笑)

【ExcelVBAでAccessデータベースを最適化をしてみる】という事をやってみました(^^)

まあ誰が得するのかわからない情報かもしれませんが、、、何とか形になりましたのでお楽しみください(。-∀-)

ﾃﾞｰﾀﾍﾞｰｽの最適化とは？

そもそもAccessデータベースを最適化する意味とは？このことについてはMicrosoftが下記のように説明しています。

データベースの最適化と修復の目的
この概要では、[データベースの最適化/修復] コマンドを使用して、使用に伴ってファイルのサイズが大きくなり、ファイルが破損するなど、データベースに影響が及ぶような問題を防ぎ、修正する方法について説明します。
データベースファイルは使用に伴ってサイズが大きくなる
データの追加や更新、デザインの変更に伴って、データベースファイルのサイズは大きくなっていきます。新しいデータの追加だけでなく、次のような原因もあります。
Access では、さまざまなタスクを実行する目的で、一時的な隠しオブジェクトが作成されます。この一時オブジェクトが不要になった後でもデータベース内に残ることがあります。
データベースオブジェクトを削除しても、そのオブジェクトが使用していたディスク領域は自動では解放されません。オブジェクトを削除しても、データベースファイルはそのディスク領域を引き続き使用します。
データベースファイル内に一時オブジェクトや削除済みオブジェクトの残骸が増えてくると、パフォーマンスが低下することがあります。オブジェクトを開いたりクエリを実行したりする時間が通常より長くなるなど、一般的な操作が全般に遅くなったように感じられます。
注: 最適化ではデータは圧縮されず、不要な領域が削除され、データベースファイルのサイズが小さくなります。
データベースファイルは破損することがある
状況によっては、データベースファイルが破損することがあります。データベースファイルをネットワーク経由で共有し、複数のユーザーが同時にファイルを直接編集する場合、まれではありますが、そのファイルは破損するリスクがあります。破損のリスクは、ユーザーがメモ型フィールドのデータを頻繁に編集する場合に大きくなり、時間経過と共にリスクは大きくなります。このリスクを軽減するには、[データベースの最適化/修復] コマンドを使用します。
多くの場合、このような破損の原因は、Visual Basic for Applications (VBA) モジュールの問題にあり、データが損失するリスクはありません。ただし、このような破損は、VBA コードの損失やフォームが使用できなくなるなど、データベース設計が破損するリスクにつながる可能性があります。
まれに、データベースファイルの破損によってデータが失われる場合もあります。通常、失われるのは、単一のユーザーが行った最後の操作、つまりデータに対する単一の変更に限られます。ユーザーがデータの変更を開始した後で、たとえばネットワークサービスの停止によってその変更が中断された場合、Access は、そのデータベースファイルが破損したものとしてマークします。このファイルは修復可能ですが、修復が完了した後で一部のデータが失われている場合があります。
ヒント: データベースを分割すると、ユーザーが直接アクセスしない別個のファイルにデータが保存されるため、データベースファイルの破損の予防と、データ損失の範囲を抑えるうえで有効です。
※Microsoftより引用

簡単に言えば【データを追加や削除を繰り返すと、見えないごみが溜まってきて処理が遅くなったりデータベースの破損の原因になる。】という事を言っているのです(。-∀-)

上記のことを考えると、大事な顧客情報などを扱うﾃﾞｰﾀﾍﾞｰｽである以上この最適化作業は定期的にする必要がある作業言えます(。-`ω-)

ですので今回はExcelからDBを最適化できないか？と考えました(笑)

ExcelVBAでAccessﾃﾞｰﾀﾍﾞｰｽを最適化する準備

まずExcelVBAでAccessの最適化するには、Accessのオブジェクトを操作するために参照設定をする必要があります。

まずVBEを開いて頂き、「ツール」→「参照設定」と進んでください。

次に【Microsoft Access 15.0 object Library】にチェックを入れてください。

ExcelVBAでAccessﾃﾞｰﾀﾍﾞｰｽを最適化するコード

ではエクセルVBAで最適化するのコードをご紹介します。解説はコード内で行っています。

流れとしては、【最適元】を最適化したものを【最適化後】という名前に変更して保存して元の【最適元】を【最適元Backup】にファイル名を変更。
そして【最適化後】を【最適元】にファイル名を変更することで最適化とBackupを同時に行うという形をとっています。

sub Sample()

   Dim res As Integer
   Dim AWP As String
   res = MsgBox("データベースを最適化します、よろしいですか?", vbYesNo)
    If res = vbNo Then Exit Sub

 
' データベースを最適化する
    On Error GoTo DB_Err
      AWP = ActiveWorkbook.Path & "\"
    ' データベースを最適化する。";pwd=○○○〇"はDBにてパスワードを設定している場合に使用。
         DBEngine.CompactDatabase AWP & "最適元.accdb", _
         AWP & "最適化後.accdb", , , ";pwd=1234"
      
      '前回のBackupファイルがあった場合前のファイルは削除する
     If Dir(AWP & "最適元Backup.accdb") <> "" Then
        Kill AWP & "最適元Backup.accdb"
    End If

      '最適元はファイル名を変更してBackupとして保存
      Name AWP & "最適元.accdb" As AWP & "最適元Backup.accdb"
     ' 最適化後のファイルの名前を元に戻します。
      Name AWP & "最適化後.accdb" As AWP & "最適元.accdb"

 
    MsgBox "最適化が完了しました。"
  
Exit_DB:

    Exit Sub

DB_Err:
    MsgBox Err.Number & Err.Description
    Resume Exit_DB

End Sub

実行結果

ではさっそく実行結果を見ていきましょう～