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

マクロの記録を卒業せよ：ADO Commandオブジェクトで実現する「プロ仕様」のストアドプロシージャ呼び出し術

こんにちは。現場の設計図を書き上げるチーフアーキテクトです。

Access VBAを触り始めると、多くの人が「マクロの記録」や、単純な `DoCmd.RunSQL` での操作からスタートします。それはそれで素晴らしい第一歩です。しかし、あなたがもし「もっと大規模なシステムに組み込みたい」「SQL Serverなどの外部データベースと高度に連携したい」と願うなら、そこには決定的な壁が立ちはだかります。

その壁の正体は、「データへのアクセスの作法（アーキテクチャ）」の違いです。

今日は、初級者が必ずと言っていいほど躓く「DAOとADOの違い」を整理した上で、プロの現場で使われる「Commandオブジェクトを用いた動的なパラメータ生成」という、極めて強力な武器の作り方を伝授しましょう。

ここをクリアすれば、あなたのVBAは「ただの自動化ツール」から「堅牢な業務アプリケーション」へと進化します。準備はいいですか？

1. そもそも「DAO」と「ADO」は何が違うのか？

「どっちもテーブルからデータを取るためのものじゃないの？」と思ったあなた。半分正解で、半分は非常に危険な考え方です。

Accessの世界には、大きく分けて2つの「データの運び屋」が存在します。

【アーキテクトの視点】
Access内のテーブルをいじるだけならDAOで十分です。しかし、SQL Serverに保存された「ストアドプロシージャ（サーバー側で動くプログラム）」を呼び出すなら、必ずADOを使いなさい。 これが鉄則です。

2. ストアドプロシージャ呼び出しの「地獄」と「天国」

SQL Serverなどのストアドプロシージャを呼び出す際、引数（パラメータ）を渡す必要があります。

初心者が陥る「地獄のコード」

パラメータが10個あるストアドを呼び出すとき、初心者はこう書きがちです。

' ❌ 良くない例：パラメータを手動で一つずつ、順番通りに並べる
cmd.Parameters.Append cmd.CreateParameter("@ID", adInteger, adParamInput, , 101)
cmd.Parameters.Append cmd.CreateParameter("@Name", adVarChar, adParamInput, 50, "田中")
' ...これを10回繰り返す...

これの何が問題か？
1. 順番が変わると即死する： ストアド側の定義が少し変わっただけで、プログラム全体が壊れます。
2. 保守が地獄： パラメータが増えるたびに、VBAコードを書き直さなければなりません。
3. 型エラーに弱い： 「ここは数値のはずなのに文字列を入れた」というミスを見つけるのが困難です。

プロが目指す「天国（動的生成）」

プロは、「パラメータの名前と値のペア」を渡し、プログラムが自動的にストアドの定義を読み取って、型やサイズを判別してセットする仕組みを作ります。

3. 実践：パラメータ動的生成エンジンの実装

では、実際に「名前を指定するだけで、型を自動判別してセットしてくれる関数」を実装しましょう。

事前準備

VBAエディタのメニュー [ツール] -> [参照設定] から、以下にチェックを入れてください。

• Microsoft ActiveX Data Objects x.x Library
• Microsoft Scripting Runtime (Dictionaryを使うため)

極限のコード例

Option Compare Database
Option Explicit

