WinスクールにはVBAの講座が5つ用意されています
VBAでSQLServerからデータを取得する
シリーズの最終回です。VBAのコードを書いていきます。
これでSQLServerと情報のやり取りをする事ができる様になります。
少し難しいのですが難しい中でも簡単に理解できるように工夫しています。
よって配列など中級者向けのコードは使わない様にしています。
同じようなコードを書かない為のアプローチもしています。
今回問い合わせいただいた初心者様のご要望も含めてコードを用意しました。
前回記事
データベース内にテーブルを作成しました。テーブルを作る際には色々なルールがあります。
テーブル内に格納するデータはルールに沿って整理、整列している事が分かりました。
ここまで作業していただいた方はお分かりですよね。環境構築は大変だと思いませんか？
VBA以外の言語はコードを書く前に開発環境を整える必要があります。
VBAはエディタとしてVBEをもっています。オブジェクトブラウザーも持っています。
実行環境としてワークシートもあります。
よって環境を整える必要はほぼありません。Excelは優秀なAppです。
では優秀なAppであるExcelが持ってるVBAを使ってRDBMSにアクセスしてみましょう。
おすすめ書籍
この本ではSQLiteというAppを紹介しています。
MicrosoftのAppではないのですがSQL文についての取り掛かりはこの本で勉強しました。
関連記事
本記事は計5回のシリーズ記事です。主にVBA×Microsoft SQL Server との環境構築について解説しています。
SQL文自体の書き方には言及していません。そこはこちらの記事でカバーさせてください。
SQL文の自動生成が可能です。加えてVBAの自動生成機能もあります。
SQL文を作ってもらえるGPTExcelとは？無料プランで使い方や日本語対応可否を調査
シリーズ初回の記事はこちら
この記事はVBAの中でSQLの使い方を紹介するシリーズの5番目の記事です。
シリーズ初回の記事はこちらです。通して見ていただくとより理解が進みます。
VBA×SQL-1【VBA×SQL】メリットや使い方、環境構築の方法を画像で解説
開発環境を整理（ExcelVBAを使ってServerからデータを取得）
シリーズ内の3番目の記事で紹介した画像です。
これまでの記事の中で自分のPC内にサーバー、データベース、テーブルを用意してきました。
今回は画像右側のExcelと画像中央のテーブルとのやり取りについてコードを紹介していきます。
ExcelとSQLServer内のテーブルとの間でデータのやり取りを行う為にExcelVBAを使うという建付けです。
仕事の流れ
先に仕事の流れを書いておきます。
これでコードを読んだときにある程度イメージが持てる様になります。
ログイン画面でのWindows認証、パスワード入力などの操作は別記事で紹介しております。
- 1下準備
各プロシージャ共通で使う変数を宣言する
- 2SQL文を用意する
VBAの中でSQL文を生成し変数に格納します
- 3DBに接続する
VBAを使って「Microsoft SQL server」に接続します
- 4テーブルからレコードセットに情報を取り出す
SQL文の情報をもとにテーブルから情報を取り出しレコードセットに格納
- 5レコードセットの情報を取り回す
レコードセットの情報を照会、追加、編集、削除する
- 6後片付け
- ADOのレコードセットやコネクションを開放
- メッセージボックスでお知らせ
レコードセット
1つ聞き慣れないキーワードが出てきます。レコードセット。Accessを使える方なら御存知かもしれません。
あくまでイメージですがエクセルとテーブルの間にいる中間テーブルだと思ってください。
事例と画像で説明
SQL文にしたがってレコードを取り出す際にレコードが1つで済むケースばかりではないです。
例えばサンプルとしてSQL文を使って3つのレコードを取り出してきたと想定します。
この時レコードセット内に生成されたレコードは当たり前ですが3つです。
この3つのレコードの情報を一気にエクセルに書き出すのではなくレコード単位で情報を出力します。
情報を取り出す方法としてはレコードセットをループしながら先頭から1レコードずつ情報を取り出します。
画像をご覧いただくとイメージがつかめます。
テーブルから何らかの条件をもとに取り出された情報はレコードセットに格納されます。
その後順番にアウトプットされるという建付けです。
アウトプットの際はセルでも良いですしユーザーフォームなどのオブジェクトに値を渡す事も可能です。
画像ではレコード単位でエクセルに情報がアウトプットされています。
ここは「レコード（赤）のカラム〇〇」だけという様にある程度自由に情報を取り扱う事も出来ます。
コード
コードは標準モジュールに全てコピペして使ってください。
多少変えるだけでそのまま使える仕様です。
ユーザーフォームで・・・というのも考えましたがやめました。
標準モジュールに貼り付けて一部を加工するとすぐに使える方が良いと考えこの仕様にしました。
もちろんSQL Serverの仕様が整ってないといけません。
これまでの記事を読んで環境構築を済ませてから以下コードを使ってみてください。
参照設定
本シリーズ初回でも紹介していますが再度お知らせしておきます。
まずはVBE上部の「ツール」タブから「参照設定」を選択。
MicrosoftActivexDataObjects2.8Libraryにレ点（チェック）を付けましょう。
これでADOが使える様になります。ADOについて知りたいかたはこちらをご覧ください。
なにが出来るのか？【VBA×SQL】メリットや使い方、環境構築の方法を画像で解説
データを取り出す
35～62行目の「取り出す」という名前のプロシージャがメインです。
「取り出す」プロシージャからコードを実行してください。
実行してから62行目に到達する前に4つのプロシージャを経由します。
- DB接続
- レコードセット
- オブジェクトの解放
- お知らせ
最終的にはワークシートのセルA1にSQL文で指示したレコードのid、セルB1にnameが表示されます。
前段で紹介した仕事の流れに沿って解説していきます。
'用意されたプロシージャは5つです
'*****************************************************
'1_下準備
Public strcn As String
Public SQL As String
Public cn As ADODB.Connection
Public rs As ADODB.Recordset
Public w As String
'*****************************************************
Public Sub DB接続()
Set cn = CreateObject("adodb.connection")
strcn = "provider=SQLOLEDB.1" & _
";Persist Security Info=False;Data Source=DESKTOP-OM7N1ED\SQLEXPRESS" & _
";Initial Catalog=testDB" & _
";password=******" & _
";persist security info=False" & _
";user id=sa;"
cn.Open strcn
End Sub
'*****************************************************
Sub レコードセット()
Set rs = New ADODB.Recordset
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic
End Sub
'*****************************************************
Sub オブジェクトの解放()
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
'*****************************************************
Sub お知らせ()
MsgBox "データを" & w & "しました", vbInformation, "報告"
End Sub
'*****************************************************
Private Sub 取り出す()
'2_SQL文を用意する
SQL = "SELECT * "
SQL = SQL & "FROM dbo.kojin "
SQL = SQL & "WHERE id = 1001 "
'3_DBに接続する
Call DB接続
'4_テーブルからレコードセットに情報を取り出す
Call レコードセット
'5_レコードセットの情報を取り回す
Dim i As Long: i = 1
Do Until rs.EOF
Cells(i, 1) = rs!ID
Cells(i, 2) = rs!Name
i = i + 1
rs.MoveNext
Loop
'6_後片付け
Call オブジェクトの解放
w = "取り出"
Call お知らせ
End Sub
'*****************************************************1_下準備
各種変数を用意します。最終的には取り出し、追加、編集、削除のプロシージャで変数を使いたいです。
よってプロシージャの中ではなくモジュールレベルの変数として宣言します。
これでコードがプロシージャ間を移動する際に引数を持って移動しなくても良い仕様になっています。
加えてモジュールレベルの変数にまとめる事でローカルウインドウで引数を参照しやすい仕様にしています。
初心者の方と会話すると「ローカルウインドウとは？」という方が多いです。
ローカルウインドウは関数からの戻り値や変数にどんな値が入っているのかを確認できる場所です。
コード作成やデバッグ作業には欠かせない要素ですので必ずVBEに表示させる様にしておきましょう。
（ローカルウインドウはVBEで表示タブをクリックしてください。タブ内中央辺りに表示されているはずです。）
2_SQL文を用意する
変数SQLにSQL文を格納します。SQL文自体の書き方はここでは紹介しません。
参考となりますがSQL文を生成してくれるAIツールを紹介しています。以下リンク先の記事をご確認ください。
おすすめVBA×AIでコードを作成｜GPTExcelが無料で全機能を開放
SQL文について全く分からない人も居ますので記事内でもできるだけ簡単に説明します。
前提知識として「テーブル」と「カラム」だけ知っておいてください。（面倒ですがこちらの記事をご覧ください）
テーブルとカラム【VBA×SQL】データベース内にテーブルを簡単に作成する方法
SELECT *
SELECTの後に対象の列を選択します。今回は*(アスタリスク)なので全ての列となります。
FROM dbo.kojin
FROMの後はテーブルを指定します。dbo.kojinというテーブルが対象のテーブルになります。
WHERE id = 1001
WHEREの後は抽出条件です。idが1001のレコードを抽出という指示になります。
コードをまとめて読む
「dbo.kojinというテーブルからidが1001のレコードにあるすべてのカラム」となります。
基本は1本のコードでSQL文を書くのですが変数を使って区切る事で分かり易く見やすくしています。
3_DBに接続する
別プロシージャにアクセスしてデータベースに接続していきます。
プロシージャ：DB接続
VBEのオブジェクトブラウザーを使って説明します。（VBEでF2キーを押下）
まずはCreateObject関数を使います。
引数にADODB.Connectionを指定する事でConnectionクラスをインスタンス化します。
インスタンス化されたConnectionクラスをオブジェクト変数cnにセットします。
これで変数cnをADODBのConnectionクラスとして使用できます。
最後にオブジェクト変数cn（Connectionクラス）のOpenメソッドでDBにアクセスします。
オブジェクト変数について良く分からない人はこちらの記事をご覧ください。
オブジェクト変数についてVBAで転記｜ブック間で文字列をコピペするマクロの作り方を教えます
Openメソッドの引数はConnectionString（プロバイダーやサーバー名）ユーザーID、PWを設定します。
ProviderのSQLOLEDB.1は定数です。接続するRDBMSによって値が変わります。
'*****************************************************
Public Sub DB接続()
Set cn = CreateObject("adodb.connection")
strcn = "provider=SQLOLEDB.1" & _
";Persist Security Info=False;Data Source=DESKTOP-OM7N1ED\SQLEXPRESS" & _
";Initial Catalog=testDB" & _
";password=********" & _
";persist security info=False" & _
";user id=sa;"
cn.Open strcn
End Sub
'*****************************************************Data Source、InitialCatalog、Password、user idをご自身の情報に置き換えてください。
4_テーブルからレコードセットに情報を取り出す
続いてテーブルからレコードセットに情報を取り出します。
レコードセットというプロシージャに担当してもらいます。
プロシージャ：レコードセット
まずはレコードセットのインスタンス化です。
オブジェクト変数のrsにADODBのRecordsetクラス（オブジェクト）をセットします。
その後オブジェクト変数rsの（Recordsetクラス）のOpenメソッドを使います。
引数は4つです。（あと1つありますが省略）
SourceでSQL文を用意、ConnectionはDB接続で用意したものを使います。
CursorTypeとLockTypeを指定する
簡単に言うとレコードセットを取得する際のルールを決めています。
1人で使う時は処理が競合しないのでデータ衝突を考えなくてよいです。
複数人でデータベースを運用する際は以下の様に一定のルールを設ける事が必要です。
CursorTypeとLockTypeが少しややこしいので今回の仕様だけ説明します。
CursorType
Cursor Typeは取得したレコードセットをどうやって選択していくのかを決めています。
今回使用しているのはadOpenKeysetになります。
レコードセット内をを前方・後方に移動できて他からデータに変更があった場合情報が反映されます。
ただしデータ追加（Update）だけには追従出来ないという仕様です。
LockType
次にLockTypeです。今回使用しているのはadLockOptimisticです。
簡単に言うとUpdateの時だけロックをかけて自身を優先させます。
2つをまとめると以下の様になります。
データ追加時は自身を優先させ他の作業はデータを共有しながらレコードセットを使うという仕様です。
複数人で1つのDBを使う時は紹介した仕様で使っていただくことをおすすめします。
'*****************************************************
Sub レコードセット()
Set rs = New ADODB.Recordset
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic
End Sub
'*****************************************************SQL文を変数SQLに格納しているので非常にシンプルに書けています。
5_レコードセットの情報を取り出す
47～56行目のコードです。
Dim i As Long: i = 1
Do Until rs.EOF
Cells(i, 1) = rs!ID
Cells(i, 2) = rs!Name
i = i + 1
rs.MoveNext
Loop今回はidでレコードを指定しているのでレコードセットには1つのレコードしか入りません。
しかし別の機会では複数のレコードを取得する事もありますのでその時の為にコードを書きました。
本当はWorksheetオブジェクトでシートを定義したうえでCellsプロパティを使う方が出力先がしっかりします。
コードの上の方でオブジェクト変数を使っているのでここでも使おうか迷いましたが止めました。
オブジェクト変数については3番の解説時にリンクを紹介しています。必要に応じてご覧ください。
ループの建付け
今回のDo Until ～ LoopではEOFがTrueになるまでループ処理が続くというコードになっています。
Do～Loopの繰り返し処理はWhileとUntilを用いる事で様々な処理が可能になります。
条件で使用されている要素の「EOF」はレコードセットの終端を確認するためのプロパティです。
Boolean型なのでTrueかFalseを返します。
EOFプロパティがTrueを返した時にループが終了となります。次にループの理屈です。
Do関連のループはFor～Nextステートメントと違い自力でループを回す必要があります。
これはループの最後に居るMoveNextメソッドが働いている為です。
MoveNextメソッドがレコードセットを1つずつ次に送っています。
セルへの入力は変数iを使ってレコード分の情報をセルに展開できる様にしています。
参考になりますがDo～Loopを用いたコードは以下記事でより詳しく解説しています。（動画付きです）
Do～Loopを動画で解説VBA｜Dir関数を使ってフォルダ内の複数ファイルを順番に処理する
コードに置き換えて考える
この思考をコードに置き換えて考えてみます。
レコードセットの終端までループ処理が続くので結果として全てのレコードをループする事になります。
6_後片付け
「オブジェクトの解放」と「お知らせ」というプロシージャで後片付けをします。
プロシージャ：オブジェクトの解放
取得したオブジェクトの解放を行うプロシージャになります。
'*****************************************************
Sub オブジェクトの解放()
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
'*****************************************************この後他の作業をする為にコードを書き続けるとオブジェクトを保有したまま他の作業をする事になります。
作業量にもよりますが使わないオブジェクトは適宜解放するべきです。
よってこのプロシージャを用意しました。
プロシージャ：お知らせ
単純に作業のお知らせです。
このあと追加、編集、削除のプロシージャでも使える様に変数を使って文字を表示させる様にしています。
'*****************************************************
Sub お知らせ()
MsgBox "データを" & w & "しました", vbInformation, "報告"
End Sub
'*****************************************************コードが終了するとセルに値が用意されたことが確認できます。
データ追加
「データを取り出す」で紹介したコードの下に貼り付けて使ってください。
そうする事で共通の変数、プロシージャを使う事ができます。
ここではデータを追加します。idが1004のmaeさんをテーブルに追加しています。
'*****************************************************
Private Sub 追加()
SQL = "SELECT * "
SQL = SQL & "FROM dbo.kojin "
Call DB接続
Call レコードセット
rs.AddNew
rs!ID = 1004
rs!Name = "mae"
rs.Update
Call オブジェクトの解放
w = "追加"
Call お知らせ
End Sub
'*****************************************************データベースにidが1004のmaeさんが追加されました。
データの編集
「データを取り出す」で紹介したコードの下に貼り付けて使ってください。
そうする事で共通の変数、プロシージャを使う事ができます。
ここではデータを編集します。idが1004のmaeさんをmaedaさんに編集しています。
「idに対して名前が変わることなんてあるのか・・・」という考えは今は忘れてください。
今回は「編集ができる」という事を見せる為にコードを用意しています。
'*****************************************************
Private Sub 編集()
SQL = "SELECT * "
SQL = SQL & "FROM dbo.kojin "
SQL = SQL & "WHERE id = 1004 "
Call DB接続
Call レコードセット
rs!Name = "maeda"
rs.Update
Call オブジェクトの解放
w = "修正"
Call お知らせ
End Sub
'*****************************************************idが1004のmaeさんがmaedaさんに編集されました。
データの削除
「データを取り出す」で紹介したコードの下に貼り付けて使ってください。
そうする事で共通の変数、プロシージャを使う事ができます。
最後はデータの削除です。idが1004のmaedaさんの情報を削除します。
本来は情報は削除せず削除フラグを立てる事で情報を残す（読まない）というのが正しい使い方です。
今回は「削除ができる」という事を見せる為にコードを用意しています。
'*****************************************************
Private Sub 削除()
SQL = "SELECT * "
SQL = SQL & "FROM dbo.kojin "
SQL = SQL & "WHERE id = 1004 "
Call DB接続
Call レコードセット
rs.Delete
Call オブジェクトの解放
w = "削除"
Call お知らせ
End Sub
'*****************************************************idが1004のmaedaさんの情報が削除されました。
まとめ
使い方に慣れてきたらサーバーをネットワーク内に持って行く方法を調べてDBに接続してみてください。
SQL文を勉強して使いこなせるようになれば個人でデータを蓄える環境を構築できる様になります。
今回はフロントエンドがExcelですがWeb環境等他の環境になることがあるかもしれません。
そんな時でも今回勉強した内容はバックエンドの環境構築の知識として活かされます。
これでシリーズは終了です。お疲れ様でした。
