<HOME　　<お願い事項 　　<Access2002 TOP　　　<Access97 TOP　　　<サイト内検索
MS-Access2000超入門部屋--100万件のデータベース？

景気のいい話じゃありませんか。ねえ。

「Accessって、レコード件数何件くらいまで大丈夫なんですか？？？」って聞かれて、答えに困ったことありません？
これは・・・一概に言えないんですよね。。。。

まず、リレーショナルデータベースっていう考え方の中には、レコードの件数制限というのはないのです。論理上は無限に入ります。
どっちかというと、件数より、全体のファイルサイズとか、データ領域のサイズとか、「バイト数」みたいな感じで制約があるものの方が多いかもしれないです。
MS-Access2000は、MDBファイルの最大サイズが2GBです。テーブルひとつの最大サイズは１GBです。これは、ヘルプの「MS-Accessの仕様」に載ってますので、気になる方は押さえておいてください。あ、うちのサイトにも載せてありますので、よかったら見ていってください。
その範囲の中なら、何件データが入ってきてもぜんぜん問題なしです。

っていっても・・・何件入力したら2GBになるのか、見当がつかないですよね・・・。

今回は、適当なデータベース作って、たくさんレコード追加してどれくらいのファイルサイズになるか試してみます。わたしゃ暇人なので（笑）
でも、必ずしもどういうデータベースでも同じファイルサイズになるわけじゃないので（あたりまえですが）、これくらいだと、これくらいかな・・・って感じで、ざっくりだいたいの検討材料にしていただければって思います。
で、どんなデータベースで試しましょうかねぇ・・・。

100万件、といっても、テーブルのフィールドの数とかでぜんぜん違ってくるってことは、みなさんご理解いただけますよね。
細かいお話をすると、主キーをいくつつけてるかとか、インデックスっていう指定をしてるかしていないとか、そういうことでテーブルのサイズって変わってくるんです。インデックスをひとつつけることで、そのフィールドに対する検索は速くなるわけですが、その分余分にスペースを取ります。本の後ろに「索引」ページとかある場合ありますよね。あんな感じで、引きやすいように別にページを設けてるみたいなイメージになりますからね。インデックスって。検索のスピードは上がるけど、その分場所を取るって感じですので、件数の多い大きなテーブルになりそうな場合は、計画的に設定していかないといけなかったりしますね。でも・・・こういうのはちょっと難しいですよね・・・。

データベースウィザードっていうのを使って、ありがちなデータベースを作っちゃってみましょうかね。
ありがちなデータベースに含まれてそうなテーブル、クエリ、フォーム、レポートを適当に作ってくれるというありがたいようないらないような機能です。でも、データベースつくりの土台にするにはちょうどいいかもしれませんよ。

「受注管理」っていうのを作ってみました。テーブルとかクエリとか、いくつか作成されました。
お客様から注文を受けたら1件伝票入力して、その後、発送と入金伝票を起こすような、カンタンなシステムの土台となるテーブル群ですね。

ついでにフォームとかレポートとか、そのもとになるクエリもできますね。
実用向きかどうかはわからないですけれど、まあ、MS-Accessから見た「一般的な受注処理システムに必要と思われるオブジェクト」がそろいました。

と、この時点で、そっとそっと「受注管理.mdb」のファイルサイズを見てみると・・・。

うわー、もう1MB超えてるんですね。フロッピーディスクにぎりぎりおさまるくらいのサイズか・・・。

と、ここでひとつ、「データベースの最適化」って言葉を思い出さないといけないですよね。
「最適化」については、ヘルプを参照してみてください。また、工房内の「Q＆A集」でも、ちょっとだけ扱ってます。
つまり・・・データベースウィザードは、各種オブジェクトを作るために一時的に作成されて最後に削除されるようなオブジェクトがいくつかあったりして、今、このMDBファイルの中身は、ぽっかり空間が空いてる状態かもしれない、ってことですね。
レコード件数が多くなる可能性の高いデータベースを作る可能性のある方は、「データベースの最適化」ってとこ、押さえといてくださいよ。

んじゃ、最適化してみましょうかねぇ。

