【入門編】ADOでストアドプロシージャの戻り値と出力パラメータを受け取る実装 – Access VBA解析バイブル

SQL Serverとの「対話」を極める：ADO Commandオブジェクトでストアドプロシージャを制御する

こんにちは。技術の深淵へようこそ。

Access VBAで開発を進めていると、必ずと言っていいほど「壁」にぶつかる瞬間があります。それは、「Accessの中だけで完結する処理から、SQL Serverなどの強力な外部データベースへ、いかにして高度な命令を出し、その結果を正確に受け取るか」という問題です。

マクロの記録や、単純な`DoCmd.RunSQL`の段階を卒業し、プロフェッショナルなエンジニアへの階段を上ろうとしているあなた。今日、あなたが手にするのは、単なる「コードの書き方」ではありません。「サーバーとVBAの間で、いかにして確実な契約（インターフェース）を交わすか」という、アーキテクチャの本質です。

今回は、ADO（ActiveX Data Objects）を用いて、SQL Serverのストアドプロシージャから「戻り値」と「出力パラメータ」をスマートに取得する極限の実装手法を伝授します。

1. なぜDAOではなくADOなのか？ アーキテクチャの境界線

まず、混乱しやすい「DAO」と「ADO」の違いを整理しておきましょう。ここを理解していないと、コードが動いても「なぜ動いているのか」が分かりません。

エンジニアの視点：
Access内のテーブルをいじるならDAOで十分です。しかし、SQL Serverのストアドプロシージャを呼び出し、「処理結果のステータスコード」や「計算された新しい値」をVBA側に持ち帰りたいなら、ADOの`Command`オブジェクトを選択するのが正解です。

2. 実践：ストアドプロシージャを制御する「契約書」の作り方

ストアドプロシージャを呼び出す際、単に `connection.Execute “EXEC my_proc …”` と文字列で送るのは、初学者のやり方です。これでは、引数の型が違ったり、SQLインジェクションの脆弱性を生んだりするリスクがあります。

プロは `ADODB.Command` オブジェクト を使います。これは、VBAとSQL Serverの間で交わされる「厳格な契約書」です。

シナリオ

SQL Server側に、以下のようなストアドプロシージャがあると仮定します。

• プロシージャ名: `usp_UpdateInventory`
• 入力 (Input): `@ProductID` (int), `@ChangeQty` (int)
• 出力 (Output): `@NewStock` (int)
• 戻り値 (Return Value): 成功なら `0`、エラーなら `エラーコード`

極限の実装コード

このプロシージャを呼び出し、すべての値を回収するVBAコードを見てみましょう。

Option Compare Database
Option Explicit

''' <summary>
''' ストアドプロシージャを実行し、在庫更新の結果を取得する
''' </summary>
Public Sub ExecuteInventoryUpdate()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strConn As String
    
    ' --- 変数の定義 ---
    Dim inputProductID As Long:    inputProductID = 101      ' 入力パラメータ
    Dim inputQty As Long:          inputQty = -5             ' 入力パラメータ（在庫減）
    Dim outputNewStock As Long     ' 出力パラメータを受け取る変数
    Dim returnValue As Long      ' ストアドのRETURN値を受け取る変数

    ' 1. 接続文字列の設定 (環境に合わせて書き換えてください)
    strConn = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDBName;Integrated Security=SSPI;"

    On Error GoTo ErrorHandler

    ' 2. オブジェクトの生成
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command

    ' 3. 接続のオープン
    conn.Open strConn

    ' 4. Commandオブジェクトの設定
    With cmd
        .ActiveConnection = conn
        .CommandText = "usp_UpdateInventory" ' 実行するプロシージャ名
        .CommandType = adCmdStoredProc      ' ストアドプロシージャであることを明示
        
        ' --- パラメータの定義（ここが心臓部です） ---
        
        ' ① 戻り値 (Return Value) の定義
        ' ※必ず一番最初に定義するのが作法です
        .Parameters.Append .CreateParameter("@ReturnVal", adInteger, adParamReturnValue)

        ' ② 入力パラメータ (Input) の定義
        .Parameters.Append .CreateParameter("@ProductID", adInteger, adParamInput, , inputProductID)
        .Parameters.Append .CreateParameter("@ChangeQty", adInteger, adParamInput, , inputQty)

        ' ③ 出力パラメータ (Output) の定義
        ' ※出力パラメータの場合、サイズ（Size）が必要な場合があります（文字列など）
        .Parameters.Append .CreateParameter("@NewStock", adInteger, adParamOutput)

        ' 5. 実行
        .Execute
        
        ' 6. 結果の回収
        ' 実行後、Parametersコレクションから値を取り出します
        returnValue = .Parameters("@ReturnVal").Value
        outputNewStock = .Parameters("@NewStock").Value
        
        ' 結果の判定
        If returnValue = 0 Then
            Debug.Print "成功！ 新しい在庫数: " & outputNewStock
            MsgBox "在庫更新に成功しました。" & vbCrLf & "現在の在庫: " & outputNewStock, vbInformation
        Else
            Debug.Print "エラー発生。ステータスコード: " & returnValue
            MsgBox "サーバー側でエラーが発生しました。コード: " & returnValue, vbCritical
        End If
    End With

CleanUp:
    ' 7. 後始末（オブジェクトのライフサイクル管理）
    If Not cmd Is Nothing Then Set cmd = Nothing
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
        Set conn = Nothing
    End If
    Exit Sub

ErrorHandler:
    MsgBox "致命的なエラーが発生しました: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

3. ここが落とし穴！ 陥りやすい3つのエラー

コードを書いていて「なぜか動かない」となった時、チェックすべきポイントは決まっています。

① パラメータの「順序」は絶対

`Command.Parameters` コレクションに追加する順番は、SQL Server側で定義されている引数の順番と完全に一致していなければなりません。特に「戻り値（Return Value）」は、慣例として一番最初に定義します。

② データ型の不一致 (Type Mismatch)

VBAの `Long` は ADOの `adInteger` に対応しますが、SQL Serverの `bigint` を扱う場合は `adBigInt` を使う必要があります。ここがズレると、実行時に「型が一致しません」という、非常に冷たいエラーメッセージが返ってきます。

③ 出力パラメータの「サイズ」指定

上記の例では `adInteger`（固定長）だったので問題ありませんが、もし出力パラメータが `VARCHAR`（可変長文字列）だった場合、以下のようにサイズを指定しないと、値が切り捨てられるかエラーになります。

' 文字列の出力パラメータの場合（例: 50文字分確保）
.Parameters.Append .CreateParameter("@StatusMsg", adVarChar, adParamOutput, 50)

最後に：エンジニアとしてのマインドセット

ここまで読んでくれたあなたは、もう「ただ動くコード」を書く人ではありません。「データの整合性を守り、サーバーとの通信を制御する」という、より高い視点を持ったエンジニアへの一歩を踏み出しました。

`Command` オブジェクトを使う手間は、一見すると遠回りに見えるかもしれません。しかし、この「厳格さ」こそが、大規模なシステムにおいてバグを未然に防ぎ、保守性を高めるための最強の武器になります。

「ここをクリアすれば、Access VBAの基本はバッチリですよ」

一歩ずつ、確実に。コードの裏側にある仕組みを理解しながら、素晴らしいアプリケーションを作り上げてください。応援しています。