【実務・中級編】ADOのCommandオブジェクトでストアドプロシージャのパラメータを動的生成する – Access VBA解析バイブル

文字列結合の呪縛を解け：ADO Commandオブジェクトによる動的パラメータ注入の極意

多くの開発者が、ストアドプロシージャを呼び出す際に「手軽さ」という名の罠に足を踏み入れる。
SQL文を文字列として連結し、`connection.Execute “EXEC MyStoredProc ‘” & strVal & “‘”` と書き下ろす。その瞬間、彼らのコードは「脆弱な設計」へと堕ちる。

SQLインジェクションの脆弱性、データ型不一致によるランタイムエラー、そして何よりSQL Serverなどのサーバー側における実行計画（Execution Plan）のキャッシュ汚染。文字列結合による動的SQLは、パフォーマンスと堅牢性の両面において、プロフェッショナルが避けるべき「禁忌」だ。

本稿では、ADOの`Command`オブジェクトを完全に掌握し、パラメータの型やサイズを動的に制御することで、保守性と堅牢性を極限まで高めた「プロフェッショナル・アーキテクチャ」を伝授する。

1. アーキテクチャの境界線：DAOとADOを混同するな

Access開発において、まず理解すべきはDAO (Data Access Objects) と ADO (ActiveX Data Objects) の決定的な違いだ。

• DAO: Accessのエンジン（ACE/Jet）に最適化された、ローカルデータ操作のための「内向き」の技術。
• ADO: SQL ServerやOracleといった外部リレーショナルデータベース（RDBMS）との通信を主眼とした、「外向き」の技術。

ストアドプロシージャを扱うということは、処理の主導権がローカルのAccessから、サーバー側のRDBMSへと移ることを意味する。この時、DAOの思考プロセス（レコードセットを操作する）から、ADOの思考プロセス（プロトコルを介して命令を投げる）へと、脳を切り替えなければならない。

2. なぜ「型」と「サイズ」の動的生成が必要なのか

ストアドプロシージャのパラメータを定義する際、もっとも多いエラーが `[Argument type mismatch]` や `[String data, right truncation]` だ。

これらは、VBA側で用意した変数の型や、`CreateParameter` メソッドで指定した `Size` が、データベース側の定義と微妙に食い違っているために発生する。
もし、パラメータが10個あるプロシージャに対し、手動で10個の `CreateParameter` を記述していたらどうなるか？ プロシージャの仕様変更（カラム追加や型変更）のたびに、VBA側のコードを書き換え、テストし直す必要がある。これは「保守性の死」を意味する。

我々が目指すべきは、「呼び出し側は値だけを渡し、パラメータの構成はロジックが自律的に解決する」設計である。

3. 実装：動的パラメータ注入エンジン

以下に、実務レベルでそのまま通用する、堅牢なストアドプロシージャ実行エンジンを示す。
このコードの核心は、引数として受け取った `Dictionary` オブジェクトから、パラメータ名と値を抽出し、ADOの `Command` オブジェクトへ動的にマッピングする点にある。

プロダクションコード例

このコードを使用するには、参照設定で 「Microsoft ActiveX Data Objects x.x Library」 を有効にしておくこと。

Option Explicit

' ======================================================================================
' Module: modDatabaseEngine
' Description: 高度なADO操作を提供するコアモジュール
' ======================================================================================

' パラメータ情報を保持するためのカスタム型
Public Type ParameterInfo
    Name As String
    Value As Variant
    DataType As Long ' ADOの型定数 (adVarChar, adInteger 等)
    Size As Long     ' 文字列等のサイズ
End Type

