none
パススルークエリに複数パラメータを設定しVBAから実行する RRS feed

  • 質問

  • パススルークエリのWhere句に日付のパラメータを二つ設定しようとしています。

    フォームから入力された値をパラメータに渡してクエリを実行したいのですが

    「実行時エラー’-2147467259(80004005)’:ODBC--呼び出しが失敗しました」で困っております。

    DBはOracle 11g、Access2016でクエリのWhere句とモジュールは下記としています。

    パラメータのない、日付を定数にしたパススルークエリでは実行ができており、

    対象のクエリの構文はSQLServerのものに編集しています。

    SQL文でのパラメータの記述に問題がありますでしょうか?

    WHERE TBL.ASKDY BETWEEN '[StartDate]' AND '[EndDate]';
    Sub ExtractTarget()
    
    Dim Cnn As New ADODB.Connection
    Dim Cat As New ADOX.Catalog
    Dim Cmd As New ADODB.Command
    Dim prmStart As ADODB.Parameter
    Dim prmEnd As ADODB.Parameter
    Dim RS As ADODB.Recordset
    
    Set Cnn = CurrentProject.Connection
    Set Cat.ActiveConnection = CurrentProject.Connection
    Set Cmd.ActiveConnection = Cat.ActiveConnection
    
    Cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True
    Cmd.Properties("Jet OLEDB:Pass Through Query Connect String") = _
     "ODBC;DSN=DBName;database=pubs;UID=ID;PWD=pass;"
    Set Cmd = Cat.Procedures("QueryA").Command
    
    Set prmStart = Cmd.CreateParameter("StartDate", adDate, adParamInput)
    Cmd.Parameters.Append prmStart
    Cmd.Parameters("StartDate").Value = [Forms]![MyForm]![StartDate]
    
    Set prmEnd = Cmd.CreateParameter("EndDate", adDate, adParamInput)
    Cmd.Parameters.Append prmEnd
    Cmd.Parameters("EndDate").Value = [Forms]![MyForm]![EndDate]
    
    Set RS = New ADODB.Recordset
    RS.Open Source:=Cmd, CursorType:=adOpenKeyset, LockType:=adLockReadOnly
    
    Stop
    
    RS.Close
    Set RS = Nothing
    
    Cnn.Close
    Set Cnn = Nothing
    
    Set prmStart = Nothing
    Set prmEnd = Nothing
    
    End Sub

    2018年12月20日 7:11

回答

  • 最初に提示いただいたコードに、不自然な点があるように感じてはいましたが、それはさておき…(Cnn 変数宣言時に無意味な New が指定されていたり、最初に設定しておいた Cmd 変数が後から QueryA のインスタンスで上書きされるなど)

    こちらも色々と試してみたのですが、そもそもパススルークエリーだとパラメーターの受け渡しができないようですね。

    とりあえず代案として:

    1. パススルーは諦めて ODBC リンクテーブルで代用し、Access 側の SQL 構文でパラメータークエリーを用意する。
    2. Oracle 側の構文を使うためにパススルーのままとするが、パラメーターは用いず、値を埋め込んだアドホッククエリーでお茶を濁す。
    3. どうしても Oracle 構文でパラメーターを渡したいので、CurrentProject.Connection から接続する代わりに別の ADODB.Connection インスタンスに対して Provider=MSDASQL あるいは Provider=OraOLEDB.Oracle を指定して開くようにする。
    • 回答としてマーク halu ok 2018年12月21日 9:09
    2018年12月21日 7:04

すべての返信

  • 『WHERE TBL.ASKDY BETWEEN '[StartDate]' AND '[EndDate]'』だと、
    文字列としての "[StartDate]" と比較していることになってしまうでしょう。

    ODBC パススルーの場合、対象となる ODBC Driver によってパラメータ指定の方法が異なりますので、ドライバーのマニュアルもしくはターゲットとなるデータベースの解説書で確認してみてください。

    参考までに、パラメータを指定する場合の構文例を幾つか挙げておきます。(対 Oracle 向けなら、「?」指定のものと「:xxx」指定のものがあったように思います)

    • WHERE TBL.ASKDY BETWEEN ? AND ?
    • WHERE TBL.ASKDY BETWEEN `StartDate` AND `EndDate`
    • WHERE TBL.ASKDY BETWEEN [StartDate] AND [EndDate]
    • WHERE TBL.ASKDY BETWEEN :StartDate AND :EndDate
    • WHERE TBL.ASKDY BETWEEN :"StartDate" AND :"EndDate"
    • WHERE TBL.ASKDY BETWEEN @StartDate AND @EndDate
    2018年12月20日 7:26
  • 魔界の仮面弁士様

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

    情報が少なく、失礼いたしました。ODBCドライバーは「Oracle in OraClient 11g home1」です。

    ご提案いただいたパターン全てと、Where句のパラメータ部分を「prmStart」「prmEnd」に変えてみたパターン、

    またモジュール側の実行部分を配列にしてみましたが、エラーに変化はありませんでした。

    Cmd.Execute Patameters:=(Array(prmStart, prmEnd))

    思い付いた限りを試したつもりでおりますがお手上げ状態です。

    取り急ぎ、ありがとうございました。

    2018年12月20日 8:35
  • 最初に提示いただいたコードに、不自然な点があるように感じてはいましたが、それはさておき…(Cnn 変数宣言時に無意味な New が指定されていたり、最初に設定しておいた Cmd 変数が後から QueryA のインスタンスで上書きされるなど)

    こちらも色々と試してみたのですが、そもそもパススルークエリーだとパラメーターの受け渡しができないようですね。

    とりあえず代案として:

    1. パススルーは諦めて ODBC リンクテーブルで代用し、Access 側の SQL 構文でパラメータークエリーを用意する。
    2. Oracle 側の構文を使うためにパススルーのままとするが、パラメーターは用いず、値を埋め込んだアドホッククエリーでお茶を濁す。
    3. どうしても Oracle 構文でパラメーターを渡したいので、CurrentProject.Connection から接続する代わりに別の ADODB.Connection インスタンスに対して Provider=MSDASQL あるいは Provider=OraOLEDB.Oracle を指定して開くようにする。
    • 回答としてマーク halu ok 2018年12月21日 9:09
    2018年12月21日 7:04
  • 魔界の仮面弁士様

    更にご検証いただいており、ありがとうございます。

    やはり無理だったのか…と今現在ショックであまり考えがまとまりませんが取り急ぎ御礼申し上げます。

    対象のDWHはかなり複雑化しており、このSQLも12のテーブルをJoinし、3000~5000ほどのレコード抽出を目的としておりました。

    リンクテーブルではおそらくとても時間が掛かる予測がたちますので(実際周囲がそういった運用をしている状況です)

    プレーンテキストと、ユーザーがフォームに入力した値からWhere句をつなぎ、

    アドホッククエリを生成してパススルークエリを実行できないか…と思いましたので来週、挑戦してみたいと存じます。

    この問題としては一応の解決を得たこととさせていただきます。

    重ねてありがとうございました。

    2018年12月21日 9:07
  • 事後報告として、他の方法で可能になりましたので追記いたします。

    結局ADOのオブジェクトは使わず、DAOのQueryDefを使うことにし

    Where句の前までのプレーンテキストと、フォームから入力された日付2つをStringのまま繋げてWhere句を生成し、

    それをQueryDefのSQLに指定、実行することで実現しました。

    一週間ほど遠回りをしてしまいましたが、大変勉強になりました。ありがとうございました。

    2018年12月25日 10:02