#704 既存クエリのSQL文を流用して一時的クエリを実行する方法 クエリ、VBA

データベース内にすでに保存されているアクションクエリのSQL文をテンプレートのように扱い、そのSQL文を取得・加工したうえで実行させる方法です。

それには、クエリオブジェクトである「QueryDef」に元となるクエリの定義を代入し、そこからSQL文を表す「SQL」プロパティ値を取得、その文字列を任意に加工後、「CreateQueryDef」メソッドを使って一時的なクエリを生成し、VBA上で実行します。

ここでのポイントとして、「元となっているクエリの内容を更新したあと書き戻し、そのクエリ自体をDoCmd.OpenQueryで実行する」という方法でもよいのですが、ここでは「SQL文の取得 → 書き換え → 一時クエリの生成 → 実行」という”一方通行の処理”としています。つまり、書き換えられたSQL文はあくまでもVBA上だけの一時的なものであり、この処理によって元のクエリの内容が変わることはありません。

■元となるクエリ「qmak商品データ」のデザイン


■このクエリのSQL文

SELECT 商品マスタ.商品コード INTO 商品データ
FROM 商品マスタ;

このクエリは、「商品マスタ」テーブルを元に「商品データ」というテーブルを新規作成する”テーブル作成クエリ”です。その際の出力フィールドは「商品コード」のみとなっています。


■引数によってテーブル作成クエリで出力するフィールドを切り替えるサンプルプロシージャ

Public Sub MakeProductData(intType As Integer)

  Dim dbs As Database
  Dim qdf As QueryDef
  Dim strSQL As String
  Dim strInto As String

  Set dbs = CurrentDb

  'テーブル作成クエリのSQL文からINTO句以降の文字列を切り出し
  Set qdf = dbs.QueryDefs("qmak商品データ")
  With qdf
    strInto = Mid$(.SQL, InStr(.SQL, "INTO"))
    .Close
  End With

  '引数に応じて出力するフィールドを切り替え
  Select Case intType
    Case 1
      strSQL = "SELECT 商品コード, 商品名 " & strInto
    Case 2
      strSQL = "SELECT 商品コード, 商品名, 販売単価 " & strInto
    Case 3
      strSQL = "SELECT 商品コード, 商品名, 仕入単価, 単位 " & strInto
  End Select

  '既存テーブルを削除
  On Error Resume Next
  DoCmd.DeleteObject acTable, "商品データ"
  On Error GoTo 0

  '一時的なクエリを作成して実行
  Set qdf = dbs.CreateQueryDef("", strSQL)
  With qdf
    .Execute
    .Close
  End With

End Sub

※テーブル作成クエリでは、作成しようとしているテーブルがすでにあるとエラーになります。そのため「DoCmd.DeleteObject」によって事前に削除しています。さらに、初めて実行する場合はそのテーブルがないので、「DoCmd.DeleteObject」自体がエラーとなってしまいます。そのため「On Error Resume Next」でエラーを無視するようにしています。


実行例:
    ※各図は上記プロシージャ実行時に作成された「商品データ」テーブルの内容を表しています。
  • 引数に「1」を指定したとき・・・・「MakeProductData 1」


  • 引数に「2」を指定したとき・・・・「MakeProductData 2」


  • 引数に「3」を指定したとき・・・・「MakeProductData 3」
| Index | Prev | Next |



T'sFactory
Accessで動く生産管理DB
Ureru Express
Webで使う販売顧客管理
Access開発&アドバイス
DB開発やテクニカルアドバイス
Copyright © T'sWare All rights reserved