''' <summary>
''' ストアドプロシージャを動的に実行するメインルーチン
''' </summary>
Public Sub ExecuteStoredProc_Dynamic()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim params As Scripting.Dictionary
    Dim connString As String
    
    ' 1. 接続文字列の設定（環境に合わせて変更してください）
    connString = "Provider=SQLNCLI11;Server=YOUR_SERVER;Database=YOUR_DB;Trusted_Connection=yes;"
    
    ' 2. 渡したいパラメータをDictionaryに格納（名前と値をセットにする）
    ' これにより、順番を気にせず「名前」で管理できる
    Set params = New Scripting.Dictionary
    params.Add "@EmployeeID", 101              ' 数値型
    params.Add "@EmployeeName", "田中 太郎"    ' 文字列型
    params.Add "@JoinDate", #2023/04/01#       ' 日付型
    
    On Error GoTo ErrorHandler
    
    ' 3. オブジェクトの初期化
    Set conn = New ADODB.Connection
    Set cmd = New ADODB.Command
    
    ' 4. 接続とコマンドの設定
    conn.Open connString
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "usp_UpdateEmployee" ' 呼び出すストアド名
    cmd.CommandType = adCmdStoredProc      ' 「これはストアドです」と宣言
    
    ' 5. 【ここが核心】動的パラメータセット
    Call SetParameters(cmd, params)
    
    ' 6. 実行（トランザクション管理を視野に）
    conn.BeginTrans ' トランザクション開始
    cmd.Execute
    conn.CommitTrans ' 成功したら確定
    
    MsgBox "実行に成功しました。", vbInformation
    
CleanUp:
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.Close
    End Set conn
    Set cmd = Nothing
    Set params = Nothing
    Exit Sub

ErrorHandler:
    If Not conn Is Nothing Then
        If conn.State = adStateOpen Then conn.RollbackTrans ' 失敗したらロールバック！
    End If
    MsgBox "エラーが発生しました: " & Err.Description, vbCritical
    Resume CleanUp
End Sub

''' <summary>
''' Commandオブジェクトの定義に基づき、Dictionaryの内容を自動的にマッピングする
''' </summary>
Private Sub SetParameters(ByRef cmd As ADODB.Command, ByRef dict As Scripting.Dictionary)
    Dim p As ADODB.Parameter
    Dim key As Variant
    
    ' Commandオブジェクトがストアドの定義を読み込んでいる状態を利用する
    ' (※プロバイダが対応している場合、cmd.Parametersには既に定義が存在する)
    
    For Each key In dict.Keys
        Dim found As Boolean: found = False
        
        ' ストアドが持っているパラメータ定義をループで探す
        For Each p In cmd.Parameters
            ' 名前が一致するかチェック（@の有無を考慮）
            If Replace(p.Name, "@", "") = Replace(CStr(key), "@", "") Then
                ' 値をセット（型変換はADOがよしなにやってくれる）
                p.Value = dict(key)
                found = True
                Exit For
            End If
        Next p
        
        If Not found Then
            Err.Raise vbObjectError + 513, "SetParameters", _
                "ストアドプロシージャにパラメータ '" & key & "' が定義されていません。"
        End If
    Next key
End Sub

4. このコードの「凄み」を解説する

なぜこのコードが「プロ仕様」なのか、その設計思想を紐解きましょう。

① 名前によるマッピング（疎結合の実現）

`SetParameters` 関数の中で、`cmd.Parameters`（ストアド側の定義）と `dict`（あなたの入力値）を名前で照合しています。
これにより、ストアドの引数の順番が `(ID, Name)` から `(Name, ID)` に変わったとしても、プログラムを修正する必要はありません。これはソフトウェア設計における「疎結合（Loose Coupling）」という非常に重要な概念です。

② トランザクションによる整合性の担保

`conn.BeginTrans` と `conn.RollbackTrans` を見てください。
もし、パラメータの型が違ったり、ネットワークが切断されたりしても、`RollbackTrans` が実行されることで、「中途半端にデータが更新されてしまう」という最悪の事態を防ぎます。これは、銀行のシステムなど、データの整合性が命の現場では必須の作法です。

③ オブジェクトのライフサイクル管理

`CleanUp` セクションで、接続を確実に閉じる（`conn.Close`）ように設計しています。
接続を開きっぱなしにすると、サーバー側のリソースを食いつぶし、やがてシステム全体が停止します。プロは、「作ったものは、必ず責任を持って片付ける」のです。

最後に：ここをクリアすれば、あなたはもう「初級者」ではない

いかがでしたか？
一見、難解に見えるかもしれませんが、構造はシンプルです。

1. Dictionaryに「名前」と「値」を詰め込む。
2. Commandオブジェクトが持つ「定義」と照らし合わせる。
3. トランザクションで守る。

このパターンをマスターすれば、どんなに複雑なストアドプロシージャが来ても、あなたは冷静に、エレガントにコードを書くことができるはずです。

「マクロの記録」という魔法の杖を捨て、自らの手で「堅牢なロジック」を組み上げる。その挑戦こそが、エンジニアとしての真の成長への道です。

応援していますよ。次は、より高度なパフォーマンスチューニングの世界でお会いしましょう！