none
ストアドとの連携 RRS feed

  • 質問

  • ご教授願います。

     現在SQLserver2008R2でストアドを作成し
     エクセルvbaから実行させるマクロを
     作成しました。
     マクロ例は以下の通りです。(一部割愛しています。)
    --------------------------------------------

     Sub btn実行表示_Click()

            Dim cnn As New ADODB.Connection
            Dim cmd As New ADODB.Command
            Dim rec As New ADODB.Recordset
           
            Dim date1 As Date
            Dim date2 as Date
           
            Dim 担当者 as string       
           
            Dim par0 As ADODB.Parameter
            Dim par1 As ADODB.Parameter  
        Dim par2 As ADODB.Parameter
            Dim par3 As ADODB.Parameter             
           
            date1 = Range("B7").Value
            date2 = "1901/01/01"       
            担当者 = "user"       
           
            cnn.ConnectionString = "Provider=SQLOLEDB;" _
                              & "Data Source=***;" _
                              & "Initial Catalog=***;" _
                              & "Integrated Security=SSPI"
                             
            cnn.Open
           
            Set cmd.ActiveConnection = cnn
            cmd.CommandText = "ストアド名"
            cmd.CommandType = adCmdStoredProc
           
            Set par0 = cmd.CreateParameter("@部門コード", adSmallInt, adParamInput) '部門コード
            cmd.Parameters.Append par0
            par0.Value = Range("B1").Value       
            Set par1 = cmd.CreateParameter("@年月", adDBDate, adParamInput)  '年月
            cmd.Parameters.Append par1
            par1.Value = date1       
            Set par2 = cmd.CreateParameter("@次月年月", adDBDate, adParamInput) '次月年月
            cmd.Parameters.Append par2
            par2.Value = date2      
            Set par3 = cmd.CreateParameter("@担当者", adVarChar, adParamInput, 16) '担当者
            cmd.Parameters.Append par3
            par3.Value = 担当者   
                           
            'rec.open cmdが使えなかったので直接パラメータを書き込みました。
      ①   rec.Open "ストアド名 " & par0 & "'" & par1 & "'" & "," & "'" & par2 & "'" & "," & "'" & par3 & "'" , cnn, adOpenForwardOnly, adLockReadOnly
            Set cmd = Nothing
                   
            rec.Close
            Set rec = Nothing       

    end sub

    ---------------------------------------
    しかし、①の部分を
    rec.Open cmd, , adOpenForwardOnly, adLockReadOnly
    とすると
    構文エラーアクセス違反です。のメッセージが表示されます。
    よって、①のようにストアドパラメータを直接書いたら
    動作しました。
    ①のcmdがおかしいとは思うのですが
    他のマクロはこの方法で動作しています。(違うストアドを呼び出しです。)

    原因等をご回答頂けたら幸いです。

    以上

    2012年2月22日 5:25

回答

  • OLEDBなので、パラメータをAppendする順番を、ストアドプロシージャのパラメータの順番に一致させる必要があるのかもしれません。ただ、ご提示されたソースを見る限り大丈夫そうな気がします・・・
    また、ストアドプロシージャを実行するだけであれば、cmd.Execute だけでよいのではないでしょうか?


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク zeak01 2012年2月29日 7:30
    2012年2月23日 1:41
    モデレータ

すべての返信

  • 他のマクロで正常に動作しているとのことですが、同じ構文のパターンで書かれているのでしょうか?
    また、rec.Open cmd.Execute と書かれてみてはいかがでしょうか?


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    2012年2月22日 6:42
    モデレータ
  •  ご回答ありがとうございます。

    他のマクロについては、①までの構文はほぼ同じです。(パラメータの数が違います)

    ただ、動作する構文はストアドからの結果を、別のシートに書き込む処理を続いて記述しています。

    今回の投稿マクロは、ストアドへパラメータを渡したら完結する処理です。

    また、rec.open cmd.Executeについても同様なエラーでだめでした。

    2012年2月22日 8:16
  • OLEDBなので、パラメータをAppendする順番を、ストアドプロシージャのパラメータの順番に一致させる必要があるのかもしれません。ただ、ご提示されたソースを見る限り大丈夫そうな気がします・・・
    また、ストアドプロシージャを実行するだけであれば、cmd.Execute だけでよいのではないでしょうか?


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク zeak01 2012年2月29日 7:30
    2012年2月23日 1:41
    モデレータ
  • par0 と par1 の間にカンマ (',') がないのは関係あるでしょうか?
    単なるコードのコピー漏れでしょうか?
    2012年2月23日 3:20