Access2000でVBAからパラメータクエリを実行

質問

Access2000でフォームからの値を参照して実行するクエリを作りました。
SQLで表すと、以下のようなものです。

クエリ名：test
SELECT TEST.HAKKODTE FROM TEST
WHERE (((TEST.HAKKODTE)=Format([Forms]![F_メニュー]![txtNyukinDate],"yyyymmdd")));

このクエリをダブルクリックで実行すると問題なく実行できます。

これを、VBAからレコードを参照したいと思い、
以下のように記述しました。

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

Dim sqlstr As String

sqlstr = "SELECT * FROM test;"

rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic

しかし、Open処理で、「１つ以上の必要なパラメータの値が設定されていません」
というエラーになってしまいます。

VBAを実行するときに、[Forms]![F_メニュー]![txtNyukinDate]に
きちんと値は入力されています。

これを実行する方法はあるでしょうか？

回答No.7

gadd3 · ベストアンサー

すみません、No.6です。補足です。

当方のテストでは "yyyy/mm/dd" を使いましたが、
テーブルの「HAKKODTE」列のデータ型によっては
違うと思います。

自作関数の
Function date_return() As Date
も、
takuchantikuさんの環境では
Function date_return() As String
が正解かもしれません。
（フォールドのデータ型のご提示が無いので適当にテストしました。）

ご自分の環境に合わせて適当に読み替えてください。
上記はあくまでもテストなので、適当です。

言いたかった事は…、
パラメータクエリをレコードセットして開きたい時は…。

・フォームの値を自作関数を使って（自作関数を経由させて）
クエリの抽出条件に（言わば間接的に）セットし、
・そのクエリをVBAで、"まんま"で実行する

というやり方です。

説明不足ですみませんでした。

お礼

回答ありがとうございます。
自作関数を作成し、クエリに
=date_return()
と組み込む、という方法は大変参考になりました。
活用させてもらいます。

回答No.6

gadd3

(01)標準モジュールに、例えば以下のようなフォームの値をGETして返すだけの関数を書きます。

Function date_return() As Date

date_return = Format([Forms]![F_メニュー]![txtNyukinDate], "yyyy/mm/dd")

End Function

(02)クエリの「HAKKODTE」列の抽出条件に(01)の関数を組み込みます。

=date_return()

と書きます。

クエリのSQLは例えばこんな感じになります。

SELECT TEST.a, TEST.HAKKODTE
FROM TEST
WHERE (((TEST.HAKKODTE)=date_return()));

クエリ名を「q_test01」とします。

(03)標準モジュールに以下のコードを書きます。
（クエリ名をかえて最後の行を足しただけです）

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

Dim sqlstr As String

sqlstr = "SELECT * FROM q_test01;"

rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic

Debug.Print rs1.Fields(0) & "-----" & rs1.Fields(1)

こういう感じで当方ではエラーが無く通っていますが…
イミディエイトにも抽出（ヒット）したレコードの値が表示されます。

なお、sqlstr = "SELECT * FROM q_test01;"

のところは

sqlstr = "SELECT * FROM クエリ名;"

という感じです。

私はいつも、フォームの値を自作関数でクエリに渡して、
クエリをVBAで実行してます。
簡単に無理やりクエリを実行するなら自作関数を使うしか方法は
無いのではないでしょうか？

なお、ご質問では、

>クエリ名：test
>SELECT TEST.HAKKODTE FROM TEST
>WHERE (((TEST.HAKKODTE)=Format([Forms]![F_メニュー]![txtNyukinDate],"yyyymmdd")));

ということで、TESTというテーブルを同じくTESTというクエリにて
開こうとしていますが、通常、テーブル名とクエリ名などで
同じ名前を使おうとすると、エラーが出て作れないのではないでしょうか？
（つまりありえない）

だとすれば、

sqlstr = "SELECT * FROM test;"

も少々おかいしような…

これだったら、クエリ名が「TEST」以外の名前なら、
テーブル「TEST」のすべてがレコードセットになるだけで
エラーにはならないはず・・・

クエリ名は本当は「TEST」テーブルと違う名前を使っているのでは
ないでしょうか？

テーブル名、フィールド名、データ型、
クエリ名
などを

もういちどしっかりとご提示されてはどうでしょうか？

違ってたらすみません。

お礼

回答ありがとうございます。

>クエリ名は本当は「TEST」テーブルと違う名前を使っ>ているのでは
>ないでしょうか？
はい、その通りです。
間違っていました。
申し訳ありませんでした。

回答No.5

noname#79209

単純に
DoCmd.OpenQuery
ではダメなんですか？

お礼

回答ありがとうございます。
今回は、VBAでレコードセットを使い、いろいろ編集したいので、
DoCmd.OpenQuery
では、だめ、、、だと思ったんですが、
対象のクエリを、テーブル作成クエリにし、
VBAにて、DoCmd.OpenQueryで実行し、
作成されたテーブルを参照することで、回避することができました。

回答No.4

noname#22222

s_husky です。

チクッと XferExecuteSQL() を作ってみました。

? dblookup(xferexecutesql(getQuerysql("クエリ1"),0))
1;A1;2006/08/01;10:00:00;

と、まあ、成功しています。

Public Function XferExecuteSQL(ByVal strQuerySQL As String, Optional fldType As Integer = 1) As String
　　Dim I　　　 As Integer
　　Dim J　　　 As Integer
　　Dim L　　　 As Integer
　　Dim P　　　 As Integer
　　Dim C　　　 As String
　　Dim V　　　 As String
　　Dim frmName As String
　　Dim fldName As String

