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

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を同時に行うという形をとっています。

実行結果

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

これが実行前

これが実行後です。多少ですが最適化した結果ファイルサイズが小さくなったことがわかっていただけると思います( ^^) _旦~~

今回は数件のデータでしたのでこの程度でしたが、データの件数が増えたりしてくるともっと効果がわかるようになると思います(。-∀-)

※画像はテスト中の物です【最適元20170808】は【最適化Backup】という名前に置き換えてあります。

ﾃﾞｰﾀﾍﾞｰｽをパスワード設定している場合の注意点

最後に注意点を、ﾃﾞｰﾀﾍﾞｰｽに対してパスワードをかけていることも少なくないと思います。今回のコードで最適化できるのは【以前の暗号化方法を使用する】で暗号化している場合に限りますのでご留意ください(。-∀-)

ダウンロード

当サイトの免責事項をお読みになってからお使いください。

Accessﾃﾞｰﾀﾍﾞｰｽ最適化 734.65 KB

Download

ブログランキング

【E-VBA】はブログランキングに登録しています(∩´∀｀)∩