#11
Accessを使ってデータを整理したり加工したりする上で「クエリー」は非常に重要な位置づけにあります。"データベース"と"クエリー(SQL)"とは切っても切れない関係といえるでしょう。とはいえAccessの場合には、テーブルを結合したり、並べ替えたり、抽出したりといった操作は、クエリーの「デザインビュー」の「デザイングリッド」を使えばマウス操作だけで簡単にできてしまいますので、必ずしもSQLの文法の知識は要りません。しかし、ある程度SQLのことが分かってくると、SQL文の一部分だけを修正すればいいような場合にもいちいちマウスを使って、テーブルを追加したり、フィールドを追加したり、抽出条件などを直したりするのは面倒なものです。確かにAccessではそのような場合のために「SQLビュー」というSQL文を直接編集できる画面が用意されているのですが、いくつかのクエリーをまとめて同様の編集を加えたいような場合(例えばテーブルの名前を変更したような場合)には、ビューの切り替えなど、かなり面倒な操作が必要となります。一方、VBAを使ったアプリケーションの処理では、保存されているクエリーの内容を直接書き換えてしまいたいといったこともあるかもしれません。そこでここでは、SQL文の編集や実行中の書き換えを行う際に必要となる、VBAによるクエリーのSQL文の取得と更新方法について説明したいと思います。
 |
|
ここでは、T'sKitの「SQLエディタ」ツールで行っている方法で、データベース内に存在する各クエリーのSQL文を取得し配列にセット、その配列に対してテキストボックスコントロールなどを使って編集を行い、最後にそれらの配列の内容によってデータベース上の各クエリーを更新する、といった処理手順をもとに説明します。
Private pastrQryName() As String |
コードでは、まずフォームモジュールの "Declarations" セクションにクエリー名とSQL文を格納する配列を宣言します。これらの配列は動的配列とし、添え字の最大値は指定しません。ある程度クエリーの数が想定できるような状況では具体的に "pastrQryName(50)" のようにしても構いませんが、アドインツールのような場合にはクエリーがまったくないデータベースから何十ケもあるようなデータベースまで考慮しなければいけませんので、メモリ効率などを考えて動的配列としておきます。 |
|
|
| 続いて、すべてのクエリーのSQL文を取得し、各配列にセットするコードです。 Dim dbs As Database Dim intQryCount As Integer Dim iintLoop As Integer Set dbs = CurrentDb 'クエリー数を取得 intQryCount = dbs.QueryDefs.Count ReDim pastrQryName(intQryCount) ReDim pastrSQL(intQryCount) pintArryNum = 0 'すべてのクエリーを列挙するループ For iintLoop = 1 To intQryCount With dbs.QueryDefs(iintLoop - 1) If (.Type And &HF) = 0 Then 'クエリー名を配列にセット pastrQryName(pintArryNum) = .Name 'SQL文を配列にセット pastrSQL(pintArryNum) = .SQL '編集対象となるクエリー数をインクリメント pintArryNum = pintArryNum + 1 End If End With Next iintLoop |
このコードでは、まずデータベース上に存在するすべての「クエリー」の集まり(コレクション)を示す "QueryDefs" の [Count]プロパティを使ってクエリーの総数を取得し、変数 intQryCount にセットします。この変数によって、先に宣言した動的配列のサイズ(添え字の最大値)を決定したり、すべてのクエリーを列挙するループを構成します。(実はこのような場合、ここでReDimを使わず、次のループ内で ReDim Preserve というキーワードを使う方法もあります) そして、すべてのクエリーを列挙するループでは、各クエリーの[Type]プロパティがゼロであるクエリーだけを対象とします("(.Type And &HF) = 0 "の部分)。これはすでに #01 ＭＤＢオブジェクト一覧を取得する でも説明していますが、フォームなどの[レコードソース]プロパティにクエリー名ではなくSQL文が指定されている場合の隠し?クエリーを除外するためのものです。このような例外的なクエリーがある場合、[Count]プロパティで得られるクエリー数と、実際にユーザーがSQL文を編集するようなクエリー、つまりデータベースウィンドウに表示されるクエリー数との間には相違が出てきます。そのため、編集対象となるクエリー数については、ループ内で 変数pintArryNum をインクリメントすることによって数えています。この変数は最後に編集されたSQL文を保存する際にも使われますが、その処理が左記のコードとは別のイベントプロシージャ([保存]ボタンのクリック時イベントなど)で行われるため、この変数を"Declarations" セクションで宣言しています。 また、このコードで今回もっともポイントとなる部分は、QueryDefオブジェクトの [SQL]プロパティです。このプロパティにSQL文のすべてがそのまま格納されています。 |
|
|
上記のコードで配列に格納されたSQL文は、任意の方法で編集することができます。T'sKitの「SQLエディタ」ツールでは、テキストボックスに順番に表示してマニュアルで編集できるようにしてありますが、VBAのコード内で一括して編集することもできます。そして、それらの方法によって編集されたSQL文を対応するクエリーに書き込んで、保存済みクエリーを更新するコードが次の例です。 Dim dbs As Database Dim iintLoop As Integer Set dbs = CurrentDb '対象クエリーのループ For iintLoop = 0 To pintArryNum - 1 '編集されているSQL文でクエリーを更新 dbs.QueryDefs(pastrQryName(iintLoop)).SQL = _ pastrSQL(iintLoop) Next iintLoop dbs.QueryDefs.Refresh |
ここでは、フォームの "Declarations"セクション にPrivateで宣言された対象クエリー数 pintArryNum を使ってループ処理します。"QueryDefs(pastrQryName(iintLoop))" の部分は多少分かりづらいかもしれませんが、あらかじめ配列 pastrQryName() にはクエリー名が保存されていますので、この部分は QueryDefs("クエリー1") のような感じになります。つまり、「クエリー」の集まりである "QueryDefs"コレクション の中の "クエリー1" を直接指し示していることになります。そして、取得時と同様に[SQL]プロパティに編集後のSQL文を代入すればクエリーを更新することができます。 |
