パラメータクエリの使い方（Jetエンジン）

TipでAccessのクエリで作成したSQLをASP/ADOのアプリケーションに流用する方法を説明しました。パラメータクエリを使用すれば、Accessで作成したクエリをそのままASP/ADOのアプリケーションで使用することができます。Accessのパラメータクエリは、SQL Serverのストアドプロシージャ(Stored Procedure)に相当します。（SQL ServerのStored Procedureは、コンパイルされて保存されていますのでAccessのクエリより高速です。）

● Accessのパラメータクエリを作成するには

ASP/ADOのアプリケーションからAccessのパラメータクエリを使用するために、以下の手順でパラメータクエリを作成します。

① ダウンロードしたサブフォルダ(\samples\ado\webdb)に格納されているado.mdbを作業用のフォルダにコピーします。

② Access を起動して、作業フォルダにコピーしたado.mdbを開きます。

③ データベースウィンドウが表示されたら、オブジェクトの一覧からクエリのタブをクリックします。クエリの一覧からデザインビューでクエリを作成するをクリックします。

テーブルの表示ダイアログが表示されたら、テーブルの一覧から得意先テーブルを選択して追加ボタンをクリックします。閉じるのボタンをクリックしてダイアログを閉じます。

④ 選択クエリのダイアログに表示されている得意先テーブルのフィールドリストから得意先コードをダブルクリックしてクエリのフィールドに移動します。同様の手順で得意先名、担当者名、電話番号、フリガナ、都道府県をダブルクリックしてクエリのフィールドに移動します。フリガナの並べ替えのコンボボックスから昇順を選択します。都道府県の抽出条件に[県名]を入力します。フリガナと都道府県の表示のチェックボックスをクリックしてチェックを外します。

⑤ クエリメニューから、パラメータを選択します。

クエリパラメータのダイアログが表示されたら、パラメータのフィールドに[県名]を入力します。データ型のコンボボックスから、テキスト型を選択します。OKボタンをクリックしてダイアログを閉じます。

⑥ これでパラメータクエリが完成しました。確認のために、データシートビューのアイコンをクリックします。パラメータの入力のダイアログが表示されたら、県名のテキストボックに東京都を入力してOKボタンをクリックします。

クエリの結果がデータシート上に表示されます。

⑦ クエリをqryテストの名称で保存して、Accessを終了させます。ASP/ADOのサンプルでは、すでに登録されているqry都道府県別得意先リストのパラメータクエリを使用します。

● パラメータクエリのパラメータに値を設定するには

Access で作成したパラメータクエリを使用してデータベースの得意先テーブルから東京都内の得意先を抽出する方法を説明します。Accessのパラメータクエリの作成方法はすでに説明しましたので省略します。ADOからAccessのクエリを使用するには、ADOXのCatalog, Procedure, Commandオブジェクトを使用します。これらのオブジェクトの関連については、ADO/ADOXオブジェクトモデルを参照してください。

Accessのパラメータクエリのパラメータに値を設定するには、CatalogオブジェクトのProceduresコレクションからパラメータクエリのCommandオブジェクトを生成します。次に、CommandオブジェクトのParametersコレクションからパラメータを参照して値を設定します。最後に、Commandオブジェクトを指定してレコードセットを開きます。

Catalog è Procedures(“ParamQuery”) è Command è Parameters(“ParamName”) è ParamValue

図　Accessのパラメータクエリにパラメータ値を設定するサンプル

Access + Webのダウンロードにサンプルが収録されています。ダウンロードの目次を表示させたら、「パラメータ値を設定するには（その１）」をクリックしてください。Internet Explorerの左側のフレームには、ASPのソースが表示されます。右側のフレームには、実行結果が表示されます。

図の行21-24では、Access 2000のデータベースado.mdbを接続するための処理を行っています。行21-22では、ProviderとData Sourceを設定して変数strConnectionに保存しています。行23では、Server.CreateObjectメソッドでConnectionオブジェクトを生成しています。行24では、ConnectionオブジェクトのOpenメソッドでデータベースado.mdbを接続しています。

21: strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" _

