【ACCESS/VBA】ストアドプロシージャ実行サンプル

①ストアド内のSELECT結果を取得する場合

Private Sub ExecSP()
    Dim cmd As New ADODB.Command
    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rtn As Integer
    m_cn = New ADODB.Connection
    conn.ConnectionString = "DSN=TEST_ODBC;Uid=USER1;Pwd=userpass1;Database=TEST_DB"
    conn.Open
    
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    
    With cmd
        .CommandText = "usp_sp1"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        .Parameters(1).Value = 100 ' ストアド引数1つ目
        .Parameters(2).Value = 200 ' ストアド引数2つ目
    End With
    
    ' ストアドプロシージャ内で最後に実行されたSQLの結果を取得
    Set rst = cmd.Execute
    ' ストアドプロシージャのReturn値を取得
    rtn = cmd.Parameters(0).Value
    
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    
 End Sub

②ストアド内のSELECT結果を取得しない場合

Private Sub ExecSP()
    Dim cmd As New ADODB.Command
    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rtn As Integer
    m_cn = New ADODB.Connection
    conn.ConnectionString = "DSN=TEST_ODBC;Uid=USER1;Pwd=userpass1;Database=TEST_DB"
    conn.Open
    
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    
    With cmd
        .CommandText = "usp_sp1"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        .Parameters(1).Value = 100 ' ストアド引数1つ目
        .Parameters(2).Value = 200 ' ストアド引数2つ目
        .Execute
    End With
    
    ' ストアドプロシージャのReturn値を取得
    rtn = cmd.Parameters(0).Value
    
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    
 End Sub