すると？？？

お、けっこう縮んだじゃありませんか（笑）
んでも720kbあるのか・・・。ウィザードで作ったフォームとかレポートって、絵が入ってたりしてちょっとボリュームありますからね。しょうがないかな・・・。

では、メインとなるテーブルをちょっと見てみましょうかね。
「受注伝票」というテーブルと、「受注明細」というテーブルがあるんですが、多分これがメインとなるテーブルでしょうね。

けっこういろんな項目持ってますね。
おそらく、「やまいもとさつまいもとハリセンください」という注文だったら、「受注伝票」1件に対し「受注明細」に3件レコードが入ってくるようになるのでしょう。このふたつのテーブルはそういう関係ですね。で、「受注ID」というフィールドでつながることによって、ひとつの受注伝票の内容を網羅するわけです。

会社や業務内容によって、項目の内容はゼンゼン変わってくると思いますが、まあ、これくらいのフィールド数のテーブルで、何件くらいデータを入れたらどれくらいのファイルサイズになるか、ちょっち挑戦してみますね。

あ、余談ですが・・・。

クエリがいくつか一緒に作られてたので、ちょっと開いてみようと思います。
まだテーブルの中に1件もデータがないですから、何も出てきませんし、ものによってはエラーになったりするかもわかんないですが・・・。

わはは。なーんもないですね。見ててもしょうがないから閉じます。

この状態で、MDBファイルのサイズを見てみると・・・。
ほんのわずかですけど、ファイルサイズ大きくなってるんですよ。

クエリとかフォームを開いただけでも、ほんのちょっとずつ大きくなるんです。
っていうか、パソコンのファイルって、メモリだけで仕事してるわけじゃなくて、一時的にハードディスクの中の空いてるところを使って仕事したりするんです。で、そのソフト終了させるとき、一時的に使ってた場所をキレイにしながらさよならしてるわけなんです。
MS-Accessの場合は、自分自身のファイルの中に、一時的な作業領域を作って、閉じるときキレイにするんですけどMDBのファイルサイズはそのまんまだったりするわけですね。正確にはちょっと違うかもしれないですが・・・無作為にファイルサイズが大きくなっているわけではなくて、そんな内部事情がいろいろ絡んでるんだと、そんなふうに考えてください。

だからといって、ちょっと使うたびに最適化するというのも、少々問題があります。
「最適化」という処理自体も、けっこう負担の大きい作業だからです。そのデータベースにもよりますけど、目に見えてファイルサイズが大きくなっていると気づいたときに、手作業で慎重に最適化するような感じでやれば、問題はないと思いますよ。

さて・・・手始めに「顧客マスター」という名前のテーブルに、適当にデータをつめてみました。

とりあえず1000件くらい・・・。
取引先のお客さんのリストですね。
え？やる気あるのかって？ありますよ！だっていくらあたしだって1000件分も架空の会社の名前や住所、思いつかないですよう。

1000件、テーブルにレコードを書き出したところで・・・。

む、けっこうファイルサイズ増えますね。
フィールド数も多いテーブルだからな。。。あ、でも。。。

最適化したら、コレくらいになりました。↑

んじゃあ、続けて、2000件にしてみようかな。

おお、増えますねぇ。

で、最適化すると・・・。

ちょっと縮むな。。。

1000件のときとの差は、だいたい200kbちょっとくらいでしょうか。
このテーブルで、1000件データが増えるということは、だいたい200kbちょっとくらいずつ増える、っていうことになりますね。

さて、じゃあ、メインのテーブル、行ってみましょうか。
先ほどもチラッとみましたけども、このテーブルです↓

とにかくめちゃめちゃ受注が入ってきたとして、10万件、いってみましょう。

（ゼエゼエ）10万件、到達しました。ファイルサイズは・・・。

うーむ、20MBちょっとか。。。最適化すると・・・んでも20MB弱ってとこですね。