22: & "Data Source=" & server.MapPath("webdb/ado.mdb")

23: set cnn = server.CreateObject("adodb.connection")

24: cnn.Open strConnection

行26では、Server.CreateObjectメソッドでCatalogオブジェクトを生成しています。行27では、CatalogオブジェクトのActiveConnectionプロパティに行24で接続したConnectionオブジェクトを設定しています。

26: set cat = server.CreateObject("adox.catalog")

27: cat.ActiveConnection = cnn

行29-30では、CatalogオブジェクトのProceduresコレクションから「qry都道府県別得意先リスト」のCommandオブジェクトを生成しています。行31では、CommandオブジェクトのParametersコレクションから「[県名]」のパラメータを取得してパラメータ値として「東京都」を設定しています。

29: set cmd = _

30: cat.Procedures("qry都道府県別得意先リスト").Command

31: cmd.Parameters("[県名]") = "東京都"

行33では、Server.CreateObjectメソッドでRecordsetのインスタンスを生成しています。

33: set rs = server.CreateObject("adodb.recordset")

行34-47のWith…End Withでは、Recordsetのオブジェクトを参照しています。行35-36では、RecordsetオブジェクトのOpenメソッドでパラメータクエリのレコードセットを作成しています。（Openメソッドの引数１にCommandオブジェクトを指定していますのでパラメータクエリのレコードセットが作成されます。）パラメータクエリ(qry都道府県別得意先リスト)のパラメータ（[県名]）には、「東京都」が設定されていますので、ここで作成されたレコードセットには東京都内の得意先のみ抽出されます。

35: .Open cmd, , adOpenForwardOnly, _

36: adLockReadOnly, adCmdText

Openメソッドの引数で指定した、adOpenForwardOnly, adLockReadOnly, adCmdTextの定数名は、行3のinclude file=で指定したadovbs.incに定義されています。

3:

行38-45のdo until…loopでは、レコードセットのすべてのレコードをブラウザに表示しています。行39-42のfor each…nextでは、レコードのすべてのフィールドをブラウザに表示しています。行44では、RecordsetオブジェクトのMoveNextメソッドで次のレコードに移動しています。カレントが最終レコードのとき、MoveNextメソッドを実行するとEOFが報告されてdo until …loopが終了します。

38: do until .EOF

39: for each fld in .Fields

40: response.Write fld.Name & ": " _

41: & fld.Value & "<br>"

42: next

43: response.Write "<hr>"

44: .MoveNext

45: loop

行46では、レコードセットを閉じています。行49では、データベースを切断しています。行50-53では、Recordset, Command, Catalog, Connectionオブジェクトを解放しています。

50: set rs = nothing

51: set cmd = nothing

52: set cat = nothing

53: set cnn = nothing

● パラメータクエリに設定する値をドロップダウンリストから受け取るには

パラメータクエリ（qry都道府県別得意先リスト）のパラメータ（[県名]）に設定する値を、HTMLの<select>・・・</select>タグで作成したドロップダウンリストから選択する方法を説明します。

図　パラメータクエリに設定する値をドロップダウンリストから受け取るサンプル

Access + Webのダウンロードにサンプルが収録されています。ダウンロードの目次を表示させたら、「パラメータ値を設定する（その２）」をクリックしてください。Internet Explorerの左側のフレームには、ASPのソースが表示されます。右側のフレームには、実行結果が表示されます。

図の行48-61では、HTMLの<select>・・・</select>タグでドロップダウンリストを作成しています。行50-59のfor…nextでは、配列avarKenに格納されている都道府県名をHTMLの<option>・・・</option>タグに埋め込んでドロップダウンリストのアイテムを作成しています。配列avarKenには、行31のRecordsetオブジェクトのGetRowsメソッドで取り込んだ都道府県名が格納されています。

27: set rs = server.CreateObject("adodb.recordset")

28: with rs

29: rs.Open strSQL,cnn, adOpenForwardOnly, _

30: adLockReadOnly, adCmdText

31: avarKen = rs.GetRows

32: rs.Close

33: end with

