Excelは今でこそ100万行のデータ行数に、凄まじい速さとなった関数などのおかげで大容量データもストレスなく扱えるようになりました
2003時代では数メガのデータ容量となったExcelファイルは保存するだけでストレスをよく感じたものです
そんな時代によく聞いたのが、容量の問題でExcelに限界を感じて、Accessに移行したという話です
やはり当時数千行データとなると、どうしてもこの話に勝てませんでした
とにかく、わしのPCにAccess入ってへんから知らんがな、と強がりを言ったものですが、数千行のデータとなってもストレスフリーなExcelに育ったのも少し感慨深いものです
さて、脱線しましたが、ここで言いたいのは現在の状況であってもやはりデータの保管という点においてAccessデータベースには敵わないという点です
いくら100万行あるとはいっても、さすがに数万というデータになるときつくなってきます
とはいえ保管されたデータを見やすくする点でExcelにはAccessは全く敵いません
今はPowerBIというアプリもありますが、まだまだExcelの加工・グラフは第一線でしょう
つまり、大容量データの保管をAccessデータベースに、データの利用にはExcelにというのが最大の利用法になります
そしてこの利用法において、最大級にExcelに軍配が上がります
なぜなら、ExcelVBAを利用することで、データベースの作成・操作が可能だからです
データベースの操作を行いつつ、Excel上で加工もできる
Accessが無くても問題はありません、Excelだけで可能です
逆にAccessはあるけど、Excelが無い人はこれが出来ません
今は強がりではなく、Accessは知らんExcelで十分と言えます
これを言うとAccessファンの人の癇に障るかもしれませんが、自分はExcelファンでここに来ている人もきっとそうなので問題なしとしておきましょう
操作の流れ
ExcelVBAでデータベースを操作する場合の流れとしては、大きく2通りあります
1つ目はデータベースファイル、accdbファイルを作成し、そこにテーブルを作成してレコードを登録、加工や集計を行う
必要に応じてワークシートに出力する
2つ目はデータベースファイルを介さず、メモリ上にデータベースを作成し、同じく登録等を行い、ワークシートに出力する
この2つの流れになります
2つ目の方法は少し難しくなりますが、処理の動きをイメージできるようになれば加工・集計にSQLを利用することが出来るようになるため処理の幅が広がります
基本的には1つ目のデータベースファイルを作成して操作を行います
この2つの違いは、データベースに登録したレコードを保存するかしないかという点で考えてください
保存する必要がある場合は1つ目、加工・集計のみであれば2つ目の流れになります
とはいえ、2つ目の流れは1つ目の流れが包括しているのでこのAccessデータベース操作が慣れてきたら挑戦してみてください
まずは以下の記事からデータベースの作成を行ってください
作成が行えたら次に作成するのがテーブルです
テーブルとはExcelで言うテーブル機能のようなものです
Accessデータベース操作はレコードセットを利用したり、SQLを利用するため少しレベルが上がる内容ですが
ここも使えるようになると、ビッグデータが怖くなくなるので1年1ファイルなんて言うケチ臭いことは考えなくて良くなります
ExcelVBAで出来ることの限界を極めるためにここをやりきってみてください
SQLについて
データベース操作において、必ず使用することになるものです
これはオブジェクト操作ではなく、別の言語です
データベースを操作するための言語なのですが、これがなかなか大変なのが扱うデータベースの種類によって微妙な差がある点です
そしてAccessデータベースで使用するSQLはMicrosoft Access SQLという言語を利用します
OracleやMySQL、Microsoft SQL Server等々の有名な言語がありますが、別物です
特にMicrosoft SQL ServerとMicrosoft Access SQLは別なので混同しないようにしてください
このAccessデータベース操作をやり始めた時に、ここの理解が全く出来ておらず
ビューの作成を行い、そのビューの構成を変更したくて「ALTER VIEW」がエラーとなった際に全く原因が分かりませんでした
Microsoft Access SQLにはこのコードがサポートされていません
そこで初めて、いま使っているSQLは一体どの種類のモノなのか？
という疑問点を長く持ち続けて、ようやく気付いたのがMicrosoft Access SQLというSQLを使用していることでした
ほとんどは他のSQLと差は無くエラーが発生することはありません
なのであまり大きく意識をする必要もなかったというのもあります
また、このSQLを明確に記載しているSQLの書籍を見たことがありませんでしたし、このSQLの専門書もありません
しかし、今はMicrosoftのヘルプサイトが充実しているのである程度何とかなります
他の言語との微妙な違いではデータ登録時の点がいくつかあります
文字列を表す場合、多くのSQLでは’（シングルクォーテーション）を使用しますが、こちらでは”（ダブルクォーテーション）も利用できます
日付データ時には’（シングルクォーテーション）か#で挟むかのどちらも利用可能です
自分は#を使用します、これはVBAの仕様と一致するからです
ワイルドカードでも少し差があります
Excelは通常、任意の1文字を「?」、0以上文字を「*」で表現します
Microsoft Access SQLでは、任意の1文字を「_」（アンダースコア）、0以上文字を「%」で表現します
なお任意の1文字は「?」、0以上文字は「\*」も利用可能なのですが、これも混乱しました
Excel通常の「?」が使えるのに「*」が使えないのはなんでやねん、となりましたから
データ型に関しても独特です
本来SQLの型にString型は存在しません、CHARやTEXTが文字列型になります
しかし類義語という指定に含まれており、自動的に変換されておりVBAからString型を指定してもエラーは発生しません
ここがVBA使いとしては楽な点です、コードの可読性を下げることなく型指定を行うことが出来る訳ですから
本来は正式な型宣言を行うべきなのでしょうが、まあ自動的に変換してくれてるんだから有効利用しましょう
この辺り含め詳細に関してはヘルプサイトを参照してください
Microsoft Access SQL リファレンスはこちらから
最後に、SQLの勉強に関してですが
ここはそれほど複雑な構文を作成する必要はありませんので、基本の書籍で十分です
おすすめ書籍に記載しているSQLの絵本という書籍があるのですが、ExcelVBAからの利用の観点で行けば十分すぎる内容がありますのでお勧めします
ちなみにMicrosoft Access SQLについての記載は無いので、微妙な違いの点についてはリファレンスと合わせて確認を進めてください