【VBA×SQL】メリットや使い方、環境構築の方法を画像で解説

この3つが上位を占めている事が多いです。質問も増えてきたので記事にする事にしました。

まずは基礎的な質問に答えていきます。続いて記事の真ん中あたりで開発環境の事例を提示します。

この記事を含めて5つの記事でVBAとSQLを一緒に使う為の方法を紹介していきます。

5番目の記事まで見ていただくとVBAの中でSQLを使う事ができる様になります。

SQLについてこの本ではSQLiteというアプリケーションを紹介しています。

今回私が紹介するアプリケーションではないのですがExcel×SQLの考え方はこの本で勉強しました。

SQLというのは言語の名前です。基本はプログラミング言語です。

さらに細分化するとデータベース言語という部類になります。以下引用文を参照ください。

SQLServerというのはMicrosoftが用意しているアプリケーションの名称です。

このアプリケーションは情報を蓄積する為のサーバーの様に使える機能を持っています。

加えてデータベースの機能も持ち合わせたアプリケーションです。

「SQLとSQLServerとの違いは？」という質問も多いです。これで各用語の住み分けが理解できることでしょう。

SQL・・・という名前なので同じくくりで考えてしまうのですが別モノです。ご注意ください。

カテゴリー分けするとSQLServerはRDBMSの中の1つです。RDBMSについてはこのあと説明します。

SQLSeverはSQLという言語とは別とは言ったものの少し矛盾が起きています。

「SQLはデータベースと情報をやり取りする為の言語」なのでデータベースがないと仕事になりません。

やはりなんらかの箱を用意しないと情報がストックできないです。開発環境をイメージできません。

最終的にはSQLを使う為にはデータベースやデータサーバーの事も一緒に考えることになります。

そういった需要に応えるために世の中にはいくつかのアプリケーションが用意されています。

SQLの定義のところで出てきたRDBMSというのがこのアプリケーションになります。

リレーショナルデータベースマネジメントシステムの略です。

リレーショナルデータベース（RDB)はExcelやAccessの様に表形式の構造でデータを格納する方式です。

各データは行や列の単位で作成、変更、抽出、削除が行われます。

用意されたデータ群をキーで繋いで1つのデータのように扱います。

RDBを「システムの情報の受け皿」として使う為に色々な機能を持たせているのがRDBMSです。

ここでSQLServerの登場です。SQLServerというAppが「システムの情報の受け皿」になります。

具体的に書くとVBAのコードの中にSQL文を書く事でRDBMSから情報を出し入れする事ができます。

このあと実際にどうやって環境を用意するのか説明していきます。

フロントエンドをExcelやAccess等のアプリケーションで準備します。

バックエンドにRDBMSを用意します。

フロントエンドとバックエンドの説明はこのあと用意した画像で確認してください。

互いのアプリケーション間をVBAとSQL文を使って情報のやり取りをするという建付けです。

よって実際はExcel×RDBMSでの運用という事になります。その中でSQLを使う事になります。

Excel×RDBMSで環境構築する際は以下のようなイメージで進めます。

今回はExcel×RDBMS（SQLServer）で環境を用意していきます。

用意した画像でイメージをもってください。

この段階ではボヤっとしていても良いのでシステムの全体像をイメージしてください。

そうしないと作業をしていても何をやっているのか分からなくなります。

おのずと知識の定着率が悪くなります。よって作業の前には全体像を把握する事が非常に重要です。

参考でAccessの画像も用意していますが今回はExcelで説明していきます。

繰り返しますが複雑に考えずイメージを持ってください。

フロントエンドはシステムの使用者（一般ユーザー）が見えるところ全般を指します。

ここではExcelを指します。

バックエンドとはシステムの使用者（一般ユーザー）が見えない所全般を指します。

ここではRDBMSや情報をやり取りする為の言語です。

VBAはフロントエンドにもバックエンドにも関わっているのできっちり分けることは難しいです。

使用者（一般ユーザー）から見えないという観点ではバックエンドと言っても良いでしょう。

まずはExcelでRDBMSから取り出したい情報を指定します。

〇の情報が欲しい等RDBMSへのリクエストを作成します。何かしらの方法で情報を入力する事になります。

画像ではユーザーフォームを使った説明をしていますがフォームが無くても大丈夫です。

使う人の人数や用途によってインプット側の情報をコントロールしたい時はフォームをご検討ください。

1のリクエストをもとにSQLを使ってSQL文を生成します。

その後SQL文をラッピングする様にVBAを用意します。

VBAを使ってSQLサーバー側にクエリ（問合せ）を投げかけて回答を用意してもらいます。

コードはシリーズ最後の記事で用意しています。

DBへの接続はActiveX Data Objectsを使っています。

ここではADOやCOMの説明は省略します。詳細が知りたい方は引用からリンクに飛んでください。