行53-55のifステートメントでは、前回選択した都道府県をデフォルト表示しています。<option value=’東京都’ selected>のようにselectedを挿入すると東京都がデフォルト表示されます。行58では、変数strOptionに格納されている<option>・・・</option>タグをブラウザに送信しています。

48: <select size="1" name="selKen">

49: <%

50: for intR = 0 to UBound(avarKen,2)

51: strOption = "<option value='" _

52: & avarKen(1,intR) & "'"

53: if strKen = avarKen(1,intR) then

54: strOption = strOption & " selected"

55: end if

56: strOption = strOption & ">" _

57: & avarKen(1,intR) & "</option>"

58: response.Write strOption & vbNewLine

59: next

60: %>

61: </select>

行65のifステートメントでは、変数strKenに都道府県名が格納されているか調べています。strKenには、行12のRequest.Formコレクションで取得した都道府県名が格納されています。RequestオブジェクトのFormコレクションは、フォームの送信ボタンをクリックしたときのみ有効です。フォームの送信ボタンをクリックすると、HTMLの<form>タグのaction=で指定したASPファイルがロードされます。（このサンプルでは、自分自身ParamQuery2.aspを指定しています。）ASPファイルが最初にロードされたときは、strKenには、空白が格納されます。

12: strKen = request.Form("selKen")

strKenに都道府県名が格納されているときは、行66-82を実行します。行66-68では、パラメータクエリ(qry都道府県別得意先リスト)のパラメータ（[県名]）にstrKenに格納されている県名を設定しています。行69-82のWith…End Withでは、Recordsetのオブジェクトを参照しています。行70-71では、RecordsetオブジェクトのOpenメソッドでパラメータクエリのレコードセットを作成しています。レコードセットには、ドロップダウンリストから選択した県名のみ抽出されます。行73-80のdo until…loopでは、レコードセットのすべてのレコードをブラウザに表示させています。行74-77のfor each…nextでは、レコードのすべてのフィールド名（得意先コード、得意先名、担当者名、電話番号）と値をブラウザに送信しています。行79では、RecordsetオブジェクトのMoveNextメソッドで次のレコードに移動しています。カレントが最終レコードのとき、MoveNextメソッドを実行するとEOFが報告されてdo until…loopが終了します。行81では、レコードセットを閉じています。

65: if len(strKen) > 0 then

66: set cmd = _

67: cat.Procedures("qry都道府県別得意先リスト").Command

68: cmd.Parameters("[県名]") = strKen

69: with rs

70: .Open cmd, , adOpenForwardOnly, _

71: adLockReadOnly, adCmdText

72: response.Write strKen & "内の得意先リスト：<hr>"

73: do until .EOF

74: for each fld in .Fields

75: response.Write fld.Name & ": " _

76: & fld.Value & "<br>"

77: next

78: response.Write "<hr>"

79: .MoveNext

80: loop

81: .Close

82: end with

83: end if

行84では、データベースを切断しています。

ドロップダウンリストから「東京都」を選択して送信ボタンをクリックすると、図のように東京都内の得意先が表示されます。

図　ドロップダウンリストから東京都と選択して東京都内の得意先を表示させた例

フォームの送信ボタン経由でASPファイル(ParamQuery2.asp)がロードされたときは、行12のRequest.Formコレクションにドロップダウンリストから選択した都道府県名が格納されています。

12: strKen = request.Form("selKen") è 東京都

行22-25では、得意先が登録されている都道府県のみ抽出するSQLを作成して変数strSQLに保存しています。複数のテーブルを連結するような複雑なSQLを作成するときは、Accessのクエリを作成してSQLを流用すると便利です。

22: strSQL = "SELECT 都道府県.ID, 得意先.都道府県" _

23: & " FROM 都道府県 INNER JOIN 得意先" _

24: & " ON 都道府県.都道府県 = 得意先.都道府県" _

25: & " GROUP BY 都道府県.ID, 得意先.都道府県;"

行27-33では、strSQLに保存されているSQLのレコードセットを作成してGetRowsメソッドでレコードセットの内容を配列変数avarKenに保存しています。avarKenは、HTMLの<option>・・・</option>タグを生成するときに使用します。