AccessでクエリオブジェクトのSQL文を取得→一部変更→戻して実行する

AccessでクエリオブジェクトのSQL文を取得→一部変更→戻して実行する

SQLをまるごと書き換えるんじゃなくて、既存クエリのSQLを一部だけ変更したいと思って試したメモです。とてもニッチだとは思っています。


背景

ブログでも書籍でも、Accessに対してSQLを自分で組み立てて投げる、ということを色んなところで書いたりしてきていた私ですが、クエリというクエリを全部SQL直書きしているかと言われると、全然そんなことはありません。

Accessでクエリオブジェクトを用意して、その条件部分をフォームのテキストボックスの値にしておけばユーザーの入力値が反映できます。それをVBAで

DoCmd.OpenQuery "クエリ名"

と、走らせるのも結構便利なものです。適材適所でいろんな方法で書いています。

そんななか、今回ぶち当たったケースのおはなしなのですが。

これまでは上記の書き方で実装していたものの、仕様変更があってWHEREがフォームのテキストボックスで賄えなくなってしまいました。For文使わなきゃいけないっぽい。ここまでくると従来どおりの方法だとちょっと厳しい。

じゃあSQL全部VBA上で書いて処理しよっかなーと考えてみたものの、文の前半部分がAccess関数を多用しててVBA上にもってくるのがしんどい…!!!!(文字列のエスケープがかなりめんどい!!!)(あと単純に長い!!!)

変えたいのはWHERE句だけなんだ…ッ! 嫌だ…ッ!! 楽をしたい!!!

という葛藤から生まれたのが以下の方法です。

QueryDefオブジェクトでSQLを引っこ抜く

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("クエリ名") 'クエリ情報を取得
Dim strSQL As String
strSQL = qdf.SQL 'SQL文を取得
  
Dim strSQL1 As String
strSQL1 = Left(strSQL, InStr(strSQL, "WHERE") - 1) '前半抜き出し
Debug.Print strSQL1 '前半確認

Dim strSQL2 As String
strSQL2 = Replace(strSQL, strSQL1, "")
Dim strWHERE As String
strWHERE = Left(strSQL2, InStr(strSQL2, "ORDER") - 1) 'WHERE句抜き出し
Debug.Print strWHERE 'WHERE句確認

strSQL2 = Replace(strSQL2, strWHERE, "") '後半抜き出し
Debug.Print strSQL2 '後半確認

Dim newStrWHERE As String
newStrWHERE = "WHERE ~" '新しいWHERE句を作成
  
qdf.SQL = strSQL1 & newStrWHERE & strSQL2 'WHERE句を置き換えてクエリに再設定
  
DoCmd.OpenQuery "クエリ名" '実行

現存しているクエリのSQL文を引っこ抜き、「前半」「WHERE句」「後半」の3つに分割し、WHERE句だけ差し替えてクエリに戻して実行、という流れです。例では後半がORDERから始まる、というつもりで書いてますが、単純に「前半」「WHERE句」だけで終了する場合もあると思います。

先日、実際にこれを閃いたときは「ヤッター!便利!!」と思ってたんですが、こうやってブログにまとめてみると、なんかこう、おとなしくSQLをちゃんと書け、という気もしないでもないですね。まあでも、こういうやり方もできたよ、という経験を書き残しておきます!

公開日:2021/11/29

書籍を執筆しています。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)