none
Access adpから一時テーブルのレコードを認識できない RRS feed

  • 質問

  • いつもお世話になります。oeameu3sngpです。
    環境はSQLServer2008 R2 Express Access2010 adp です。

    パラメータとして受け取った件数から次の30件のレコードを返すストアドを作りました。受け取った値が総レコード件数に達している場合はグローバル一時テーブルを利用して最大値に達したメッセージをストアドの呼び出し元に返し、レコードとしては最初の30件を返すようにしています。

    今、最大レコード件数が100件の時、100をパラメータとして渡たすと、ストアド側では最初の30件を返すと同時に一時テーブルにも指定のメッセージを書き込んでくれています。ところが、呼び出し元の方には最初の30件のレコードは返って来るのですが、一時テーブルにinsertされたレコードを認識してくれません。(レコード件数0件となります)
    ところが、続けてストアドを実行すると、指定したパラメータにかかわらず、存在しないはずの最大値に達したメッセージを一時テーブルから取得できてしまいます。実際に返って来るレコードはパラメータで指定したところから30件が帰ってきます。

    結果的に返って来るレコードとレコードの最大値に達したメッセージが処理1回分ずれる現象になります。
    ストアド側では想定した通りの動きになることはSSMSで確認できているのですが…。
    どなたかご助言頂ける方がお見えでしたらよろしくお願い致します。


    ====== 呼び出し元の処理 ===========
     ' /// ストアドの実行
      strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
              & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"

       If Not IsNull(DLookup("myMess", "##_Info")) = True Then
          MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る"
          Me.RecCnt = 30     ===>>>> フォームに持っている現在の最終レコード件数の値です。
       Else
          Me.RecCnt = Me.RecCnt + intRecCnt =====>>>> フォームの最終レコード件数を更新
       End If

    ====== ストアドの一部抜粋 =========
    -- 情報受け渡し用一時テーブル作成
        if ( object_id('tempdb..##_Info') is not null )
      begin
       truncate table ##_Info; 
      end
     else
      begin
       create table ##_Info(
             myMess nvarchar(30) primary key
          );
      end
      
     -- end of if
     
        -- レコード件数取得
        declare mycur cursor
        for
        select COUNT(*)as cnt
     from dbo.T_商品M
     where 得意先=@User;
     open mycur
     fetch next from mycur into @MaxRCnt
     DEALLOCATE mycur
     
     if @RecCnt >= @MaxRCnt
      begin
       set @Start=1
       set @End = 30
       insert into ##_Info values('レコードが無くなったので最初の30件を表示します')

     この後にレコードの抽出処理が続きます。

    2011年8月25日 6:37

回答

  • SQL Serverの既定の分離レベルはRead Commitedです。Accessには関してはわかりませんが、いずれにしても今回の件は分離レベルとは無関係のようです。
    まず、VBAではトランザクションを切っていませんし、同時に2つのトランザクションが発生しているわけではありませんから、分離レベルによる制御は発生しません。
    また、以下のコードですが、②のところで初めてストアドプロシージャが実行されます。よって、実行する前におそらくリンクテーブルであろう##_Infoを参照しても、今回のストアドプロージャを実行する前の値が入っていることになります。では、##_Infoには何の値が入っているかと言えば、前回②を実行した際の値が入っていることになります。すなわち一つ前の値です。

       ' /// lst商品にソースを設定する
    strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
    & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"

    If Not IsNull(DLookup("myMess", "##_Info")) = True Then
    MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る" ・・・・・・①
    Me.RecCnt = 30
    Else
    Me.RecCnt = Me.RecCnt + intRecCnt
    End If

    Me.lst商品.RowSource = strSQL ・・・・・②

    よって、単純に以下のように実行順序を入れ替えれば、うまく動くような気がします。

       ' /// lst商品にソースを設定する
    strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
    & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"

    Me.lst商品.RowSource = strSQL

    If Not IsNull(DLookup("myMess", "##_Info")) = True Then
    MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る"
    Me.RecCnt = 30
    Else
    Me.RecCnt = Me.RecCnt + intRecCnt
    End If

    (追記)ちなみに掲載されている「動作した」コードは、##_Infoを参照する前にstrSQLを実行しているからだと思います。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 編集済み trapemiya 2011年8月25日 13:39 追記
    • 回答としてマーク oeameu3sngp 2011年8月26日 1:25
    2011年8月25日 13:33
  • トランザクションが ReadCommited で、adp 側で 30 件のレコードを取り出した後で Close する前に ##_Info を読もうとしていませんか?  

    1. ストアドを呼び出し、レコードセットの読み取りを開始
    2. 30件を読み出す
    3. レコードセットを閉じる
    4. ##_info に格納された件数を取得する

    であれば、問題ないのですが、1 → 2 → 4 → 3 の順で作業されていると、書かれているように1回遅れで取得できるような動きになりそうです。

    • 回答としてマーク oeameu3sngp 2011年8月25日 8:35
    2011年8月25日 6:51

