Access ADO パラメータークエリー

質問

Access ADOでフォームのコントロールを参照するパラメータークエリーを開こうとすると、どうしても「SQLステートメントが正しくありません・・・」のエラーが発生してしまいます。
もちろん、クエリー単体では正常に動作しますが、クエリーのパラメーターに参照するコントロールを登録してもADOでは開けません。
現在はパラメータークエリーの抽出結果を一時テーブルに書き込んで、これをADOで開くという非効率極まりない方法で凌いでいますが、どうすれば良いのでしょうか。

回答No.5

山田 太郎（@f_a_007） · ベストアンサー

添付図を忘れていました。

お礼

たいへんありがとうございます。助かりました。
これで、長年の悩みが解決しました。
「外道」的方法ですが、直接SQL文を編集してからADOで開くのが一番簡単かもしれません。

sqlStr="SELECT B.Field_1, B.ID FROM B WHERE (((B.ID)=" & [Forms]![B]![ID] & "));"

rst.open sqlStr,cn, ....

回答No.4

山田 太郎（@f_a_007）

・パラメータの指定は最大３つまで。
・パラメータの記述は次で統一。
　一番目=[xxxxx]
　二番目=[yyyyy]
　三番目=[zzzzz]

こういう約束ですと、自分だけが使う次のような関数を用意すれば・・・。

Public Function SetQueryParameters(ByVal strQueryName As String, _
　　　　　　　　　　　　　　　　　　Optional varParamValue1 As Variant = Null, _
　　　　　　　　　　　　　　　　　　Optional varParamValue2 As Variant = Null, _
　　　　　　　　　　　　　　　　　　Optional varParamValue3 As Variant = Null) As String
On Error Resume Next
　　Dim cnn As ADODB.Connection
　　Dim cat As ADOX.catalog
　　Dim cmd As ADODB.Command
　　Dim strCommandText As String

　　' 接続を開きます。
　　Set cnn = CurrentProject.Connection

　　' カタログを開きます。
　　Set cat = New ADOX.catalog
　　cat.ActiveConnection = cnn

　　' Command オブジェクト
　　Set cmd = New ADODB.Command
　　Set cmd = cat.Procedures(strQueryName).Command

　　' パラメータの置換
　　strCommandText = cmd.CommandText
　　strCommandText = IIf(Len(varParamValue1 & "") > 0, Replace(strCommandText, "[xxxxx]", varParamValue1), strCommandText)
　　strCommandText = IIf(Len(varParamValue1 & "") > 0, Replace(strCommandText, "[yyyyy]", varParamValue2), strCommandText)
　　strCommandText = IIf(Len(varParamValue1 & "") > 0, Replace(strCommandText, "[zzzzz]", varParamValue2), strCommandText)
　　SetQueryParameters = strCommandText
End Function

添付図では、次のDBSelect()が、ちゃんと"A"を取得しています。

Public Function DBSelect(ByVal strQuerySQL As String, Optional strPause As String = ";") As Variant
On Error GoTo Err_DBSelect
　　Dim I　　　　　 As Integer
　　Dim J　　　　　 As Integer
　　Dim R　　　　　 As Integer　　' DataValue(,) のインデックスを決める行カウンター
　　Dim C　　　　　 As Integer　　' DataValue(,) のインデックスを決める列カウンター
　　Dim M　　　　　 As Integer　　' DataValue(,) の一つ目の添字の最大値=行総数 - 1
　　Dim N　　　　　 As Integer　　' DataValue(,) の二つ目の添字の最大値=列総数 - 1
　　Dim rst　　　　 As ADODB.Recordset
　　Dim fld　　　　 As ADODB.Field
　　Dim strList　　 As String　　 ' 全てのデータをセミコロン(;)等で区切った文字列に

