もくじ

taka



最新記事 by taka (全て見る)
- 『雑記』最近の私の話~ - 2019年9月26日
- 【動画】自作パソコン構成変更!ケースをNZXT H500に交換しました! - 2019年6月5日
- VBA 『Array関数』指定した要素で配列を作成する - 2019年5月23日
- 「動画」初完全ワイヤレスイヤホン『Jabra Elite Active 65t』 - 2019年5月17日
- VBA 日付で令和を簡単に判定してみよう - 2019年5月7日
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
実行結果
これが実行前
これが実行後です。多少ですが最適化した結果ファイルサイズが小さくなったことがわかっていただけると思います( ^^) _旦~~
今回は数件のデータでしたのでこの程度でしたが、データの件数が増えたりしてくるともっと効果がわかるようになると思います(。-∀-)
※画像はテスト中の物です【最適元20170808】は【最適化Backup】という名前に置き換えてあります。
データベースをパスワード設定している場合の注意点
最後に注意点を、データベースに対してパスワードをかけていることも少なくないと思います。今回のコードで最適化できるのは【以前の暗号化方法を使用する】で暗号化している場合に限りますのでご留意ください(。-∀-)
ダウンロード
当サイトの免責事項をお読みになってからお使いください。

Accessデータベース最適化
フォロー・チャンネル登録お願いします
VBA以外についてのブログはこちら↓
https://yb-log.com/■Twitterフォロー
Follow @taka_pc1027■YouTubeチャンネル登録
ファイルやコードの利用、WEBサイトの利用について
サンプルコードなどは当サイトの免責事項をよくお読みになってからお使いください。Sponsored Link
WordPressでブログを始めるなら
WordPress簡単インストール&安心の安定性


この記事が気に入ったら
いいねしよう!
最新記事をお届けします。
Yay google is my king aided me to find this outstanding internet site!
I think you have mentioned some very interesting
details, appreciate it for the post.