[Access][VBA]ADOで定義済みアクションクエリ（パラメータ付き）を実行する

DAOとADO

VBAでAccessのテーブルやクエリを操作する仕組みにDAOとADOがある。

DAO（Data Access Objects）は古くからあるコンポーネントで、AccessのコアであるJETエンジンに特化した仕組みだ。Accessをスタンドアロンで使用するようなときは高速な処理が期待できる。

ADO（ActiveX Data Objects）は、Accessだけでなく外部のデータベースにアクセスすることができる。フロントエンドにAccessのフォームやレポートを使用して、データは外部のデータベース（OracleやSQL Serverなど）に保存するといったケースで威力を発揮する。また。VB.NETやC#などAccessVBA以外のプログラミング言語でも使用することができる。DAOでも外部のデータベースにアクセスすることができるけどODBC接続を使わないと駄目という制約がある。ADOは「OLE DB」という仕組みを使ってODBCを介さないで接続が可能だ。

どっちを使うかはケースバイケースなところがあるが、個人的な意見として、Oracle等外部データベースに接続するなら迷わずADO、Access単体でも将来の拡張性を考慮してADOを使ったほうがいいと思う。最初はスタンドアロンで運用していても、途中からOracle等の外部データベースに移行することがあるからだ。ADOでプログラムを作っておけば、データベース接続の部分だけを書き換えればほぼ修正なしで行けるはずだ（もちろんコードの組み方によっては大幅な修正が必要なこともある）。

VBAで定義済みアクションクエリを実行する（DAOの場合）

ADOでちょっと困るのはAccess独自のコンポーネントの扱い方だ。DAOはAccessに特化しているだけあって、Accessの「テーブル」や「クエリ」を比較的容易に扱うことができる。

例えばあらかじめ作成しておいたアクションクエリを実行する時は

'DAOのアクションクエリ
CurrentDb().QueryDefs("クエリ1").Execute

と一行で実行できる。

アクションクエリにパラメータがある場合はこんな感じになる

'DAOのアクションクエリ（パラメータ付き）
With CurrentDb().QueryDefs("クエリ1")
    .Parameters("パラメータ1").Value = 20200725
    .Execute
End With

VBAで定義済みアクションクエリを実行する（ADOの場合）

ところがADOだとそう簡単にはいかない
上と同じようにアクションクエリを実行するプログラムをADOで書くと次のようになる

'ADOのアクションクエリ
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "クエリ1"
cmd.Execute

Commandオブジェクトを作成して、プロパティをいくつかセットして、実行……ちょっと面倒である。

パラメータ付きだとどうなるかというと

'ADOのアクションクエリ（パラメータ付き）（エラー）
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "クエリ1"
cmd.Parameters("パラメータ1").Value = 20200725 'この行でエラー
cmd.Execute

上のプログラムは実は6行目でエラーになる。
何故かというと、CommandTextでパラメータ付きクエリを指定してもParameterオブジェクトが自動的に作成されないからだ。パラメータ付きクエリを実行するときはCommandオブジェクトに自分でParameterオブジェクトを追加しなくてはならない。

正しくは次のように記述する

'ADOのアクションクエリ（パラメータ付き）
Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "クエリ1"
'クエリ1内のパラメータを作成（複数ある場合はすべて）
cmd.Parameters.Append cmd.CreateParameter("パラメータ1", adVariant, adParamInput, , 20200725)
cmd.Execute

これでうまくいくはずだ。

実はアクションクエリのパラメータが自動的にParameterオブジェクトに変換される仕組みもあるにはある。それはADOの拡張ライブラリのADOXを使うやり方だ。そのプログラムも参考に書いておこうと思う。

'ADOXのアクションクエリ（パラメータ付き）
Dim cmd As ADODB.Command
Dim cat As ADOX.Catalog

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Procedures("クエリ1").Command
cmd.Parameters("[パラメータ1]").Value = 20200525
cmd.Execute

ADOXのCatalogオブジェクトを通じてCommandオブジェクトを取得すると自動的にParameterオブジェクトもその中に追加されているので、値をセットして実行する、という手順になる。

なお、ADOXを使うには参照設定で「Microsoft ADO Ext.～」にチェックマークを入れておくこと。