Excel(VBA)を使用して、SQL Serverのストプロ(引数あり)を実行する

この記事では
 ・Excel(VBA)からSQL Serverの
 ・ストプロ(引数あり)を実行する方法
を記載します!

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

PR

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

VBAから
 ・引数として文字列を渡して
 ・ストプロ「SampleProcedure」を実行(引数あり)
します。

PR

ストプロを作成

ここでは例として、
 ・引数として文字列をもらって
 ・テーブル「tableA」へ
 ・レコードをINSERTするストプロ「SampleProcedure」
を作成します。

テーブル「tableA」
テーブル「tableA」
--DB名を設定
USE [sampleDB]
GO

CREATE OR ALTER PROCEDURE SampleProcedure
	--引数
	@message VARCHAR(50)
AS
	--tableAへレコードをINSERT
	INSERT INTO tableA VALUES(@message,GETDATE())
	RETURN
GO
作成したストプロ「SampleProcedure」
作成したストプロ「SampleProcedure」
PR

VBAを作成

ここでは例として、
 ・上記で作成したストプロ「SampleProcedure」を実行
します。

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 STORED_PROC As String = "SampleProcedure"
    
    Dim con As Object
    Dim conStr As String
    Dim command As Object
    Dim commandParameter 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 execStoproError
    
    Set command = CreateObject("ADODB.Command")
    
    '===============================
    'ストプロのパラメーターを設定
    '===============================
    Set commandParameter = command.CreateParameter()
    
    With commandParameter
        '引数名
        .Name = "message"
        '型を指定
        '┗VARCHAR:200
        '┗DECIMAL:14
        '┗DOUBLE :5
        .Type = 200
        '桁数
        .Size = 50
        'パラメータの方向
        '┗入力:1
        '┗出力:2
        .Direction = 1
        '値
        .Value = "VBAからストプロ実行"
    End With
    
    command.Parameters.Append commandParameter
    
    '===============================
    'ストプロを実行
    '===============================
    With command
        .ActiveConnection = con
        'コマンドの種類を指定
        '┗SQL   :1
        '┗ストプロ:4
        .CommandType = 4
        .CommandText = STORED_PROC
        .Execute
    End With

    MsgBox "ストプロを実行しました!"
    
    '===============================
    '後片付け
    '===============================
    con.Close
    Set commandParameter = Nothing
    Set command = 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
    
execStoproError:
    '===============================
    '後片付け
    '===============================
    con.Close
    Set commandParameter = Nothing
    Set command = Nothing
    Set con = Nothing

    errorMessage = "ストプロの実行でエラーが発生しました。" & vbCrLf & vbCrLf & _
            "●エラー番号" & vbCrLf & Err.Number & vbCrLf & vbCrLf & _
            "●エラー内容" & vbCrLf & Err.Description & vbCrLf
    MsgBox errorMessage, vbCritical
    Exit Sub

End Sub

以下を定数として設定しています(8~14行目)。
・接続情報
・実行するストプロ名
※必要に応じて環境に合うよう変更してください。

引数を作成し、ストプロへ渡す引数として追加しています(44~64行目)。
※必要に応じて実行するストプロに合うよう変更してください。

ストプロを実行しています(69~77行目)。

PR

結果

Excel(VBA)を使用して、SQL Serverのストプロ(引数あり)を実行できました。

VBAから出力させている正常終了メッセージ
VBAから出力させている正常終了メッセージ
テーブル「tableA」にINSERTされたレコード
テーブル「tableA」にINSERTされたレコード
PR

参考①

上記のVBAコードで使用している以下の詳細については、公式サイトをご確認ください。

●CreateObject(“ADODB.Connection”)


●CreateObject(“ADODB.Command”)


●CreateParameterメソッド

PR

参考②

Excel(VBA)を使用して、SQL ServerへSQLを実行することもできます。

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

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