あ、下に「受注管理」っていうファイルがもういっこありますが、これ、なんだかご存知ですか？
拡張子が「ldb」ってつきます。ロックファイルって呼んでますけど、要するに「そのMDBファイルが、現在開いているか、閉じているか」をあらわすファイルです。開いていれば、MDBファイルと同名のldbファイルができます。閉じると、自然に消えてなくなります。閉じずにパソコンの電源切っちゃったり、無理やり強制終了しちゃったりすると、ldbファイルがそのまま残っちゃってることがあります。ldbファイルが残っていると、MDBファイルを開くことができなかったりします。（使用中だと勘違いする）
そういうときは、ldbファイルを削除してから、MDBファイルを開くようにするとよいでしょう。でも、MDBファイル開いているときにldbファイル削除しようとすると、おかしくなっちゃいますから、普段は気にする必要のない縁の下の力持ちファイルです。

じゃあ、今度は、「受注明細」にデータを書き込んでみますね。

ええと、まずは、100万件、いってみます。

おほほ、フィールド数の少ないテーブルですけれど、いっきに80MB近くまでいきましたね。

次、200万件・・・。

ふむ・・・100万件増えるごとに、60MB近く増えてるみたいですね。

（ゼエゼエ）・・・ご、500万件、データ作りました。え？ぜんぶ同じ内容じゃないかって？そんなこと言わないでくださいよう。
テストデータだっていったって、大変なんですから・・・。

で、この状態でファイルサイズをみてみると・・・。

こんな感じかな・・・。
まあでも、100万件増えるごとに最適化していけば、そんなにあっぷあっぷするようなファイルサイズではないですね。

あ、ご存知かもわかんないですけど・・・・・・最適化って、一旦MDBを削除して、再度同じ名前で作り直してるんです。
コピーを作っておいて、以前のMDBを削除して、でもってコピーしたファイルに以前のMDBの名前を付けてるんですね。コピーのファイルは一時的なものなので、db1とかdb2とか、適当なものがつきます。

つまり・・・最適化の最中って、一時的にMDBが2つになるわけです。150MBのMDBを最適化するために、もう150MB分、ハードディスクを使うのです。
当然、最適化にも時間がかかるようになります。この最中に電源障害とか起こってパソコン停止したら、多分・・・MDBファイル、壊れちゃうかもしれないですね。
ファイルサイズの大きなMDBというのは、こうしたリスクも背負わなければならないわけです。

MS-Access2000は、Access97のときよりもより安全に最適化処理を進めることができるよう、強化されています（だそうです）。
でも・・・こうした内部の様子を見ると、細心の注意を払った方がよさそうですよね。
だからこそ、「データベースの最適化を自動でやりたい」とか考えずに、みんなでこの辺の知識をきちんと持って、定期的に手作業でバックアップ・最適化を実行するようになさった方がいいんじゃないかなって思います。
ファイル壊れちゃってからマイクロソフトのせいにしたりMS-Accessの悪口あっちこっちに書いたりしてる人もいますね。気持ちはすごくわかるんですが・・・・やっぱり、こういうことはある程度理解して使っていくべきじゃないかなぁって思うんです。

なんて、こう書いても結局「Accessって壊れやすいらしい、バグらしい」って誤解釈なさる方も多々あるかもわからんですが・・・。
ぜひとも、より安全に運用するための方法、ご自身で見出していってください。

500万件のテーブル相手の仕事というのは、どんなもんなんでしょう。
例えば、カンタンなフォームを作ってみて・・・・・。

フィルタを使って、受注明細IDで検索できるような仕組みを作ってみました。
「テキスト12」というテキストボックスにIDを入力してEnterすると、該当のレコードが表示されるようにしたんですが・・・。

まあ、特に500万件相手だからといって、遅いとかいう感じはないですね。むしろ、10件くらいのときと同じ感じです。

多分、500万件あっても1000件あっても、↑こうやってフォームに1件表示させたり、前のレコードに移動させたり、1件検索してみたり、500万件の中からなんかしらの条件で絞り込んで20件だけ印刷したり・・・そんなこんなでフォームやレポート閉じたり開いたりする分には、100件だろうが500万件だろうが、あんまり変わらないです。

