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をちゃんと書け、という気もしないでもないですね。まあでも、こういうやり方もできたよ、という経験を書き残しておきます！