すべての返信

  • トランザクションが ReadCommited で、adp 側で 30 件のレコードを取り出した後で Close する前に ##_Info を読もうとしていませんか?  

    1. ストアドを呼び出し、レコードセットの読み取りを開始
    2. 30件を読み出す
    3. レコードセットを閉じる
    4. ##_info に格納された件数を取得する

    であれば、問題ないのですが、1 → 2 → 4 → 3 の順で作業されていると、書かれているように1回遅れで取得できるような動きになりそうです。

    • 回答としてマーク oeameu3sngp 2011年8月25日 8:35
    2011年8月25日 6:51
  • K. Takaoka こんにちは初めまして。
    投稿したVBA側のコードも含めて情報提示に抜けがありました。
    ストアドからの戻り値はリストボックスのソースに指定していること、その指定方法が Me.lst商品.RowSource = strSQL であることです。
    なのでK. Takaokaさんが仰る

    > トランザクションが ReadCommited で、adp 側で 30 件のレコードを取り出した後で Close する前に ##_Info を読もうとしていませんか?

    に、どう対処したら良いのだろうか?と思いましたが、ストアドの戻り値をレコードセットに受け取り、それをリストボックスのレコードセットプロパティー にセットすることで、思い通りの結果を実現することが出来ました。と言うことはadpとSQLServerのデフォルトの分離レベルはReadCommitedで接続されている事になるのでしょうか…。

    難しい事は解りませんが、勉強になりました。有難うございます。
    あらためて、動作しなかったコードと動作したコードを載せておきます。


    ========= 動作しなかった ==============
    Private Sub cmdNext_Click()
    ' /// 2011/8/25
    ' /// +30件で次のレコードを検索する。

       Dim strSQL As String
      
       If IsNull(Me.lst得意先) = True Then
          MsgBox "得意先が選択されていません", vbExclamation, "エラー"
          Exit Sub
       End If
      
       ' /// lst商品にソースを設定する
       strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
              & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"
             
       If Not IsNull(DLookup("myMess", "##_Info")) = True Then
          MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る"
          Me.RecCnt = 30
       Else
          Me.RecCnt = Me.RecCnt + intRecCnt
       End If
      
       Me.lst商品.RowSource = strSQL

    End Sub

     

    ========= 動作した ==============

    Private Sub cmdNext_Click()
    ' /// 2011/8/25
    ' /// +30件で次のレコードを検索する。

       Dim cn As ADODB.Connection
       Dim rs As New ADODB.Recordset
       Dim strSQL As String
      
       If IsNull(Me.lst得意先) = True Then
          MsgBox "得意先が選択されていません", vbExclamation, "エラー"
          Exit Sub
       End If
      
       Set cn = CurrentProject.Connection
      
       ' /// lst商品にソースを設定する
       strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
              & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"
             
       rs.Open strSQL, cn, adOpenDynamic, adLockReadOnly, adCmdText
             
       MsgBox "RecCntは " & Me.RecCnt
       MsgBox DCount("*", "##_Info")
             
       If Not IsNull(DLookup("myMess", "##_Info")) = True Then
          MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る"
          Me.RecCnt = 30
       Else
          Me.RecCnt = Me.RecCnt + intRecCnt
       End If
      
       Set Me.lst商品.Recordset = rs
      
       rs.Clone: Set rs = Nothing
       cn.Close: Set cn = Nothing

    End Sub

    2011年8月25日 8:36
  • SQL Serverの既定の分離レベルはRead Commitedです。Accessには関してはわかりませんが、いずれにしても今回の件は分離レベルとは無関係のようです。
    まず、VBAではトランザクションを切っていませんし、同時に2つのトランザクションが発生しているわけではありませんから、分離レベルによる制御は発生しません。
    また、以下のコードですが、②のところで初めてストアドプロシージャが実行されます。よって、実行する前におそらくリンクテーブルであろう##_Infoを参照しても、今回のストアドプロージャを実行する前の値が入っていることになります。では、##_Infoには何の値が入っているかと言えば、前回②を実行した際の値が入っていることになります。すなわち一つ前の値です。

       ' /// lst商品にソースを設定する
    strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
    & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"

    If Not IsNull(DLookup("myMess", "##_Info")) = True Then
    MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る" ・・・・・・①
    Me.RecCnt = 30
    Else
    Me.RecCnt = Me.RecCnt + intRecCnt
    End If

    Me.lst商品.RowSource = strSQL ・・・・・②

    よって、単純に以下のように実行順序を入れ替えれば、うまく動くような気がします。

       ' /// lst商品にソースを設定する
    strSQL = "exec dbo.商品マスタ照会レコード抽出 " _
    & "@RecCnt=" & Me.RecCnt & ",@User='" & Me.lst得意先 & "'"

    Me.lst商品.RowSource = strSQL

    If Not IsNull(DLookup("myMess", "##_Info")) = True Then
    MsgBox DLookup("myMess", "##_Info"), vbInformation, "最初に戻る"
    Me.RecCnt = 30
    Else
    Me.RecCnt = Me.RecCnt + intRecCnt
    End If

    (追記)ちなみに掲載されている「動作した」コードは、##_Infoを参照する前にstrSQLを実行しているからだと思います。

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 編集済み trapemiya 2011年8月25日 13:39 追記
    • 回答としてマーク oeameu3sngp 2011年8月26日 1:25
    2011年8月25日 13:33
  • trapemiyaさんおはようございます。

    懇切丁寧に当方のアホさ加減を解説頂き有難うございました。笑

    # 笑うてる場合ちゃいますけど…

    > ②のところで初めてストアドプロシージャが実行されます。

    を読んだところで、汗が出てきました。
    と言うことで結果は改めて書きません。有難うございました。

    話題は逸れますが、トランザクションも難しいですね。
    どんな処理のところでトランザクションを意識すべきなのか…。
    ぼちぼち勉強したいと思います。

    2011年8月26日 1:26