でも、件数の多さに比例する仕事もあります。
例えば、更新クエリを使って、500万件全レコードの価格を書き換えるとか、500万件を並べ替えるとか（再クエリするとか）だと、やっぱそれなりに時間かかります。こういうのはしょうがないですよね。1レコードずつ書き換えてるんですから、500万件あればそれなりに時間はかかります。

ようるすに・・・じゃない、ようするに、「ほんとに、500万件全部いっこのテーブル、いっこのデータベースの中にずっとつめこんでおかなければならないのか、しょっちゅうしょっちゅう500万件全件に対して更新クエリとかやんなきゃなんないようなお仕事なのか・・・業務面から見直しする必要があるんじゃないか、ってことですね。
どうしても見直しができない場合はしょうがないです。Accessのために必要なデータを入力しないようにするなんてことになったら、本末転倒ですからね。でも・・・ほんとに、見直しの余地、ありませんかね？？？考えてみる価値はあると思うんですよ。

MS-Accessは、MS-Excelとはぜんぜん違う、というお話は、他のコーナーでも繰り返ししてますし、みなさんこの辺はオッケーだと思いますが、違いのひとつがこの辺に現われます。
MS-Excelも、たくさんのデータを入力することができますよね。でも、入力したデータは、ひとつのファイルの中ですべて行動を共にします。つまり、5万件入力されたExcelのブックを開こうとすると、5万件すべてを開くことになるのです。
10m四方の模造紙を広げて、メモを取っているようなもんです。例え10文字でも、5文字でも、まず10m四方の模造紙を広げます。全部広げ終わるまで、入力はおろか見ることすらできません。で、終わったらたたみます。広げたりたたんだりするのに時間はかかりますけど、空いているところに好きなように書き込むことができて、便利は便利です。

MS-Accessは、例え500万件のデータがテーブルにあったとしても、とりあえず1回の処理で相手にするのは1レコードだけです。500万ページあるノートがあって、必要なときに必要なページだけはがして書き込んだり消したりして、用が済んだら元のところに戻す、といったイメージです。ノート全体を広げて使うのとは、ちょっとイメージが違います。

わたしは、データベースと表計算ソフトの使い分けは、データ件数やデータ容量だけで判断するもんではないと思ってます。基本的には他のコーナーでもお話したことですが「仕事の履歴を残すものと、残す必要のないもの」という観点で考えるべきだと思ってます。

どっちがよい、というわけではないですよ。その辺、誤解なさらないでクダサイね。どっちもメリットがあり、どっちもデメリットを持っています。
また、使う人の「好み」もあります。はっきり言って「好み」の部分が、非常にポイントになりますよね。ソフトウェアの評価って。。。だから、わたしは、Accessっていいのか悪いのか、わからないですし・・・・。最終的には「お好み」でいいんじゃないですかね。

パソコンソフトなんてものは「Accessってどんなことができるのか」じゃなくて、「Access使ったらわたしにはどんなことに使うのか」ってことなんです。
できることできないことは、人によってバラバラなんですよ。Accessも、Excelも。。。「正解」は存在しないのです。

んじゃあ、余興ついでに、目いっぱいフィールドの多いテーブルを作ってやってみようと思います。
別のデータベース作って、下のようなテーブル作ってみました。

254フィールドの、横に長ーーーーーいテーブルです。
で、適当に、数値型や日付時刻型のフィールドを設けてみました。・・・こんな横に長いテーブル作ることって、あります？

とりあえず10件だけ入力してみて、ファイルサイズを確認してみると・・。

こんなもんですね。まだまだ。

で、5万件になると・・・あ、ちょっぴりオーバーしちゃいましたけど、まあこんな感じですと・・・。

おおお、もう200MB超えてるわ。

で、10万件まで書き込んでみると・・・。

ひえー。もう400MB超えてるだ。
500万件のテーブルを持つさっきのMDBと比べても、こっちの方がだんぜんファイルサイズが大きくなりましたね。

これも、一概には言えないですが・・・。リレーショナルデータベースのテーブルというものは、「縦に伸びる」ことでその特性を活かすことができるものなんです。
ひとつのテーブルに、あんまりたくさんのフィールドを持つことは、まあ、悪いことではありませんけれど、非効率といえば非効率なんです。
できれば、ひとつのテーブルのフィールド数は、少ない方がいい・・・。「じゃあいくつくらいまでいいの？」っていうことじゃなくて、少なくできるなら少ない方がいいってことです。

