VBAでSQLServerの情報を取得する
シリーズの最終回です。VBAのコードを書いていきます。
これでSQLServerと情報のやり取りをする事ができる様になります。
少し難しいのですが難しい中でも簡単に理解できるように工夫しています。
よって配列など中級者向けのコードは使わない様にしています。
同じようなコードを書かない為のアプローチもしています。
今回問い合わせいただいた初心者様のご要望も含めてコードを用意しました。
是非コードをご覧ください。
前回記事
データベース内にテーブルを作成しました。
テーブルを作る際には色々なルールがあります。
テーブル内に格納するデータはルールに沿って整理、整列している事が分かりました。
ここまで作業していただいた方はお分かりかと思います。
環境構築は大変だと思いませんか？
VBA以外の言語はコードを書く前に開発環境を整える必要があります。
VBAはエディタとしてVBEをもっています。オブジェクトブラウザーも持っています。
実行環境としてワークシートもあります。
よって環境を整える必要はほぼありません。Excelは優秀なAppです。
では優秀なAppであるExcelが持ってるVBAを使ってRDBMSにアクセスしてみましょう。
おすすめ書籍
SQLについてこの本ではSQLiteというAppを紹介しています。
MicrosoftのAppではないのですがSQL文についての取り掛かりはこの本で勉強しました。
環境を整理する
シリーズ内の3番目の記事で紹介した画像です。
これまでの記事の中で自分のPC内にサーバー、データベース、テーブルを用意してきました。
今回は画像右側のExcelと画像中央のテーブルとのやり取りについてコードを紹介していきます。
仕事の流れ
先に仕事の流れを書いておきます。
これでコードを読んだときにある程度イメージが持てる様になります。
- 1下準備
各プロシージャ共通で使う変数を宣言する
- 2SQL文を用意する
SQL文を変数に格納
- 3DBに接続する
SQLserverに接続します
- 4テーブルからレコードセットに情報を取り出す
SQL文の情報をもとにテーブルから情報を取り出しレコードセットに格納
- 5レコードセットの情報を取り回す
レコードセットの情報を照会、追加、編集、削除する
- 6後片付け
- ADOのレコードセットやコネクションを開放
- メッセージボックスでお知らせ
レコードセット
1つ聞き慣れないキーワードが出てきます。レコードセットです。
あくまでイメージですがエクセルとテーブルの間にいる中間テーブルだと思ってください。
事例と画像で説明
SQL文にしたがってレコードを取り出す際にレコードが1つで済むケースばかりではないです。
例えばSQL文を使って3つのレコードを取り出してきたと想定します。
この時レコードセット内のレコードは当たり前ですが3つです。
この3つのレコードの情報を一気にエクセルに書き出すのではなくレコード単位で情報を取り出します。
情報を取り出す方法としてはレコードセットをループしながら1レコードずつ情報を取り出す事になります。
画像をご覧いただくとイメージがつかめます。
テーブルから何らかの条件をもとに取り出された情報はレコードセットに格納されます。
その後順番にアウトプットされるという建付けです。
アウトプットの際はセルでも良いですしユーザーフォームなどのオブジェクトに値を渡す事も可能です。
画像ではレコード単位でエクセルに情報がアウトプットされています。
ここは「レコード（赤）のカラム〇〇」だけという様にある程度自由に情報を取り扱う事も出来ます。
コード
コードは標準モジュールに全てコピペして使ってください。
多少変えるだけでそのまま使える仕様です。
コード内でダブルクリックすると情報が選択できます。
ユーザーフォームで・・・というのも考えましたがやめました。
標準モジュールに貼り付けて一部を加工するとすぐに使える方が良いと考えこの仕様にしました。
もちろんSQLServerの仕様が整ってないといけません。
これまでの記事を読んで環境構築を済ませてから以下コードを使ってみてください。
参照設定
本シリーズ初回でも紹介していますが再度お知らせしておきます。
まずはVBE上部の「ツール」タブから「参照設定」を選択。
MicrosoftActivexDataObjects2.8Libraryにレ点を付けましょう。
これでADOが使える様になります。
ADOについて知りたいかたはこちらをご覧ください。
なにが出来るのか？【VBA×SQL】メリットや使い方、環境構築の方法を画像で解説
データを取り出す
49～79行目の「取り出す」という名前のプロシージャがメインです。
「取り出す」プロシージャからコードを実行してください。
実行してから79行目に到達する前に4つのプロシージャを経由します。
- DB接続
- レコードセット
- オブジェクトの解放
- お知らせ
最終的にはワークシートのセルA1にSQL文で指示したレコードのid、セルB1にnameが表示されます。
前段で紹介した仕事の流れに沿って解説していきます。
Option Explicit
'*****************************************************
'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_下準備
各種変数を用意します。
最終的には取り出し、追加、編集、削除のプロシージャで変数を使いたいです。
よってプロシージャの中ではなくモジュールの変数として宣言します。
これでコードがプロシージャ間を移動する際に引数を持って移動しなくても良い仕様になっています。
加えてモジュールレベルの変数にまとめる事でローカルウインドウで引数を参照しやすい仕様にしています。
2_SQL文を用意する
変数SQLにSQL文を格納します。
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でF11キーを押下）
まずは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が少しややこしいので今回の仕様だけ説明します。
Cursor Typeは取得したレコードセットをどうやって選択していくのかを決めています。
今回使用しているのはadOpenKeysetになります。
レコードセット内をを前方・後方に移動できて他からデータに変更があった場合情報が反映されます。
ただしデータ追加（Update）だけには追従出来ないという仕様です。
次にLockTypeです。今回使用しているのはadLockOptimisticです。
簡単に言うとUpdateの時だけロックをかけて自身を優先させます。
2つをまとめると以下の様になります。
データ追加時は自身を優先させ他の作業はデータを共有しながらレコードセットを使うという仕様です。
複数人で1つのDBを使う時は紹介した仕様で使っていただくことをおすすめします。
'*****************************************************
Sub レコードセット()
Set rs = New ADODB.Recordset
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic
End Sub
'*****************************************************
SQL文を変数SQLに格納しているので非常にシンプルに書けています。
5_レコードセットの情報を取り出す
63～72行目のコードです。
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つのレコードしか入りません。
しかし別の機会では複数のレコードを取得する事もありますのでその時の為にコードを書きました。
ループの建付け
大枠ではEOFがTrueになるまでループ処理が続くというコードになっています。
EOFはレコードセットの終端を確認するためのプロパティです。
Boolean型なのでTrueかFalseを返します。
EOFプロパティがTrueを返した時にループが終了となります。
次にループの理屈です。
Do関連のループはFor～Nextステートメントと違い自力でループを回す必要があります。
これはループの最後に居るMoveNextメソッドが働いている為です。
MoveNextメソッドがレコードセットを1つずつ次に送っています。
セルへの入力は変数iを使ってレコード分の情報をセルに展開できる様にしています。
コードに置き換えて考える
この思考をコードに置き換えて考えてみます。
レコードセットの終端までループ処理が続くので結果として全てのレコードをループする事になります。
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環境等他の環境になることがあるかもしれません。
そんな時でも今回勉強した内容はバックエンドの環境構築の知識として活かされます。
これでシリーズは終了です。お疲れ様でした。
