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

お知らせ記事には広告が含まれておりますがExcelのスキルUPに繋がる様コンテンツ自体は手を抜かずに作成しております

VBAとSQLは一緒に使うことができる

初心者様よりSQLに関する質問をいただきました。

  • SQLとは?
  • SQLServerとは?
  • ExcelVBA(マクロ)と一緒に使う事はできるのか?※書き方等含

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

EnjoyExcel
EnjoyExcel

SQLは言語名です。大きく分けるとプログラミング言語です。

ExcelやAccessと合わせて使う事ができますよ。

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

今回の記事の最後にはExcel×SQLで環境構築する際のメリットを紹介します。

VBA×SQLの使い方を紹介する為の記事群を用意しました

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

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

この本で勉強しました

Excelマクロ(VBA)とSQLを一緒に使うという事例を紹介した書籍はかなり少ないです。

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

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

\おすすめの書籍です/

SQLServerについて勉強する事ができる書籍です。

関連書籍が少なく困っていたのですがなんとかこの本を見つける事ができました。

大型書店かネットショップであれば購入できます。細かい設定も非常に詳しく書いてあるので重宝しています。

買って良かった本です

実際に購入してみたところ本当におすすめでしたので電子書籍(Kindleストア)のリンクも用意しました。

SQLServerはデュアルディスプレイの環境を構築し電子書籍を使う事で効率よく学習する事ができますよ。

質問の回答

冒頭で挙げた3つの質問に回答していきます。

SQLとは

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

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

関係データベース管理システム (RDBMS) において、データの操作や定義を行うためのデータベース言語問い合わせ言語)、ドメイン固有言語である。

プログラミングにおいてデータベースへのアクセスのために、他のプログラミング言語と併用される。

Wikipedia

RDBMSなど聞きなれないキーワードもありこれから勉強する人にとっては分かりにくいかもしれません。

最初は「データベースと情報をやり取りする為の言語」という理解で十分です。

SQLServerとは

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

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

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

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

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

  • SQL ・・・ 言語の名前
  • SQLServer ・・・ アプリケーションの名前

続いてもう少しだけSQLServerについて説明を加えます。

SQLServerをカテゴリー分けするとRDBMSにカテゴライズされます。RDBMSについては後ほど説明します。

SQLを使うには情報をストックする場所が必要

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

SQLはデータベースと情報をやり取りする為の言語」です。あくまでただの言語です。

よって言語だけ勉強しても実際はデータベースがないと仕事になりません。

やはりなんらかの箱を用意しないと情報がストックできないのでシステムを構築する事ができません。

これでは開発環境をイメージできないのでもう少し思考を広げてみましょう。

結論としてはSQLを使うにあたり言語と一緒にデータベースやデータサーバーの事も一緒に考える必要があります。

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

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

RDBMS

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

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

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

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

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

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

Excelと一緒に使う事はできるのか?

可能です。最終的には「Excel×RDBMSでシステム構築しVBA×SQLを使って情報をやり取りする」という建付けです。

具体的に書くとExcelとRDBMSの間で情報を出し入れする為にVBAのコードの中にSQL文を書く事になります。

以降で環境構築する方法をさらに具体例に説明していきます。

Excel×SQLでシステムを構築してみる

まずは結論です。

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

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

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

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

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

環境構築

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

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

作業の全体像をつかむ

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

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

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

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

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

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

フロントエンドとバックエンド

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

ここではExcelを指します。

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

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

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

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

仕事の流れを整理する

どうやって情報が動いていくのか簡単にまとめてみました。

1_○の情報がほしい

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

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

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

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

2_SQL文生成、VBAでラッピング

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

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

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

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

DBへの接続にはADOを使います

具体的な方法は別途紹介しますので簡単にやっている事を紹介しておきます。

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

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

ActiveX Data Objects (ADO)はデータソースアクセスを目的としたCOMオブジェクトのセットである。

Wikipedia

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

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

参照設定より選択できるライブラリという機能については別記事で解説しています。

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

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

ライブラリを活用することでDBへの経路を作る事が出来る様になります。

3_サーバーから○の情報を取り出す

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

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

4_○の情報を取り出す

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

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

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

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

アプリケーションを用意する

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

まずはRDBMSをどうするのか考えます。有名なものを1つ挙げてみますとやはりOracleさんです。

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

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

MicrosoftもRDMBSを用意しています

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

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

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

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

  • イメージ:記事中段で仕事の流れを説明した際の画像に合わせて画像を用意
  • 名称:各要素の名前
  • 役割:役割を表示
  • リクエスト:リクエストというキーワードを使って後に続く文字を用意

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

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

文字を取得出来る様に画像の内容を書き出しておきました。(必要に応じてコピーしてください)

番号名称役割“リクエスト”を使って説明
1SQL言語リクエストを「作る」
2SQLServerExpressエンジンリクエストを「回答」する
3SQLServerManagementStudioデータベースリクエストを「用意」する
画像の内容をテーブルに書き出しました(一部加工しています)

SQLについては冒頭で説明しましたので以降の2つについて順番に説明していきます。

SQLServerExpress

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

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

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

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

さらに詳しくしりたい方はMicrosoftさんの関連サイトのリンクを用意しておきます。

番号各エディション料金バックアップ商用利用その他
1Express無料手動可能メモリ容量、DB量が決まっている
2Standard有料自動可能メモリ容量はOSによる DB量は無制限
画像の内容をテーブルに書き出しました

文字を取得出来る様に画像の内容を書き出しておきました。(必要に応じてコピーしてください)

SQLServerManagementStudio

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

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

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

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

Excel×SQL(RDBMS)のメリット

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

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

既にイメージできると思いますがRDBMSで情報を持つ方が大変です。環境構築やSQL文を勉強する必要がある為です。

そうなるとRDBMSを使うのはデメリットの方が多い様に感じますがそれに勝るメリットがあります

情報を持つ箱のスケールが大きい

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

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

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

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

動作が軽い

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

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

加えて関数を打ち込むなどの演算をすると結果が返ってくるまで少し時間がかかります。

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

信頼性が高い

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

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

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

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

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

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

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

RDBMSを使っておけばデータ衝突の心配はほぼありません。利用人数が増えても心配なく利用できます。

デメリットもある

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

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

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

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

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

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

まとめ

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

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

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

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

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

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

参考:関連記事

本シリーズはこちらの記事とも連携しています。

マクロ勉強の道筋マクロは何から勉強するのか|学習をサポートするためのロードマップを作成

タイトルとURLをコピーしました