VBEのツールタブから参照設定を選択すると以下のようなダイアログボックスが表示されます。

Microsoft ActiveX Data Objects 2.8 Libraryにレ点を付けましょう。

リンク先のタイトルはライブラリとは関係無いように見えますが問題ないです。

記事内のライブラリを解説しているところにリンクを飛ばしています。

サーバーエンジン（SQLServerExpress）がサーバー（SQLManagementStudio）から情報のやり取りをします。

最終的にSQL文に対する回答となる情報を生成します。

用意された情報はレコードセットというかたちでフロント側に返ってきます。

フォームやセルに情報を展開（出力）します。

取り出した情報はレコード（1行）単位です。配列、条件分岐、繰り返し処理を使って情報を取り出します。

これが一連の流れになります。

上記環境を実現する為に実際にアプリケーションを用意しましょう。

しかし有償ですので「ちょっと使いたい」という方には不向きです。

よって今回は無料で使えて簡単に開発環境を用意できるアプリケーションを使っていきます。

MicrosoftからSQLServerという名前でRDBMSが用意されています。

冒頭の質問で出てきたSQLServerというのはこのアプリケーションを指していました。

SQLServerはサーバーとしての機能とエンジン、各種機能を繋ぐための言語を用意しています。

それぞれの要素がどんな作業をしているのかを簡単にイメージできるように画像を用意しました。

リクエスト列はリクエストというキーワードを使って各要素の作業をイメージしやすいようにしています。

たとえば言語であるSQLではリクエストを「作る」という様に読みます。

サーバーのエンジンです。ここが外部からの情報を一旦受けてサーバーとやり取りしています。

フロント側に情報を返す作業もエンジンが担当しています。

このエンジンには色々なエディション（版）がありエディションによって機能や費用に差があります。

ひとまず2つだけ画像に情報を用意しておきました。

リレーショナルデータベースの形式を用いたサーバーです。

サーバーという物体が居るわけではなくサーバーの様に情報を蓄えることができるアプリケーションです。

ただ情報を蓄えるだけではなく管理者が情報を管理しやすい様に様々な機能が用意されています。

サーバーの画面は次回以降の記事で用意していきます。画面を見るとよりイメージできるようになります。

ExcelにもセルがありRDBの形式で情報を持つことができます。

よってデータベースやサーバーの機能は擬似的にExcelで再現することも可能です。

RDBMSで情報を持つ方が大変です。環境構築やSQL文を勉強する必要がある為です。

デメリットの方が多く感じるのですがそれに勝るメリットがあります。

Excelは1,048,576行しか情報を持つことが出来ません。

今回使用するSQLServerはもっと沢山の情報を持つことができます。

加えて正規化でテーブルを分ける事で列方向に少なく情報を持つことができます。

正規化はネットで「正規化　データベース」などで検索していただくと解説が出てきます。

興味がある方は調べてみてください。

SQLServerでは動作も重たくなりません。

Excelで104万行まで情報を持つと起動したりソートするのも一苦労です。

関数を打ち込むと結果が返ってくるまで少し時間がかかります。

SQLserverはそんなことは起きません。スピーディーに情報にアクセスしデータを取得できます。

RDBMSは複数人で1つの情報にアクセスしてもデータが破損しないような作りになっています。

複数人でエクセルを使って作業をする事をイメージしてください。

ひとまず「共有」という機能で対応されることでしょう。

これで2～3人ぐらいで1つのデータを使うという環境であればデータ破損のリスクは低いです。

しかし数十人、数百人で1つのデータを使うとデータ破損のリスクが一気に高まります。

私はVBAでユーザーフォームを使う際はデータ更新がぶつからないような細工が出来ます。

よってデータ破損のリスクはあまり考えなくて良いのですが多くのユーザーさんはそうはいきません。

VBAでWebスクレイピングをしたり他の言語を使った事がある方は想像がつくでしょう。

環境構築がすごく大変です。加えて覚えることが沢山あるにも関わらず学習用の参考文献も少ないです。

アプリケーションをまたぐ仕事になりますのでエラー対策も大変です。

何でエラーが起きているのか把握して制御していくのが本当に大変です。

習得するまでには沢山のハードルがあります。挑戦した人の多くは環境構築で止めてしまう様です。

残念ですがコードを使うフェーズまで辿り着けていないという事ですね。

SQLの疑問点について解説していきました。

後半ではExcelとRDBMSを使った開発環境の紹介をしています。

お金はかけられないけど複数人で使える様なシステムを作ってみたいという人にはハマる内容です。

該当する方はチャレンジしてみてください。

以降の記事ではSQLSever関連のアプリケーションをダウウンロード、インストールしていきます。

最終的にはExcelからRDBMSの情報を呼び出すというところまで紹介していく予定です。