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

Question

＜環境＞
　OS：Win7　64x
　Access2016上で、Access2002で開発したmdbを使用している

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

＜詳細＞
テーブル
　テーブルＡ、テーブルＢ、テーブルＣ、実行クエリ一覧テーブル、その他テーブルあり

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

実行クエリ一覧テーブル
　実行順序(数値型)｜グループ｜クエリ名
　1｜編集｜テーブルＡ削除クエリ
　2｜編集｜テーブルＡ追加クエリ
　3｜編集｜テーブルＡ更新クエリ１
　4｜編集｜テーブルＡ更新クエリ２
　5｜編集｜テーブルＡ更新クエリ３
　6｜編集｜テーブルＢ削除クエリ
　7｜編集｜テーブルＢ追加クエリ
　8｜編集｜テーブルＣ追加クエリ
　9｜編集｜テーブルＣ更新クエリ１
　…

上記のような、実行したいクエリの名前が書かれているテーブルがあります。
このテーブルに書かれたクエリを、実行順序に従って実行するために、
フォームのボタンを押すと、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, "テーブルＢ", "MicrosoftExcelBiff8(*.xls)", "D:\temp\テーブルＢ.xls"

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

End Sub

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

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

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

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

Answer 1

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

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

Answer 2

ご指摘ありがとうございます。修正してみます。

Answer 3

＞qdf.Executeで実行しているクエリが、完了しないまま次のクエリを実行しているようなのですが、

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

---

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

Answer 4

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」では無いのですね。

Answer 5

trapemiyaさま

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

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

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

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

Answer 6

魔界の仮面弁士さま

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

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