【VBA×SQL】Serverに接続テーブル名を指定しデータ取得（コード付き）

VBAでSQLServerからデータを取得する

前回記事

SQLに関する書籍

私はマクロ×SQLを勉強するにあたり以下書籍で勉強しました。

おすすめ書籍

こちらの書籍は電子書籍がおすすめです。書籍の半額以下で購入できます。

細かい設定につていも非常に詳しく書いてあるので重宝しています。

個人的には電子書籍の方が使いやすいです。電子書籍（Kindleストア）のリンクも用意しておきます。

関連記事

SQL文の自動生成が可能です。加えてVBAの自動生成機能もあります。

シリーズ初回の記事はこちら

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

開発環境を整理（ExcelVBAを使ってServerからデータを取得）

仕事の流れ

ログイン画面でのWindows認証、パスワード入力などの操作は別記事で紹介しております。

レコードセット

事例と画像で説明

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

コード

参照設定

データを取り出す

実行してから62行目に到達する前に4つのプロシージャを経由します。

'用意されたプロシージャは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_下準備

2_SQL文を用意する

おすすめVBA×AIでコードを作成｜GPTExcelが無料で全機能を開放

SELECT *

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

FROM dbo.kojin

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

WHERE id = 1001

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

コードをまとめて読む

3_DBに接続する

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

プロシージャ：DB接続

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

'*****************************************************
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_テーブルからレコードセットに情報を取り出す

プロシージャ：レコードセット

まずはレコードセットのインスタンス化です。

CursorTypeとLockTypeを指定する

CursorType

LockType

複数人で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

オブジェクト変数については3番の解説時にリンクを紹介しています。必要に応じてご覧ください。

ループの建付け

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さんが追加されました。

データの編集

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

'*****************************************************
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さんに編集されました。

データの削除

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

'*****************************************************
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さんの情報が削除されました。

まとめ

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

EnjoyExcel