''' <summary>
''' ストアドプロシージャを動的に実行する
''' </summary>
''' <param name="conn">接続済みのADO.Connectionオブジェクト</param>
''' <param name="spName">ストアドプロシージャ名</param>
''' <param name="params">ParameterInfo型の配列。パラメータがない場合はEmpty</param>
''' <param name="isReadOnly">トランザクションの管理用（Trueなら読み取り専用）</param>
''' <returns>実行結果のRecordset（更新系の場合はNothing）</returns>
Public Function ExecuteStoredProcedure( _
    ByRef conn As ADODB.Connection, _
    ByVal spName As String, _
    Optional ByRef params As Variant, _
    Optional ByVal isReadOnly As Boolean = False) As ADODB.Recordset

    Dim cmd As ADODB.Command
    Dim i As Long
    Dim p As ParameterInfo
    
    On Error GoTo ErrorHandler

    ' Commandオブジェクトの初期化
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = conn
        .CommandText = spName
        .CommandType = adCmdStoredProc
    End With

    ' --- 動的パラメータの注入 ---
    ' paramsはParameterInfoの配列であることを想定
    If Not IsEmpty(params) Then
        For i = LBound(params) To UBound(params)
            p = params(i)
            ' CreateParameter(Name, Type, Direction, Size, Value)
            ' Directionはデフォルトで adParamInput (入力)
            .Parameters.Append .CreateParameter( _
                p.Name, _
                p.DataType, _
                adParamInput, _
                p.Size, _
                p.Value)
        Next i
    End If

    ' --- トランザクション管理と実行 ---
    If Not isReadOnly Then conn.BeginTrans
    
    Dim rs As ADODB.Recordset
    ' Executeの戻り値としてRecordsetを受け取る
    ' (プロシージャがSELECTを返す場合)
    Set rs = cmd.Execute

    If Not isReadOnly Then conn.CommitTrans
    
    Set ExecuteStoredProcedure = rs
    Set cmd = Nothing
    Exit Function

ErrorHandler:
    ' エラー発生時はロールバック
    If Not isReadOnly And conn.State = adStateOpen Then
        conn.RollbackTrans
    End If
    
    ' エラー情報の詳細を投げる（実務ではログ出力に置き換えること）
    Err.Raise Err.Number, "ExecuteStoredProcedure", _
        "Error executing SP: " & spName & vbCrLf & "Detail: " & Err.Description
    
    Set cmd = Nothing
End Function

実装のポイント（アーキテクトの視点）

1. `ParameterInfo` 型の導入:
単なる `Dictionary(Name, Value)` では、`DataType` や `Size` の情報が欠落する。これらを構造体（またはクラス）にカプセル化することで、DB側の厳格な型定義に追従できる。
2. トランザクションの局所化:
`isReadOnly` フラグにより、参照系（SELECT）と更新系（INSERT/UPDATE）のトランザクション制御を一つの関数内で完結させている。これにより、呼び出し側での「Commit忘れ」によるロック事故を防ぐ。
3. `adCmdStoredProc` の明示:
`CommandType` を明示的に指定することで、ADOに対して「これはSQL文ではなくプロシージャである」と伝え、解析のオーバーヘッドを削減している。

4. 実践的な呼び出し方

では、このエンジンをどのように使うべきか。以下に「業務アプリケーション」を想定した実装例を示す。

Sub Test_ExecuteSP()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim params(0 To 1) As ParameterInfo ' 2つのパラメータ
    
    ' 1. 接続設定 (実際には共通の接続関数を使用すること)
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=YOUR_SERVER;Initial Catalog=YOUR_DB;Integrated Security=SSPI;"

    On Error GoTo ErrHandler

    ' 2. パラメータの構築 (ここが動的な肝)
    ' パラメータ1: 社員ID (Integer)
    params(0).Name = "@EmployeeID"
    params(0).DataType = adInteger
    params(0).Size = 0
    params(0).Value = 101

    ' パラメータ2: 備考 (VarChar, Size 50)
    params(1).Name = "@Note"
    params(1).DataType = adVarChar
    params(1).Size = 50
    params(1).Value = "昇進に伴う給与改定"

    ' 3. 実行
    Debug.Print "Executing Stored Procedure..."
    Set rs = ExecuteStoredProcedure(cn, "usp_UpdateEmployeeSalary", params, False)

    If Not rs Is Nothing Then
        MsgBox "更新成功", vbInformation
        rs.Close
    End If

CleanUp:
    If Not rs Is Nothing Then Set rs = Nothing
    If Not cn Is Nothing Then cn.Close: Set cn = Nothing
    Exit Sub

ErrHandler:
    MsgBox "致命的なエラー: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

5. 結論：保守性を担保するための「規律」

この設計の真価は、コードの長さではなく、「変更に対する強さ」にある。

もし将来、データベースの `Note` カラムが `VARCHAR(100)` に拡張されたとしても、君が修正すべきは `params(1).Size = 100` というたった一行だけだ。ロジック全体を書き換える必要はない。

「動けばいい」という考え方は、プロジェクトが大規模化し、運用フェーズに入った瞬間に牙を向く。
`Command` オブジェクトを使いこなし、型とサイズを明示的に管理する。この一見「面倒な」プロセスこそが、プロフェッショナルなエンジニアが、現場の混乱を未然に防ぐための唯一の手段なのだ。

「コードは書く時間よりも、読まれる時間の方が長い。そして、直される回数は、書いた回数よりも圧倒的に多い。」

この格言を胸に、堅牢なアーキテクチャを構築せよ。