だから、テーブルを小分けして、リレーションシップとかやってつないだりするんですよ。
わざわざつなぐなら、いっこのテーブルにしとけばいいじゃないの、って思う人もいるかもしれないですよね。でもね、フィールド数は、少ない方がいいんですよ、できるだけ。。。。

さて・・・。
いろいろごちゃごちゃと後ろ向きなことも書いちゃいましたけど、時々用心しながら最適化して、時々バックアップとって・・・と、いちおうひととおりの気配りをしていれば、500万件だろうが1000万件だろうが、レコード持つことできそうですよね。また、データ件数の見積りも、1000件ずつくらいそれっぽいデータ入力して、ファイルサイズをみてみればだいたいの概算はできそうです。

結局、どういうデータベースになるのか、項目の洗い出しとテーブル設計の部分をやっつけちゃわないと、「何件くらいレコード持つことができるのか」は、わかんないってことなんですけどね・・・。

データ件数の多くなりそうなデータベースを作る場合、もうひとつ用心しなくてはならないことがあります。
それは、「共有」です。

これだけネットワーク環境が強化されている今、「サーバーにMDBファイルを置いて、みんなで共有して使う」っていう考え方は、決して特別ではないと思います。そして、MS-Accessは「マルチユーザー環境」というものを可能にしています。つまり、ファイルをサーバーに置いて、みんなで「共有モード」で同時に開くことができるのです。

正確には・・・「開くことができてしまう」といったほうがいいかもしれません。確かに、これってとても便利です。MDBファイルをみんなが個々に持っていたら、顧客の登録とかみんながバラバラに行うことになってしまい、データの共有って不可能ですよね。サーバーに置いとけば、みんなで個々に顧客登録をして、それをみんなでシェアできます。

でもね、これって、「みんなで同時に開く」っていうのとは、ちょっと違うんですよ。
確かに共有することはできますし、同時に開くこともできちゃいますが、MS-Accessの心臓部は、大勢の人が同時にMDBファイルを開くという状態に、さほど強くありません。どっちかというと弱いです。「同時に」っていうところがポイントです。

論理上の機能としては、Accessだってリレーショナルデータベースのはしくれですから、同時にみんなで開いて使う（マルチユーザー環境）を実現します。でもね・・・MDBって、所詮パソコンのファイルでしょう。みんなで同時に開いてたら、MDBファイルとして負荷がかかって、パソコンファイルとして破損する恐れがあるのです。テーブルとかクエリとかフォームは壊れないんですが、その外側のMDBファイルが壊れちゃうこと、あるんですよ。。。

これで泣いちゃった人も、けっこういるんじゃないでしょうかねぇ・・・。
今は、サーバーの中だろうが自分のパソコンのハードディスクだろうが、あんまり意識せずに使ってる人も多いみたいですし・・・。自分のPCのハードディスクに保存されてるファイルを開いているのか、サーバーの中のファイルを使ってるのか、よくわかんない、って人もいますよね。

みんなで同時に開く可能性があるのなら、MDBを二つに分けて設計するとよいでしょう。
「テーブルが入ってるMDB」を作り、これをサーバーに置いて、「フォームとかレポートとかが入ってるMDB」を使用者の数だけ用意して、各PCのハードディスクに保存しておきます。で、「テーブルのリンク」という機能を使って、サーバー内のテーブルを利用するのです。
こうすれば、テーブルの中のデータはシェアできますが、サーバーの中のMDBファイルを同時に開いているわけではないので、思わぬ障害から大切なデータを守ることができるはずです。

これは、データの件数にかかわらず、大勢で共有したいとき、必ず考慮しなくてはならないことですので、ちょっと今回の趣旨から外れちゃうんですが・・・。
でも、こうしたことちょっぴり気をつけていれば、「ファイルが開けなくなったー」なんてことはきっと起こらないと思いますよ。
安全で、快適なデータベースを構築していってくださいね。

（オシマイ）