AccessVBAで変数を活用したクエリ(SQL)の作成

スポンサーリンク

変数を活用するとVBAでのクエリ(SQL)実行でより自由度の高くなる

以前にAccessVBAでクエリ(SQL)を実行するコードを紹介しましたが、VBAでのSQLの実行は変数を用いるとより自由度の高い複雑な処理が可能になります。

今回はAccessVBAでクエリ(SQL)を実行する際に変数を用いる方法を紹介します。

SQL文の文字列を変数で置き換える

クエリを構成するSQL構文は文字列なので、その中の一部を置き換えることでVBAの変数を使用することができます。

例として前回紹介したAccessVBAコードに下記のようなクエリ(SQL)を実行するコードを用います。
こちらのAccessVBAコードは「仕入伝票一覧」というテーブルの中に格納されているデータのうち、「店番」フィールドの値が「1」のものを削除するクエリを実行します。

Sub Accessで削除クエリ実行()

Dim StrSQL As String

StrSQL = “DELETE [仕入伝票一覧].[店番] ” & _
“FROM [仕入伝票一覧] ” & _
“WHERE [仕入伝票一覧].[店番]=””1″”;”

DoCmd.SetWarnings False ’アラートを出なくする
DoCmd.RunSQL (StrSQL)
DoCmd.SetWarnings True ’アラートを出るように戻す

End Sub

この条件式で変数を使用したい場合、下記のように変更します。

Sub 変数を使用したSQL文の作成()

Dim StrSQL As String
Dim joken As String
joken = “1”

StrSQL = “DELETE [仕入伝票一覧].[店番] ” & _
“FROM [仕入伝票一覧] ” & _
“WHERE [仕入伝票一覧].[店番]=””” & joken & “””;”

’DoCmd.SetWarnings False ’アラートを出なくする
DoCmd.RunSQL (StrSQL)
’DoCmd.SetWarnings True ’アラートを出るように戻す

End Sub

スポンサーリンク

変数に置き換える手順

元々の文字列を表す「”」と文字列の中の「”」を表す「””」が混在しているので理解しにくいですが、下記のような記載の順序を覚えておくとエラーが起こさずに変数に置き換えることができます。

①変数に置き換えたい文字の部分を「&」と「”」を用いて切り離す

文字列をつなぎ合わせた状態にするイメージで切り離す文字の前後に「” & “」を挿入します

“WHERE [仕入伝票一覧].[店番]=””1″”;”

“WHERE [仕入伝票一覧].[店番]=””” & “1” & ““”;”

②切り離された「”1″」の部分を変数に置き換える

「”1″」を変数「joken」に置き換えます。

“WHERE [仕入伝票一覧].[店番]=””” & “1” & “””;”

“WHERE [仕入伝票一覧].[店番]=””” & joken & “””;”

予備知識:「””」は「’」で置き換えるとわかりやすい

文字列中のダブルコーテーションを表す2連続の「””」は数が多くなってややこしければシングルコーテーション「’」で代用することも可能です。

“WHERE [仕入伝票一覧].[店番]=“”” & joken & ““”;”

“WHERE [仕入伝票一覧].[店番]=” & joken & “;”

このような形で変数を使えるとFor~Nextなどでjokenの値を変えながら、複数条件で削除クエリを実行させることも可能です。

下記は「店番」フィールドが1~100までのレコードを削除するAccessVBAコードです。
この場合、変数jokenを数値型で宣言しておいて、後でFormat関数を用いて文字列型に変更する方がより正しいコードになります。
(数値型のままでもVBAが自動的に変換してくれるので動きますが念のため。)

Sub 削除クエリを色々な条件式で実行する()

Dim StrSQL As String
Dim joken As Long

DoCmd.SetWarnings False ’アラートを出なくする
For joken = 1 To 100
StrSQL = “DELETE [仕入伝票一覧].[店番] ” & _
“FROM [仕入伝票一覧] ” & _
“WHERE [仕入伝票一覧].[店番]=””” & Format(joken, “0”) & “””;”

DoCmd.RunSQL (StrSQL)
Next joken
DoCmd.SetWarnings True ’アラートを出るように戻す

End Sub

もちろん今回のjokenの値を変更しながら繰り返し削除するだけならクエリだけの機能でも可能ですが、今回のように条件が100個あるような場合、一回で済ませるクエリを作るのは条件式が長くなり面倒です。

その点VBAで変数を使えば繰り返しを実行するだけで済むのでかなり楽になります。

注意点はきちんとアラートを非表示にしてから実行しないと繰り返しの数だけ確認画面が表示されることで、失敗するとずっとEnterキーを押し続ける羽目になります。

コメント