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

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

SQL Server上のDB「sampleDB」にあるテーブル「EMPLOYEE」に対し、
　・INSERT文
　・SELECT文
を実行します。

サンプルプログラム作成

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 USER_NAME As String = "XXXXX"
    'パスワード
    Const PASSWORD As String = "XXXXX"
    '===============================
    'テーブルの列
    '===============================
    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へ接続
    '===============================
    '接続文字列の組み立て(Windows認証)
    conStr = "Provider=" & PROVIDER & ";" & _
              "Data Source='" & SERVER_NAME & "';" & _
              "Initial Catalog='" & DB_NAME & "';" & _
              "Integrated Security=SSPI;" & _
              "DataTypeCompatibility=80;"
              
    '接続文字列の組み立て(SQL Server認証)
'    conStr = "Provider=" & PROVIDER & ";" & _
'              "Data Source=" & SERVER_NAME & ";" & _
'              "Initial Catalog=" & DB_NAME & ";" & _
'              "User ID=" & USER_NAME & ";" & _
'              "Password=" & PASSWORD & ";" & _
'              "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

結果(正常終了の場合)

INSERT文

SELECT文

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

備考 実行するSQLについて

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

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

　　

参考

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

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