Access Study Vol.19 データベース解析・収集サンプルコード集（16）

カレントデータベース内にあるクエリのSQL文を探索し、その中から「FROM句」部分を収集します。

まず「Database」オブジェクトの変数である「dbs」に”カレントデータベース”をセットします。
そのデータベース内にあるすべてのクエリのコレクションである「QueryDefs」の中から、For Each～Nextステートメントでひとつずつクエリ情報を取り出し、「QueryDef」オブジェクトの変数「qdf」にセットしていきます。
VBAで扱うQueryDefsコレクションには、ナビゲーションウィンドウに表示されるクエリの他、フォームのレコードソースやコンボボックス等の値集合ソースプロパティに直接記述されているSQL文なども含まれています。

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

※もしそれらのクエリも収集対象とするのであれば、このIfの条件式を抹消します。
取り出されたひとつクエリについて、そのSQL文を「QueryDef」オブジェクトの「SQL」プロパティから取得します。
SQL文内での「FROM」という文字の出現位置を「InStr」関数によって取得し、変数「intPos1」にセットします。

FROM句を含まない場合、その返り値は”0”となりますので、その場合は収集対象外とします。
次に、同じく「InStr」関数を使って、FROM句から後ろについて「改行コード（vbCrLf）」の出現位置を取得し、変数「intPos2」にセットします。

※よってここでは、SQL文はクエリのデザインビューによって整形されていて、FROM句の途中に改行コードがなく、かつFROM句が改行コードで終わっていることが前提となります。
上記で得られた２つの出現位置 intPos1とintPos2を元に、「Mid$」関数を使って「FROM」から「改行コード」の間にある文字列部分を切り出し、それを”FROM句”としてイミディエイトウィンドウに出力します。

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

Sub Sample_2_06()
'クエリのSQL文からFROM句を収集する

  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, "FROM")
        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


16		クエリ系 - クエリのSQL文からFROM句を収集する
カレントデータベース内にあるクエリのSQL文を探索し、その中から「FROM句」部分を収集します。まず「Database」オブジェクトの変数である「dbs」に”カレントデータベース”をセットします。そのデータベース内にあるすべてのクエリのコレクションである「QueryDefs」の中から、For Each～Nextステートメントでひとつずつクエリ情報を取り出し、「QueryDef」オブジェクトの変数「qdf」にセットしていきます。 VBAで扱うQueryDefsコレクションには、ナビゲーションウィンドウに表示されるクエリの他、フォームのレコードソースやコンボボックス等の値集合ソースプロパティに直接記述されているSQL文なども含まれています。それらのクエリは名前の先頭に「~sq_」という４文字が内部的に付加されていますので、ここではそれを判別してIf文で除外します。 ※もしそれらのクエリも収集対象とするのであれば、このIfの条件式を抹消します。取り出されたひとつクエリについて、そのSQL文を「QueryDef」オブジェクトの「SQL」プロパティから取得します。 SQL文内での「FROM」という文字の出現位置を「InStr」関数によって取得し、変数「intPos1」にセットします。 FROM句を含まない場合、その返り値は”0”となりますので、その場合は収集対象外とします。次に、同じく「InStr」関数を使って、FROM句から後ろについて「改行コード（vbCrLf）」の出現位置を取得し、変数「intPos2」にセットします。 ※よってここでは、SQL文はクエリのデザインビューによって整形されていて、FROM句の途中に改行コードがなく、かつFROM句が改行コードで終わっていることが前提となります。上記で得られた２つの出現位置 intPos1とintPos2を元に、「Mid$」関数を使って「FROM」から「改行コード」の間にある文字列部分を切り出し、それを”FROM句”としてイミディエイトウィンドウに出力します。 ※ここではさらに「Replace」関数によって「;（セミコロン）」を空文字に置換することによって除去しています。これはSQL文がFROM句で終わっている場合にSQL文の終端を表す「;」は出力しないようにするためです。 Sub Sample_2_06() 'クエリのSQL文からFROM句を収集する 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, "FROM") 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 \|