MS Accessのパラメータクエリ

2017年5月23日 2025年4月22日 asai

asai

本日は、2回目の投稿となる。MS Accessのパラメータクエリのお話である。
パラメータクエリ
Accessでのクエリは「VIEWみたいなものである」とSQLポケリに書いたと思う。
といい加減ではいけない気がするので、ちょっと調べた。
CREATE VIEWのところに、*Accessでは、ビューは「クエリ」として作成されます。
と書いてある。
1.6ビューのところに、ビューにはパラメータを付けることはできない、うんぬんという表記もある。
Accessにおいては、ビュー=クエリにパラメータを付けることができる。
この辺りが「Accessならでは」の事情になっており混乱している。
Oracleなどのデータベースはサーバーとして動作している。サーバへの命令はほとんどSQLだけで行う。
Accessは、データベースエンジンが内蔵されていて、ユーザ操作のGUIとセットになって動いている。データベース操作は、GUIのデザイナで行う。一部の操作はSQLでも可能。逆にSQL命令でなければ、記述できないクエリも存在する。
Accessのデータベースエンジンだけを使用して、SQL命令だけでAccessデータベースを扱うことも可能ではあるが、メインとなる操作方法ではない。
それは、さておき、パラメータクエリの作成方法について解説してみたいと思う。
パラメータクエリといっても、クエリの作成方法が異なるわけではない。普通にクエリを作成して、デザインビューの抽出条件のところに、[ ] で囲んで適当な文字列を記述すればよいだけ。

この状態で、クエリを実行すると、ダイアログボックスが表示され、[ ] の中に書かれた文字列が表示される。ユーザが、ダイアログボックスに数値を入力すると、その数値が抽出条件になる。といった寸法である。

テーブルから条件に一致するレコードのみを抽出したい、という要望は日常茶飯事的に発生するものである。クエリにパラメータを持たせることができるのは、非常に便利なわけである。
パラメータの型を指定しておくと、ダイアログボックスで入力した数値がエラーチェックされるようになるので、さらに便利になる。

また、>=[パラメータ]のように抽出条件を書いておけば、入力された数値以上であるレコードが抽出されるようにもできる。
Accessで、クエリを作成すると、データベースオブジェクトのビューのようなものが作成される。Oracleなどでは、ビューにパラメータを作成することはできないが、Accessではそれが可能となっている。
ビューの中に、パラメータがあると、Accessでは、数値を入力するようにダイアログを表示する。
Oracleなどでは、ビューにパラメータを付けることはできないが、仮にパラメータが作成できたとする。Oracleは別のサーバマシンで動作していることが多いであろう。サーバマシンでダイアログを表示しても、ユーザが数値を入力することはできないであろう。
つまり、Accessはデータベースエンジンとユーザ操作のGUIが一体化しているので、こういった芸当が可能なわけである。
置換変数
ちなみに、OracleのSQL Developerでは、「&&パラメータ」と書いておくと同じようなことが可能である。
しかし、これは、SQL DeveloperやSQL*Plusだけでの話。バインド変数ではなく、置換変数と呼ばれる機能。SQL Developerが変数の内容を展開してからクエリを実行する。ビューの中に置換変数を記述することはできない。

AccessのパラメータクエリとOracleの置換変数を見てきた。クエリにパラメータを渡す方法は、各データベースでいろいろ。SQL標準を基本に考えれば「ビューにパラメータを付けることはできない」で正解である。
SQLポケリでは、ビューにパラメータを付けることができないので、そういう場合はストアドを作れ、となっている。Accessでは、クエリにパラメータを付けることができるので、それで代用可能では？と思われるが、そうもいかない。
なぜかというと、クエリからパラメータ付きの別クエリを結合なりで呼び出すことは可能であるが、パラメータを与えることができない。例えば、以下のようなSELECT命令にはできないのである。

SELECT * FROM foo CROSS JOIN パラメータクエリ(foo.a)

パラメータの値は、ダイアログボックスで入力するしかないのである。
テーブルを戻す関数を作成すれば、ビューに抽出用のパラメータを付けた感じにすることができる。Accessではできないが、SQL Serverでは以下のようにすれば可能である。

CREATE FUNCTION table_func(@argment int) RETURNS TABLE AS
RETURN (SELECT * FROM foo WHERE a=@argment)
SELECT * FROM foo CROSS APPLY table_func(foo.a) AS S

table_funcがテーブルを返す関数。その引数にfooのa列の値を指定して渡している。
こういった芸当はAccessのパラメータクエリではできないのである。
CROSS APPLYの話はこちらからどうぞ