【VBA×SQL】コード紹介Serverに接続テーブルの情報を取得

VBAでSQLServerの情報を取得する

シリーズの最終回です。VBAのコードを書いていきます。

これでSQLServerと情報のやり取りをする事ができる様になります。

少し難しいのですが難しい中でも簡単に理解できるように工夫しています。

よって配列など中級者向けのコードは使わない様にしています。

EnjoyExcel
EnjoyExcel

できるだけ分かり易くコードを用意しました。

サブルーチンを沢山使って余計なコードを書かない様にしています。

ここだけ少し難しいかもしれません。

同じようなコードを書かない為のアプローチもしています。

今回問い合わせいただいた初心者様のご要望も含めてコードを用意しました。

是非コードをご覧ください。

前回記事

データベース内にテーブルを作成しました。

テーブルを作る際には色々なルールがあります。

テーブル内に格納するデータはルールに沿って整理、整列している事が分かりました。

ここまで作業していただいた方はお分かりかと思います。

環境構築は大変だと思いませんか?

VBA以外の言語はコードを書く前に開発環境を整える必要があります。

VBAはエディタとしてVBEをもっています。オブジェクトブラウザーも持っています。

実行環境としてワークシートもあります。

よって環境を整える必要はほぼありません。Excelは優秀なAppです。

では優秀なAppであるExcelが持ってるVBAを使ってRDBMSにアクセスしてみましょう。

おすすめ書籍

SQLについてこの本ではSQLiteというAppを紹介しています。

MicrosoftのAppではないのですがSQL文についての取り掛かりはこの本で勉強しました。

\おすすめの書籍です/

環境を整理する

シリーズ内の3番目の記事で紹介した画像です。

これまでの記事の中で自分のPC内にサーバー、データベース、テーブルを用意してきました。

今回は画像右側のExcelと画像中央のテーブルとのやり取りについてコードを紹介していきます。

開発環境の整理

仕事の流れ

先に仕事の流れを書いておきます。

これでコードを読んだときにある程度イメージが持てる様になります。

仕事の流れ
  • 1
    下準備

    各プロシージャ共通で使う変数を宣言する

  • 2
    SQL文を用意する

    SQL文を変数に格納

  • 3
    DBに接続する

    SQLserverに接続します

  • 4
    テーブルからレコードセットに情報を取り出す

    SQL文の情報をもとにテーブルから情報を取り出しレコードセットに格納

  • 5
    レコードセットの情報を取り回す

    レコードセットの情報を照会、追加、編集、削除する

  • 6
    後片付け
    • ADOのレコードセットやコネクションを開放
    • メッセージボックスでお知らせ

レコードセット

1つ聞き慣れないキーワードが出てきます。レコードセットです。

あくまでイメージですがエクセルとテーブルの間にいる中間テーブルだと思ってください。

事例と画像で説明

SQL文にしたがってレコードを取り出す際にレコードが1つで済むケースばかりではないです。

例えばSQL文を使って3つのレコードを取り出してきたと想定します。

この時レコードセット内のレコードは当たり前ですが3つです。

この3つのレコードの情報を一気にエクセルに書き出すのではなくレコード単位で情報を取り出します。

情報を取り出す方法としてはレコードセットをループしながら1レコードずつ情報を取り出す事になります。

画像をご覧いただくとイメージがつかめます。

レコードセットとは

テーブルから何らかの条件をもとに取り出された情報はレコードセットに格納されます。

その後順番にアウトプットされるという建付けです。

アウトプットの際はセルでも良いですしユーザーフォームなどのオブジェクトに値を渡す事も可能です。

画像ではレコード単位でエクセルに情報がアウトプットされています。

ここは「レコード(赤)のカラム〇〇」だけという様にある程度自由に情報を取り扱う事も出来ます。

コード

コードは標準モジュールに全てコピペして使ってください。

多少変えるだけでそのまま使える仕様です。

コード内でダブルクリックすると情報が選択できます。

ユーザーフォームで・・・というのも考えましたがやめました。

標準モジュールに貼り付けて一部を加工するとすぐに使える方が良いと考えこの仕様にしました。

もちろんSQLServerの仕様が整ってないといけません。

これまでの記事を読んで環境構築を済ませてから以下コードを使ってみてください。

