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

この記事では
Excel(VBA)からSQL Serverへ接続するサンプルプログラム
を記載します!

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

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

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

サンプルプログラム作成

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

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

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

★テーブルの列について
上部に定数として纏めています。実行する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

結果(正常終了の場合)

INSERT文

SELECT文

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

備考 実行するSQLについて

上記のサンプルプログラムのようにVBA上に記載するのではなく、
シート上に記載してVBAから取得するようにしたほうが、
プログラム(VBA)を変更せずに実行するSQLを変更することができるため、
便利だと思います。

  

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

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