　　Set rst = New ADODB.Recordset
　　' =================
　　'　Begin With: rst
　　' -----------------
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　' --------------
　　　　　　'　配列を再宣言
　　　　　　' --------------
　　　　　　M = .RecordCount - 1
　　　　　　N = .Fields.Count - 1
　　　　　　ReDim dataValues(M, N)
　　　　　　' ------------------------------------
　　　　　　'　列情報を For-Next で配列に代入する
　　　　　　' ------------------------------------
　　　　　　.MoveFirst
　　　　　　For R = 0 To M
　　　　　　　　C = -1
　　　　　　　　For Each fld In .Fields
　　　　　　　　　　With fld
　　　　　　　　　　　　C = C + 1
　　　　　　　　　　　　dataValues(R, C) = .Value
　　　　　　　　　　End With
　　　　　　　　Next fld
　　　　　　　　.MoveNext
　　　　　　Next R
　　　　Else
　　　　　　ReDim dataValues(0, 0)
　　　　　　dataValues(0, 0) = ""
　　　　　　strList = ""
　　　　End If
　　End With
　　' ---------------
　　'　End With: rst
　　' ===============
　　' -------------------------------
　　'　区切子(;)で連結して１文に
　　' -------------------------------
　　If Len(strPause) Then
　　　　For I = 0 To M
　　　　　　For J = 0 To N
　　　　　　　　strList = strList & dataValues(I, J) & strPause
　　　　　　Next J
　　　　　　strList = strList & Chr(13)
　　　　Next I
　　End If
Exit_DBSelect:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBSelect = IIf(Len(strPause), strList, dataValues())
　　Exit Function
Err_DBSelect:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBSelect)" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　　"・SQL Text=" & strQuerySQL, _
　　　　　　vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBSelect
End Function

回答No.3

山田 太郎（@f_a_007）

? DBSelect(Replace(GetQueryText("testQ"), "Forms!B!ID", Forms("B").Controls("ID")))
B;1;

添付図が見にくいので・・・。

つまり、クエリのSQL文を取得し、Replace関数でパラメータをセットしてADOに渡せば、＜ちょっと面倒なやり方＞をする必要はないということです。

お礼

ご教示ありがとうございます。
なぜADOでパラメータークエリーがうまく動かないのか、原因と解消法がわかりました。
ただ、一度に複数のパラメーターを取得する場合は、どの様にしたら良いのでしょうか。
とりあえず、フォーム側を細工すればひとつのパラメーターで済ませそうですが。

回答No.2

山田 太郎（@f_a_007）

＞ADOでフォームを参照するパラメータークエリーを開こうとすると、
＞エラーが発生してしまいます。
＞もちろん、クエリー単体では正常に動作しますが、
＞クエリーのパラメーターを工夫してもADOでは開けません。

これは、当たり前のことですよ。
例えば、次のような関数を用意してテストすると判ります。

Public Function GetQueryText(ByVal strQueryName As String) As String
　　Dim cnn As ADODB.Connection
　　Dim cat As ADOX.catalog
　　Dim cmd As ADODB.Command

　　' 接続を開きます。
　　Set cnn = CurrentProject.Connection

　　' カタログを開きます。
　　Set cat = New ADOX.catalog
　　cat.ActiveConnection = cnn

　　' Command オブジェクト
　　Set cmd = New ADODB.Command
　　Set cmd = cat.Procedures(strQueryName).Command

　　GetQueryText = cmd.CommandText
End Function

【testQ】

SELECT B.Field_1, B.ID
FROM B
WHERE (((B.ID)=[Forms]![B]![ID]));

Accessでクエリを実行するとtestQ のWhere節は

WHERE ID=2

に変換されてます。しかし、ADOで実行しても、その変換は行われません。ですから、当然にエラーになります。

【一番簡単なエラー回避策】

添付図のように、クエリのパラメータを変換してADOに渡すことです。

【ちょっと面倒なやり方】

http://msdn.microsoft.com/ja-jp/library/cc376629.aspx

回答No.1

bin-chan

> Access ADOでフォームのコントロールを参照するパラメータ
単体起動できるんだから、上記の部分があやしい。

パラメータ指定部分を開示いただけますか？