18 クエリ系 - クエリのSQL文からORDER BY句を収集する

カレントデータベース内にあるクエリのSQL文を探索し、その中から「ORDER BY句」部分を収集します。
  1. まず「Database」オブジェクトの変数である「dbs」に”カレントデータベース”をセットします。

  2. そのデータベース内にあるすべてのクエリのコレクションである「QueryDefs」の中から、For Each〜Nextステートメントでひとつずつクエリ情報を取り出し、「QueryDef」オブジェクトの変数「qdf」にセットしていきます。

  3. VBAで扱うQueryDefsコレクションには、ナビゲーションウィンドウに表示されるクエリの他、フォームのレコードソースやコンボボックス等の値集合ソースプロパティに直接記述されているSQL文なども含まれています。

    それらのクエリは名前の先頭に「~sq_」という4文字が内部的に付加されていますので、ここではそれを判別してIf文で除外します。

    ※もしそれらのクエリも収集対象とするのであれば、このIfの条件式を抹消します。

  4. 取り出されたひとつクエリについて、そのSQL文を「QueryDef」オブジェクトの「SQL」プロパティから取得します。

  5. SQL文内での「ORDER BY」という文字の出現位置を「InStr」関数によって取得し、変数「intPos1」にセットします。

    ORDER BY句を含まない場合、その返り値は”0”となりますので、その場合は収集対象外とします。

  6. 次に、同じく「InStr」関数を使って、ORDER BY句から後ろについて「改行コード(vbCrLf)」の出現位置を取得し、変数「intPos2」にセットします。

    ※よってここでは、SQL文はクエリのデザインビューによって整形されていて、ORDER BY句の途中に改行コードがなく、かつORDER BY句が改行コードで終わっていることが前提となります。

  7. 上記で得られた2つの出現位置 intPos1とintPos2を元に、「Mid$」関数を使って「ORDER BY」から「改行コード」の間にある文字列部分を切り出し、それを”ORDER BY句”としてイミディエイトウィンドウに出力します。

    ※ここではさらに「Replace」関数によって「;(セミコロン)」を空文字に置換することによって除去しています。これはSQL文がORDER BY句で終わっている場合にSQL文の終端を表す「;」は出力しないようにするためです。

Sub Sample_2_08()
'クエリのSQL文からORDER BY句を収集する

  Dim dbs As Database
  Dim qdf As QueryDef
  Dim intPos1 As Integer
  Dim intPos2 As Integer

  Set dbs = CurrentDb
  For Each qdf In dbs.QueryDefs
    With qdf
      If Left$(.Name, 4) <> "~sq_" Then
        intPos1 = InStr(.SQL, "ORDER BY")
        If intPos1 > 0 Then
          intPos2 = InStr(intPos1, .SQL, vbCrLf)
          Debug.Print "■" & .Name
          Debug.Print Replace(Mid$(.SQL, intPos1, intPos2 - intPos1), ";", "")
          Debug.Print "------------------"
        End If
      End If
    End With
  Next qdf

End Sub

実行例:
| Index | Prev | Next |



Copyright © T'sWare All rights reserved