Excel(VBA)を使用して、SQL ServerへSQLを実行する

この記事では
 ・Excel(VBA)からSQL Serverへ
 ・SQLを実行する方法
を記載します!

今回は
 ・認証方法に「Windows 認証」
 ・ADOを使用
 ・SQL Server Native Client(OLE DBドライバ)を使用
します!

PR

サンプルプログラムの概要

以下のテーブル「EMPLOYEE」に対し、
 ・INSERT文
 ・SELECT文
を実行します。

テーブル「EMPLOYEE」
テーブル「EMPLOYEE」
PR

サンプルプログラム作成

接続文字列に「Integrated Security=SSPI;」を記載することで、
「Windows 認証」で接続できます。

接続文字列のプロバイダには、推奨されている「MSOLEDBSQL」を指定します。
※「SQLNCLI」は非推奨。

推奨されているプロバイダ
推奨されているプロバイダ

★「サーバー名」等の接続文字列(接続情報)について
上部に定数として纏めています。環境に合わせて修正してください。

★テーブルの列について
上部に定数として纏めています。実行するSELECT文に合わせて修正してください。

合わせてrecordset.Fieldsで取得レコードを参照している部分も修正してください。

Option Explicit

Sub execSqlToSqlServer()
    '===============================
    '接続文字列
    '===============================
    'プロバイダ
    Const PROVIDER As String = "MSOLEDBSQL"
    'サーバー名(サーバーのPC名\インスタンス名)
    Const SERVER_NAME As String = "localhost\SQLEXPRESS"
    'DB名
    Const DB_NAME As String = "sampleDB"
    '===============================
    'テーブルの列
    '===============================
    Const ID_COLUMN As Integer = 0
    Const NAME_COLUMN As Integer = 1
    Const SEX_COLUMN As Integer = 2
    Const SECTION_COLUMN As Integer = 3
    
    Dim con As Object
    Dim conStr As String
    Dim selectSql As String
    Dim recordset As Object
    Dim recordStr As String
    Dim insertSql As String
    Dim command As Object
    Dim errorMessage As String
    
    On Error GoTo conError
    
    '===============================
    'SQL Serverへ接続
    '===============================
    '接続文字列の組み立て
    conStr = "Provider=" & PROVIDER & ";" & _
              "Data Source='" & SERVER_NAME & "';" & _
              "Initial Catalog='" & DB_NAME & "';" & _
              "Integrated Security=SSPI;" & _
              "DataTypeCompatibility=80;"
    'オープン
    Set con = CreateObject("ADODB.Connection")
    con.Open conStr
    
    On Error GoTo insertError
    
    '===============================
    'INSERT文を実行
    '===============================
    Set command = CreateObject("ADODB.Command")

    '実行するINSERT文を設定
    insertSql = "INSERT EMPLOYEE (ID, NAME, SEX, SECTION) VALUES ('00003', '田中花子', '女', '総務課')"
    'INSERT文を実行
    With command
        .ActiveConnection = con
        .CommandText = insertSql
        .Execute
    End With

    MsgBox "INSERT文を実行しました!"
    
    On Error GoTo selectError

    '===============================
    'SELECT文を実行
    '===============================
    '実行するSELECT文を設定
    selectSql = "SELECT TOP(5) ID, NAME, SEX, SECTION FROM EMPLOYEE"
    
    'SELECT文を実行し結果を取得
    Set recordset = CreateObject("ADODB.Recordset")
    recordset.Open selectSql, con
    'SELECT文で取得したレコード分繰り返し
    Do Until recordset.EOF
        ' 取得した1レコードをカンマ区切りでString型の変数に設定
        recordStr = recordset.Fields(ID_COLUMN).Value & "," & _
                    recordset.Fields(NAME_COLUMN).Value & "," & _
                    recordset.Fields(SEX_COLUMN).Value & "," & _
                    recordset.Fields(SECTION_COLUMN).Value
        ' 取得した1レコードをMsgBoxで出力
        MsgBox recordStr
        ' 次のレコードへ進む
        recordset.MoveNext
    Loop
    
    '===============================
    '後片付け
    '===============================
    recordset.Close
    con.Close
    Set command = Nothing
    Set recordset = Nothing
    Set con = Nothing
    
    Exit Sub

conError:
    '===============================
    '後片付け
    '===============================
    Set con = Nothing

    errorMessage = "接続でエラーが発生しました。" & vbCrLf & vbCrLf & _
            "●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
            "●エラー内容" & vbCrLf & Err.Description & vbCrLf
    MsgBox errorMessage, vbCritical
    Exit Sub
    
insertError:
    '===============================
    '後片付け
    '===============================
    con.Close
    Set command = Nothing
    Set recordset = Nothing
    Set con = Nothing

    errorMessage = "INSERT文でエラーが発生しました。" & vbCrLf & vbCrLf & _
            "●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
            "●エラー内容" & vbCrLf & Err.Description & vbCrLf
    MsgBox errorMessage, vbCritical
    Exit Sub

selectError:
    '===============================
    '後片付け
    '===============================
    recordset.Close
    con.Close
    Set command = Nothing
    Set recordset = Nothing
    Set con = Nothing

    errorMessage = "SELECT文でエラーが発生しました。" & vbCrLf & vbCrLf & _
            "●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
            "●エラー内容" & vbCrLf & Err.Description & vbCrLf
    MsgBox errorMessage, vbCritical
    Exit Sub

End Sub
PR

結果(正常終了の場合)

INSERT文

実行結果(INSERT文)
実行結果(INSERT文)

SELECT文

実行結果①(SELECT文)
実行結果①(SELECT文)
実行結果②(SELECT文)
実行結果②(SELECT文)
実行結果③(SELECT文)
実行結果③(SELECT文)
PR

結果(Insert文の実行でエラーの場合)

実行結果(INSERT文でエラー)
実行結果(INSERT文でエラー)
PR

備考 実行するSQLについて

上記のサンプルプログラムは、SQLをVBA上に直接記載しています。

そうではなくて、SQLをシート上に記載してVBAから取得するようにしたほうが良いと思います。
SQLを変更することになった際に、プログラム(VBA)の修正が不要となるためです。
※プログラム(VBA)の単体テストが不要になる。

  

トランザクション管理をする手順を以下の記事に記載しました!
合わせてご確認ください!


PR

参考

Excel(VBA)を使用して、SQL Serverのストプロを実行することもできます。

詳細は以下の記事をご確認ください。

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