【Excel・Access VBA】ADOでストアドを実行するサンプルプログラム

VBA
スポンサーリンク

今回はVBAからADO経由でデータベースサーバーのストアドプロシージャを実行する方法を紹介します。

尚、以前にVBAのADOを使用したデータベースの接続関連のクラスを紹介させていただきましたが、今回はそのクラスをベースに、ストアド実行用のメソッドを追加する方法で実装しています。

よって、以下の記事も併せてご参照ください。

【Excel・Access VBA】データベース接続用クラスで時短プログラミング
ExcelやAccessでデータベースに接続してデータ取得やデータ更新処理を実行する場合に、ADOを利用して実装すること...

 

サンプルプログラム

以下の項で、以前に紹介したデータベース接続用クラスに追加するメソッドのプログラムと、そのメソッドを呼び出すサンプルプログラムを記載します。
 

データベース接続クラスに追加するプログラム

当処理は、以前に紹介したデータベース接続用クラス(クラス名:DataBaseAccess)をそのまま使用していただいている場合は、以下のプログラムをそのクラスの処理の最後尾にでもペタッと貼り付けてもらうだけで良いです。
※Connectionは上記リンクで掲載しているクラスから取得してきていることが前提の処理で作られているので、もしそのクラスを使わずに当メソッドを使用する場合は、データベースとのConnectionを別途生成してください。

'引数のストアド名とパラメーターを使用して、ストアドを実行します。
'引数1:ストアド名
'引数2:パラメーター※2次元配列(パラメータ名,データ型,値) パラメーターは5つまで。
'戻り値:ストアドの戻り値をそのままVariantにセット
Public Function execute_usp(SpName As String, Param() As String) As Variant

    Dim cmd As New ADODB.Command
    Dim Param1 As New ADODB.Parameter
    Dim Param2 As New ADODB.Parameter
    Dim Param3 As New ADODB.Parameter
    Dim Param4 As New ADODB.Parameter
    Dim Param5 As New ADODB.Parameter
    Dim i As Integer
    
        
        Set cmd.ActiveConnection = mCon
        'コマンドタイプにストアドを指定します。
        cmd.CommandType = adCmdStoredProc
        'ストアド名をセットします。
        cmd.CommandText = SpName
        
        '戻り値のパラメーターを指定します。
        '※なぜか引数より先に戻り値の定義をしないとエラーになるので注意。
        cmd.Parameters.Append cmd.CreateParameter("Return", adInteger, adParamReturnValue)

        '引数のパラメーター配列を要素数分ループします。
        For i = LBound(Param, 1) To UBound(Param, 1)
        
            'パラメータが5以上(カウンタが4以上)の場合はループから抜けます。
            If i > 4 Then
                Exit For
            End If
            
            'パラメーターをセットします。
            Select Case i
                Case 0
                    Set Param1 = cmd.CreateParameter(Param(0, 0), Param(0, 1), adParamInput)
                    Param1.Value = Param(0, 2)
                    cmd.Parameters.Append Param1
                Case 1
                    Set Param2 = cmd.CreateParameter(Param(1, 0), Param(1, 1), adParamInput)
                    Param2.Value = Param(1, 2)
                    cmd.Parameters.Append Param2
                Case 2
                    Set Param3 = cmd.CreateParameter(Param(2, 0), Param(2, 1), adParamInput)
                    Param3.Value = Param(2, 2)
                    cmd.Parameters.Append Param3
                Case 3
                    Set Param4 = cmd.CreateParameter(Param(3, 0), Param(3, 1), adParamInput)
                    Param4.Value = Param(3, 2)
                    cmd.Parameters.Append Param4
                Case 4
                    Set Param5 = cmd.CreateParameter(Param(4, 0), Param(4, 1), adParamInput)
                    Param5.Value = Param(4, 2)
                    cmd.Parameters.Append Param5
            End Select
            
        Next i

        cmd.execute

        'ストアドの戻り値を当処理の戻り値にセットします。
        execute_usp = cmd.Parameters("Return")

        Set Param5 = Nothing
        Set Param4 = Nothing
        Set Param3 = Nothing
        Set Param2 = Nothing
        Set Param1 = Nothing

        Set cmd = Nothing

End Function

 

メソッドの仕様概要

  • メソッドの引数は「実行するストアド名」「ストアドに渡すパラメータ」。
  • パラメータは五つまで対応しそれ以上渡されても無視します。
  • パラメータは二次元配列とし、「パラメータ名」「データ型」「値」を格納します。
  • メソッドの戻り値にはストアドの戻り値をそのままセットします。

 

メソッド呼び出しサンプルプログラム

当項では呼び出し側のサンプルプログラムを記載します。

'当関数は正常終了時には空文字、異常終了時にはエラーメッセージを戻り値に格納します。
Public Function SampleFunc(Target1CD As Long, Target2CD As Long) As Variant

    Dim db As test_project.DataBaseAccess
    Dim strSpName As String
    Dim strPara(1, 2) As String
    Dim varReturn As Variant

    On Error GoTo ErrorProcess

        '■処理1 データベースの接続処理
        '----------------------------------------------------------------------------------------------
        Set db = test_project.DatabaseFactory.creater
        'サーバデータベースと接続します。
        db.connect

        '■処理2 サーバ側のストアドを実行します。
        '----------------------------------------------------------------------------------------------
        'ストアド名を指定します。
        strSpName = "dbo.sample_stored_procedure"
        
        'パラメーター1
        strPara(0, 0) = "Target1CD"   	'パラメーター名
        strPara(0, 1) = adBigInt        'データ型
        strPara(0, 2) = Target1CD	'値
        'パラメーター2
        strPara(1, 0) = "Target2CD"     'パラメーター名
        strPara(1, 1) = adBigInt        'データ型
        strPara(1, 2) = Target2CD  	'値
        '※上記パラメータのデータ型をダブルクォーテーションで囲むと
        ' CreateParameterメソッドの定数として扱われなくなるので注意。
        
        'ストアドを実行します。
        varReturn = db.execute_usp(strSpName, strPara)
        
        If varReturn = 1 Then
            SampleFunc = "ストアド実行処理でエラーが発生しました。処理を中止します。"
            Set db = Nothing
            Exit Function
        End If

        '■処理3 後処理
        '----------------------------------------------------------------------------------------------
        'データベースと切断します。
        db.disconnect

        '各オブジェクト変数を解放します。
        Set db = Nothing

        '正常終了の場合は空文字を返します。
        SampleFunc = ""
        
        Exit Function

ErrorProcess:
        SampleFunc = "ERROR: " & Err.Description 
        On Error Resume Next
        db.disconnect
        Set db = Nothing        
End Function

上記のサンプルプログラムでは、実行パラメータを二つ持っている「dbo.sample_stored_procedure」という名称のストアドを実行しています。
また、ストアドの戻り値が1の場合は異常とみなしています。

ストアドのパラメーター作る処理で指定する「データ型」は、ADOにおけるデータ型の定数を指定します。
データ型と定数の一覧は以下のリンク先で確認してください。
DataTypeEnum -Microsoft ADO プログラマリファレンス

 

最後に

今回作成したストアド実行用のメソッドは、どんなストアドでも呼べるように汎用性を持たせた実装をしたつもりですが、実行環境に合わせて作り替えてご使用ください。

それでは今回も読んでいただきありがとうございました。

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