none
Access・VBAでクエリの連続実行 RRS feed

  • 質問

  • <環境>
     OS:Win7 64x
     Access2016上で、Access2002で開発したmdbを使用している

    <問題になっていること>
    クエリの実行を連続して処理している部分で
    直前のクエリが終わらないうちに、次のクエリが実行されている。
    クエリの実行が終わってから、次のクエリを実行するように改善したい

    <詳細>
    テーブル
     テーブルA、テーブルB、テーブルC、実行クエリ一覧テーブル、その他テーブルあり

    クエリ
     下記「実行クエリ一覧テーブル」参照
     ~削除クエリはDelete文
     ~更新クエリはUpdate文
     ~追加クエリはInsert文
     のSQLが書かれたクエリが保存されている状態

    実行クエリ一覧テーブル
     実行順序(数値型)|グループ|クエリ名
     1|編集|テーブルA削除クエリ
     2|編集|テーブルA追加クエリ
     3|編集|テーブルA更新クエリ1
     4|編集|テーブルA更新クエリ2
     5|編集|テーブルA更新クエリ3
     6|編集|テーブルB削除クエリ
     7|編集|テーブルB追加クエリ
     8|編集|テーブルC追加クエリ
     9|編集|テーブルC更新クエリ1
     …

    上記のような、実行したいクエリの名前が書かれているテーブルがあります。
    このテーブルに書かれたクエリを、実行順序に従って実行するために、
    フォームのボタンを押すと、Syori1が実行されるようになっています。

    Private sub Syori1()

        Dim str_SQL             As String
        Dim cn                  As ADODB.Connection     
        Dim rs                  As ADODB.Recordset      

        'マウスポインタを砂時計
        Screen.MousePointer = 11
           
        '警告メッセージOFF
        DoCmd.SetWarnings False
        
        Set cn = Application.CurrentProject.Connection
       
        'SQLを作成する
        '実行クエリ一覧テーブルに登録されている内容を、実行順に取得する。
        str_SQL = "             SELECT 実行クエリ一覧.*"
        str_SQL = str_SQL & "     FROM 実行クエリ一覧"
        str_SQL = str_SQL & "    WHERE (((実行クエリ一覧.[グループ])='編集'))"
        str_SQL = str_SQL & " ORDER BY 実行クエリ一覧.実行順序;"
        
        Set rs = cn.Execute(str_SQL)        
        rs.MoveFirst
      
        '実行クエリ一覧テーブルの、実行順に従ってクエリを実行する。
        Do Until rs.EOF = True

            Dim qdf As QueryDef
            
            Set qdf = Application.CurrentDb.QueryDefs(rs("クエリ名"))
           
            'クエリ実行
            qdf.Execute
            
            rs.MoveNext

        Loop
           
        rs.Close
        cn.Close
        Set rs = Nothing
        Set cn = Nothing

        'マウスポインタを初期値に
        Screen.MousePointer = 0

        '警告メッセージON
        DoCmd.SetWarnings True

        '(wait)

        '最後にデータを出力
        DoCmd.OutputTo acOutputTable, "テーブルB", "MicrosoftExcelBiff8(*.xls)", "D:\temp\テーブルB.xls"

        MsgBox "処理完了、テーブルBを確認してください。"

    End Sub

    問題の部分ですが、
    テーブルB.xlsが更新されていない状態で出力されました。
    また
    ・Syori1実行ではなく、手動でクエリを順番に実行すると正常にテーブルB.xlsが更新された状態で出力できる。
    ・(wait)部分で5秒待ってから処理を進めたところテーブルB.xlsが正常に更新された状態で出力できる。

    テーブルのレコード数は1000件未満のこともあれば、10,000件近いこともある。

    qdf.Executeで実行しているクエリが、完了しないまま次のクエリを実行しているようなのですが、
    どうすれば、改善されるでしょうか。

    どうぞよろしくお願いいたします。

    2018年8月5日 1:46