　　L = Len(strQuerySQL)
　　P = InStr(1, strQuerySQL, "Forms!", vbTextCompare) + 6
　　For I = P To L
　　　　C = Mid$(strQuerySQL, I, 1)
　　　　If C = "!" Then
　　　　　　J = 1
　　　　ElseIf C = ")" Then
　　　　　　Exit For
　　　　Else
　　　　　　If J = 0 Then
　　　　　　　　frmName = frmName & C
　　　　　　Else
　　　　　　　　fldName = fldName & C
　　　　　　End If
　　　　End If
　　Next I
　　V = Forms(frmName).Controls(fldName)
　　If fldType = 1 Then
　　　　V = "'" & V & "'"
　　ElseIf fldType = 2 Then
　　　　V = "#" & V & "#"
　　End If
　　XferExecuteSQL = CutStr(strQuerySQL, "Forms!" & frmName & "!" & fldName, 1) & _
　　　　　　　　　　 V & _
　　　　　　　　　　 CutStr(strQuerySQL, "Forms!" & frmName & "!" & fldName, 2)
End Function

Public Function CutStr(ByVal Text As String, _
　　　　　　　　　　　 ByVal Separator As String, _
　　　　　　　　　　　 ByVal N As Integer) As String
　　Dim strDatas() As String

　　strDatas = Split("" & Separator & Text, Separator, , 0)
　　CutStr = strDatas(N * Abs((N <= UBound(strDatas))))
End Function

なお、GetQuerySQL()に綴りの間違いがあったので正しておきます。

Public Function GetQuerySQL(ByVal QName As String) As String
　　Dim dbsCurrent　As DAO.Database
　　Dim qdfQuery　　As QueryDef
　　Dim strQuerySQL As String

　　Set dbsCurrent = CurrentDb
　　Set qdfQuery = dbsCurrent.QueryDefs(QName)
　　GetQuerySQL = qdfQuery.SQL
　　qdfQuery.Close
　　dbsCurrent.Close
End Function

XferExecuteSQL()は、パラメータの種類が一つです。
また、パラメータが数字、文字列、日付なのかを指示する必要があります。
XferExecuteSQL()を汎用化するには、幾つかのテストを重ねる必要があるでしょう。

※かように、面倒臭いプログラミングが果たして常道なのか否か？
※多少、疑問に思います。

お礼

回答ありがとうございます。
今回は、上記２つの方法どちらかにしたいと思います。
実際に関数を作っていただきありがとうございました。今後の参考にしたいと思います。

回答No.3

noname#22222

実行する方法はあります。
が、専用の関数を開発する必要があります。

まず、その理由を確認出来る関数を示します。

Public Function GetQurySQL(ByVal QName As String) As String
　　Dim dbsCurrent　As DAO.Database
　　Dim qdfQuery　　As QueryDef
　　Dim strQuerySQL As String

　　Set dbsCurrent = CurrentDb
　　Set qdfQuery = dbsCurrent.QueryDefs(QName)
　　GetQurySQL = qdfQuery.SQL
　　qdfQuery.Close
　　dbsCurrent.Close
End Function

? GetQurySQL("クエリ1")
SELECT Table1.*, *
FROM Table1
WHERE (((Table1.ID)=[Forms]![フォーム1]![ID]));

このように、GetQurySQL関数でクエリのSQL文を参照すると、[Forms]![フォーム1]![ID]が置換されていません。
これがエラーの原因です。

ということは、

DBLookup(XferExecuteSQL(GetQuerySQL("クエリ1")))

で参照可になるということです。

XferExecuteSQL関数は、[Forms]![フォーム1]![ID]の置換を行う関数です。

ここまでで、質問者は、XferExecuteSQL関数の作成が課題であることが判ったと思います。
質問の的をそこに絞って再質問すれば即答えが集中するでしょう。

Public Function DBLookup(ByVal strQuerySQL As String) As String
On Error GoTo Err_DBLookup
　　Dim I　　 As Integer
　　Dim N　　 As Integer
　　Dim Datas As String
　　Dim dbs　 As DAO.Database
　　Dim rst　 As DAO.Recordset

　　Set dbs = CurrentDb
　　Set rst = dbs.OpenRecordset(strQuerySQL)
　　With rst
　　　　Do Until .EOF
　　　　　　N = .Fields.Count - 1
　　　　　　For I = 0 To N
　　　　　　　　Datas = Datas & .Fields(I) & ";"
　　　　　　Next I
　　　　　　.MoveNext
　　　　Loop
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　dbs.Close
　　DBLookup = Datas
　　Exit Function
Err_DBLookup:
　　MsgBox Err.Description
　　Resume Exit_DBLookup
End Function

回答No.2

kabilunlun

クエリーをかまさずにやってみてはどうですか。
「１つ以上の必要なパラメータの値が設定されていません」はOpenメソッドを実行したときに
テーブル名やフィールド名が正しくないときにでるようなので、もしかしたらtxtNyukinDateが上手く参照できていないかもしれません。

sqlstr = "SELECT TEST.HAKKODTE FROM TEST WHERE TEST.HAKKODTE = '" & Format(Forms!F_メニュー.txtNyukinDate,"yyyymmdd") & "'"
rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic

現在自分で検証できないため、F_メニューのtxtNyukinDateの参照方法は間違っているかもしれません。

お礼

引き続きありがとうございます。
クエリをかまさずに、べたでSQLを記述すると、問題なく実行できました。

しかし、コードで記述するのは大変（同じようなものが複数ある）ので、
できれば、クエリから実行したいと思っています。

もう少し調査してみます。

回答No.1

kabilunlun

sqlstr = "SELECT * FROM test;"
rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic
を、
Set rs1 = cn.Execute("SELECT * FROM test")
に置き換えてもだめですかね。

お礼

回答ありがとうございます。
置き換えてみましたが、やはり同じエラーになってしまいました。。。