参照設定

本シリーズ初回でも紹介していますが再度お知らせしておきます。

まずはVBE上部の「ツール」タブから「参照設定」を選択。

MicrosoftActivexDataObjects2.8Libraryにレ点を付けましょう。

これでADOが使える様になります。

ADOについて知りたいかたはこちらをご覧ください。

データを取り出す

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文の書き方はここでは紹介しません。

ただし全く分からない人も居ますのでできるだけ簡単に説明します。

テーブルとカラムについて分からない方はこちらの記事をご覧ください。

SELECT *

SELECTの後に対象の列を選択します。今回は*(アスタリスク)なので全ての列となります。

FROM dbo.kojin

FROMの後はテーブルを指定します。dbo.kojinというテーブルが対象のテーブルになります。

WHERE id = 1001

WHEREの後は抽出条件です。id1001のレコードを抽出という指示になります。

コードをまとめて読む

dbo.kojinというテーブルからidが1001のレコードにあるすべてのカラム」となります。

基本は1本のコードでSQL文を書くのですが変数を使って区切る事で分かり易く見やすくしています。

3_DBに接続する

別プロシージャにアクセスしてデータベースに接続していきます。

プロシージャ:DB接続

VBEのオブジェクトブラウザーを使って説明します。(VBEでF11キーを押下)

まずはCreateObject関数を使います。

引数にADODB.Connectionを指定する事でConnectionクラスをインスタンス化します。

インスタンス化されたConnectionクラスをオブジェクト変数cnにセットします。

これで変数cnADODBConnectionクラスとして使用できます。

最後にオブジェクト変数cnConnectionクラス)のOpenメソッドでDBにアクセスします。

DB接続

オブジェクト変数について良く分からない人はこちらの記事をご覧ください。

Openメソッドの引数はConnectionString(プロバイダーやサーバー名)ユーザーID、PWを設定します。

ProviderSQLOLEDB.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人で使う時は処理が競合しないのでデータ衝突を考えなくてよいです。

複数人でデータベースを運用する際は以下の様に一定のルールを設ける事が必要です。

CursorTypeLockTypeが少しややこしいので今回の仕様だけ説明します。

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
'*****************************************************

コードが終了するとセルに値が用意されたことが確認できます。

取り出すの結果

データ追加

データを取り出す」で紹介したコードの下に貼り付けて使ってください。

そうする事で共通の変数、プロシージャを使う事ができます。

ここではデータを追加します。id1004maeさんをテーブルに追加しています。

'*****************************************************
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
'*****************************************************

データベースにid1004maeさんが追加されました。

追加の結果

データの編集

データを取り出す」で紹介したコードの下に貼り付けて使ってください。

そうする事で共通の変数、プロシージャを使う事ができます。

ここではデータを編集します。id1004maeさんを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
'*****************************************************

id1004のmaeさんがmaedaさんに編集されました。

編集の結果

データの削除

データを取り出す」で紹介したコードの下に貼り付けて使ってください。

そうする事で共通の変数、プロシージャを使う事ができます。

最後はデータの削除です。id1004maedaさんの情報を削除します。

本来は情報は削除せず削除フラグを立てる事で情報を残す(読まない)というのが正しい使い方です。

今回は「削除ができる」という事を見せる為にコードを用意しています。

'*****************************************************
Private Sub 削除()

    SQL = "SELECT * "
    SQL = SQL & "FROM dbo.kojin "
    SQL = SQL & "WHERE id = 1004 "

Call DB接続
Call レコードセット

    rs.Delete

Call オブジェクトの解放
    w = "削除"
Call お知らせ

End Sub
'*****************************************************

id1004maedaさんの情報が削除されました。

削除の結果

まとめ

使い方に慣れてきたらサーバーをネットワーク内に持って行く方法を調べてDBに接続してみてください。

SQL文を勉強して使いこなせるようになれば個人でデータを蓄える環境を構築できる様になります。

今回はフロントエンドがExcelですがWeb環境等他の環境になることがあるかもしれません。

そんな時でも今回勉強した内容はバックエンドの環境構築の知識として活かされます。

これでシリーズは終了です。お疲れ様でした。

VBA×SQLお疲れ様でした
タイトルとURLをコピーしました