回答

  • ADO と DAO が混在してますね。

    どちらかに統一しましょう。

    2018年8月5日 8:38
  • qdf.Executeで実行しているクエリが、完了しないまま次のクエリを実行しているようなのですが、

    waitの位置から言って、クエリの実行順序に影響を及ぼす位置ではないと思いますが、他に省略しているコードはないでしょうか?Excecuteは実行が完了するまでそこで待つはずです。クエリに戻り値があれば、それを受け取らなければなりませんし。よって、実行が完了しないうちに次のVBAが実行されることはないはずです。もしそうでないのであれば、マルチタスクで動くことになってしまいます。
    また、通常は一度に複数のテーブルを操作する場合、トランザクションを用いますが、今回の場合は必要ないのでしょうか?トランザクションが終われば一連のテーブルが更新されていることが保証されます。


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2018年8月6日 1:37
    モデレータ
  • DAO 接続にせよ ADO 接続にせよ、BeginTrans / CommitTrans は必ず行ってください。

    トランザクション指定が無い場合には即時反映されるものと誤解されがちですが、実際にはこれを怠ると非同期書き込みモードが利用されるため、実行結果が即時反映されずに、0.5秒程度の書き込み遅延が発生する可能性があります。
    (単一接続時には問題となりませんが、今回のように複数の接続がある場合には問題になりやすいです)

    さらに、DoCmd.OutputTo の 直前に「DBEngine.Idle dbRefreshCache」を呼び出して DAO 側のリードキャッシュを更新してください(もしくはレジストリで PageTimeout 値を調整する)。何もしない場合、リードキャッシュの監視間隔は 5000 ミリ秒です。

    また既に指摘がありますが、接続数を減らすためにも、CurrentDb 接続(DAO) / CurrentProject.Connection 接続(ADO) のいずれかに統一されることをお奨めします。(今回のケースでは DAO 接続をお奨めします)

    > Set cn = Application.CurrentProject.Connection
    > Set qdf = Application.CurrentDb.QueryDefs(rs("クエリ名"))
    これらは「Application.」な表記を採用していて、
    > Screen.MousePointer = 0
    > DoCmd.SetWarnings True
    こちらは「Application.Screen」「Application.DoCmd」では無いのですね。

    2018年8月6日 7:35

すべての返信

  • ADO と DAO が混在してますね。

    どちらかに統一しましょう。

    2018年8月5日 8:38
  • ご指摘ありがとうございます。修正してみます。

    2018年8月5日 15:28
  • qdf.Executeで実行しているクエリが、完了しないまま次のクエリを実行しているようなのですが、

    waitの位置から言って、クエリの実行順序に影響を及ぼす位置ではないと思いますが、他に省略しているコードはないでしょうか?Excecuteは実行が完了するまでそこで待つはずです。クエリに戻り値があれば、それを受け取らなければなりませんし。よって、実行が完了しないうちに次のVBAが実行されることはないはずです。もしそうでないのであれば、マルチタスクで動くことになってしまいます。
    また、通常は一度に複数のテーブルを操作する場合、トランザクションを用いますが、今回の場合は必要ないのでしょうか?トランザクションが終われば一連のテーブルが更新されていることが保証されます。


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2018年8月6日 1:37
    モデレータ
  • DAO 接続にせよ ADO 接続にせよ、BeginTrans / CommitTrans は必ず行ってください。

    トランザクション指定が無い場合には即時反映されるものと誤解されがちですが、実際にはこれを怠ると非同期書き込みモードが利用されるため、実行結果が即時反映されずに、0.5秒程度の書き込み遅延が発生する可能性があります。
    (単一接続時には問題となりませんが、今回のように複数の接続がある場合には問題になりやすいです)

    さらに、DoCmd.OutputTo の 直前に「DBEngine.Idle dbRefreshCache」を呼び出して DAO 側のリードキャッシュを更新してください(もしくはレジストリで PageTimeout 値を調整する)。何もしない場合、リードキャッシュの監視間隔は 5000 ミリ秒です。

    また既に指摘がありますが、接続数を減らすためにも、CurrentDb 接続(DAO) / CurrentProject.Connection 接続(ADO) のいずれかに統一されることをお奨めします。(今回のケースでは DAO 接続をお奨めします)

    > Set cn = Application.CurrentProject.Connection
    > Set qdf = Application.CurrentDb.QueryDefs(rs("クエリ名"))
    これらは「Application.」な表記を採用していて、
    > Screen.MousePointer = 0
    > DoCmd.SetWarnings True
    こちらは「Application.Screen」「Application.DoCmd」では無いのですね。

    2018年8月6日 7:35
  • trapemiyaさま

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

    >省略しているコード
     Syori1にまとめてしまいましたが、
     実際にはテーブルBの出力は別ボタンで出力しており、
     Syori1は警告メッセージONのあと、「編集完了」のメッセージで終了
     Syori2ボタンで
      '最後にデータを出力
        DoCmd.OutputTo acOutputTable, "テーブルB", "MicrosoftExcelBiff8(*.xls)", "D:\temp\テーブルB.xls"
     「出力完了」のメッセージ を出しています。

     それと、on error goto でエラーのキャッチをしています。

    トランザクションも追加してみます。


    2018年8月6日 22:44
  • 魔界の仮面弁士さま

    回答ありがとうございます。お二人からお話頂いたトランザクションについて、修正してみます。

    その他の箇所についても、親切に丁寧にご指導いただきまして、ありがとうございます。トラブルが無かったとしても、このプログラムは問題だらけ妙なソースだったのですね。お恥ずかしい限りです。皆さんにご指摘いただいた点について、修正してみます。

    2018年8月6日 22:45