派遣で働くエンジニアのスキルアップを応援するサイト

PRODUCED BY RECRUIT

第5話 VBA内でSQLを扱うコツ【連載】実務で使えるAccessのコツ

f:id:itstaffing:20210827161938j:plain

データベース管理ソフトウェア、Accessを実務で使うときに「知っているとちょっと便利なコツ」について紹介するこのコラム。今回は、VBA内に複雑なSQLを組み込むための手順を解説します。複雑なものほど長くなるSQLですが、なにを使って区切ると読みやすくなるのでしょうか。あなたのAccess力アップに役立ちますように。

前回のコラムを見逃した方はこちらからご覧ください。
第4話 DAOとADOの違い

【筆者】今村 ゆうこさん
【筆者】今村 ゆうこさん
地方の非IT系企業に勤務し、業務アプリケーション開発を行うプログラマー。著書のイラストや図解も手掛けている。大学卒業後出身地へUターン就職し、2度の産休/育休を経て現在は小学生と保育園児の2人のこどもを抱えるワーキングマザー。主にVBAを使ってデータベースの情報を加工/展開するアプリを開発するほか、WordPressを使った個人ブログ(https://ateitexe.com/)で技術情報を発信している。著書に『Accessデータベース 本格作成入門』『Excel & Access連携 実践ガイド』(技術評論社)など。

業務で使えるSQLを作るには

前回の記事では、AccessVBAでSQLを「処理する部分」のテンプレートの書き方をご紹介しました。このテンプレートがあれば、SQLだけ書き換えれば自由度の高い使い方ができてとっても便利です。

ただ、この「SQLを書き換えるだけ!」が意外と難しくて、固定値の短いSQL文ならばそれほどではありませんが、業務で使うとなるとどうしても複雑になりがちです。

そういったSQL文は細かいパズルのようで、うっかり必要な記述が抜けてしまったり、そのミスに気づきにくかったり、慣れるまではゴールが遠く感じてしまうかもしれません。

f:id:itstaffing:20210827161940j:plain

今回は、そんなSQLパズルの組み立て方のコツをご紹介します。

SQLの基本形をおさらい

まずはSQLでレコード操作するための基本の文を見てみましょう。

・抽出 → SELECT フィールド1, フィールド2 FROM テーブル WHERE 条件;
・挿入 → INSERT INTO テーブル(フィールド1, フィールド2) VALUES(値1, 値2);
・更新 → UPDATE テーブル SET フィールド1=値1, フィールド2=値2 WHERE 条件;
・削除 → DELETE FROM テーブル WHERE 条件;

使うときに大切なのは、フィールドのデータ型です。値はデータ型に適した形で書かなければなりません。

上のINSERT文を例にすると、たとえばフィールド1が数値型でフィールド2がブーリアン型の場合、対応する値は以下のように書きます。*真(true)か偽(false)のどちらかの値が入る

f:id:itstaffing:20210827161943j:plain

フィールド1が文字列型だった場合は「'」を、フィールド2が日付型だった場合「#」を、対応する値にそれぞれ付けることでデータ型に対応させます。

f:id:itstaffing:20210827161947j:plain

この識別記号の扱いをよく覚えておきましょう。

VBA内ではSQLは“文字列”で扱う

VBA内ではSQLはそのまま認識できないので、文字列として扱うことになります。前回の記事で紹介したテンプレートを使う側では、レコードを抽出するコード(抜粋)は以下で、

Public Sub selectRecord()
  '## レコードの抽出

  'SQL作成
  Dim sql As String
  sql = "任意のSELECT構文"

  '以下略
End Sub

レコードを挿入・更新・削除するコード(抜粋)は以下のようになっていましたよね。

Public Sub executeSQL()
  '## レコードの挿入・更新・削除

  'コレクションの作成
  Dim sqlList As Collection
  Set sqlList = New Collection

  'SQL作成
  Dim sql As String 'SQL文用変数の宣言

  sql = "任意のINSERT/UPDATE/DELETE構文"
  sqlList.Add sql 'コレクションへ追加

  '以下略
End Sub

このように、VBA内でのSQLは、「"」で括ることで、文字列として扱います。SQLの文字列は「'」、VBAの文字列は「"」と、使い分けておきましょう。

さきほど例に挙げた、データ型の識別記号を含めたINSERT文をVBA内に書くならば、以下のようになるわけです。

f:id:itstaffing:20210827161916j:plain

ここまで、しっかり理解しておきましょう。

VBA内に複雑なSQLを組み込むための手順

それではもう少し具体的に、VBA内で変数を組み込んだSQL文を作成してみます。

以下は「販売」テーブル内の「ID」フィールド(文字列型)、「売上日」フィールド(日付型)へ値を挿入するSQLです。

f:id:itstaffing:20210827161920j:plain

1.まずは1行の文字列にする

f:id:itstaffing:20210827161923j:plain

はじめに、「"」で括っただけの1つの文字列でレコード操作ができるか確認してください。このとき、適切な場所に「半角スペース」が含まれているかが大切です。必要なスペースが入っていないと、この時点でエラーになってしまいます。

2.句の区切りで分割する

f:id:itstaffing:20210827161929j:plain

複雑なSQLは長くなるので、VALUES、WHEREなどの区切りで分割すると読みやすくなります。「"」で括られている文字列が2つになり、文字列同士を「&」で結合しています。

<p">このとき、「VALUES」直前のスペースを忘れないように気をつけてください。スペースをどちらの文字列に含むかは好みですが、統一しておくとよいでしょう(筆者はスペースを文字列の最後に含めるようにしています)。

3.改行する

f:id:itstaffing:20210827161926j:plain

VBE(Visual Basic Editor)内で、改行は「 _」(半角スペース+アンダースコア)です。2つに分割した文字列を改行して、インデントを入れて行頭を揃えましょう。

この改行はVBA上のもので、文字列合成されたSQL文内には改行は含まれません。

4.フィールドごとに分割&改行する

f:id:itstaffing:20210827161932j:plain

フィールドがたくさんある場合は、フィールドと値を1つの要素ずつ改行しておくと比較的読みやすくなります。このとき、インデントを入れておくとさらに良いでしょう。最終的にSQLが正しく認識されれば動作するので、あまり細かく分割されすぎないほうがよい方は適宜で構いません。

5.可変部分を分割する

f:id:itstaffing:20210827161935j:plain

「P001」や「2021/1/1」は、実務では変数を使いたい部分です。「'」や「#」などの識別子を分割して、可変部分のみ切り出します。

6.変数に置き換える

f:id:itstaffing:20210827161935j:plain

可変部分を変数に置き換えます。変数部分は、Form.Textbox.Valueのように書けば、フォーム上のテキストボックスの値を組み込むこともできます。

以上の手順はあくまで一例ですので、ご自身の理解しやすい形にしていただければと思います。

なお、Accessはインハウスデータベース(企業などが自社で利用するための情報を非公開で蓄積するデータベース)の中でも非常に少人数かつ限定的に使われるものなので、SQLインジェクション(不正なSQL文を実行させる攻撃)の可能性は低いと思われますが、VBAでも対策は可能です。こちらは次回記事でご紹介いたします。

例)テーブル結合があるSELECT文

それでは次は、よく登場する複数テーブルからのSELECT文を見てみましょう。一文で書くと以下のように長くて、読み解くのがつらいですね。しかし、この時点でちゃんと動くかどうか確認しておきましょう。ここに問題があった場合、後で原因をたどるのが困難になってしまいます。

sql = "SELECT T1.販売ID, T1.商品ID, T2.商品名, T1.売上日, T1.数量, T2.定価 FROM 販売データ AS T1 INNER JOIN 商品マスター AS T2 ON T1.商品ID = T2.商品ID WHERE T1.売上日 BETWEEN #2021/01/01# AND #2021/01/30# ORDER BY T1.売上日 DESC;"

まずは大きな区切り(上記で色が付いている部分)で文字列を分割・改行します。インデントで行頭を揃えるのも忘れずに。これだけでもかなり読みやすくなりましたね。

sql = _
  "SELECT T1.販売ID, T1.商品ID, T2.商品名, T1.売上日, T1.数量, T2.定価 " & _
  "FROM 販売データ AS T1 INNER JOIN 商品マスター AS T2 ON T1.商品ID = T2.商品ID " & _
  "WHERE T1.売上日 BETWEEN #2021/01/01# AND #2021/01/30# " & _
  "ORDER BY T1.売上日 DESC;"

FROM句がまだ情報量が多いので改行してみましょう。関連項目はさらに一段インデントを入れておくとわかりやすくなります。

sql = _
  "SELECT T1.販売ID, T1.商品ID, T2.商品名, T1.売上日, T1.数量, T2.定価 " & _
  "FROM 販売データ AS T1 " & _
    "INNER JOIN 商品マスター AS T2 " & _
    "ON T1.商品ID = T2.商品ID " & _
  "WHERE T1.売上日 BETWEEN #2021/01/01# AND #2021/01/30# " & _
  "ORDER BY T1.売上日 DESC;"

現時点ではWHERE句の条件は1つだけですが、複数設定する場合は、条件ごとに改行してインデントを入れると可読性が良くなります。

sql = _
  "SELECT T1.販売ID, T1.商品ID, T2.商品名, T1.売上日, T1.数量, T2.定価 " & _
  "FROM 販売データ AS T1 " & _
    "INNER JOIN 商品マスター AS T2 " & _
    "ON T1.商品ID = T2.商品ID " & _
  "WHERE " & _
    "T1.売上日 BETWEEN #2021/01/01# AND #2021/01/30# " & _
    "AND T1.商品ID = 'P001' " & _
  "ORDER BY T1.売上日 DESC;"

フィールドの改行はお好みですが、改行する場合はインデントを入れると良いでしょう。

sql = _
  "SELECT " & _
    "T1.販売ID, " & _
    "T1.商品ID, " & _
    "T2.商品名, " & _
    "T1.売上日, " & _
    "T1.数量, " & _
    "T2.定価 " & _
  "FROM 販売データ AS T1 " & _
    "INNER JOIN 商品マスター AS T2 " & _
    "ON T1.商品ID = T2.商品ID " & _
  "WHERE " & _
    "T1.売上日 BETWEEN #2021/01/01# AND #2021/01/30# " & _
    "AND T1.商品ID = 'P001' " & _
  "ORDER BY T1.売上日 DESC;"

WHERE句の条件値を変数に置き換えます。こうしておけば、柔軟に条件を変えて抽出することができます。「'」や「#」の識別子の有無に注意しながら記述しましょう。

sql = _
  "SELECT " & _
    "T1.販売ID, " & _
    "T1.商品ID, " & _
    "T2.商品名, " & _
    "T1.売上日, " & _
    "T1.数量, " & _
    "T2.定価 " & _
  "FROM 販売データ AS T1 " & _
    "INNER JOIN 商品マスター AS T2 " & _
    "ON T1.商品ID = T2.商品ID " & _
  "WHERE " & _
    "T1.売上日 BETWEEN #" & date1 & "# AND #" & date2 & "# " & _
    "AND T1.商品ID = '" & id & "' " & _
  "ORDER BY T1.売上日 DESC;"

なお、文字列の改行が多くなりすぎると認識されなくなってしまうので、長くなる場合はいったん終わらせて、改めて今まで書いた文字列と結合するという方法もあります。

sql = _
  "SELECT " & _
    "T1.販売ID, " & _
    "T1.商品ID, " & _
    "T2.商品名, " & _
    "T1.売上日, " & _
    "T1.数量, " & _
    "T2.定価 "

sql = sql & _
  "FROM 販売データ AS T1 " & _
    "INNER JOIN 商品マスター AS T2 " & _
    "ON T1.商品ID = T2.商品ID " & _
  "WHERE " & _
    "T1.売上日 BETWEEN #" & date1 & "# AND #" & date2 & "# " & _
    "AND T1.商品ID = '" & id & "' " & _
  "ORDER BY T1.売上日 DESC;"

イミディエイトウィンドウに出力して間違いを探す

SQLの記述は、1つ手を加えたらその都度動作確認をしていく癖をつけておくと、「どこで間違いが発生しているのかわからない!」という状況にハマりにくくなります。

しかしながら、ループ内で変数が意図しない値になるなど、不測の事態はいつでも起こり得ます。原因を調査するときは、合成した後のSQLがどんな形になっているかをイミディエイトウィンドウに出力してみると間違いが見つけやすくなります。

Dim sql As String
sql = "任意のSQL文"

Debug.Print sql

いかがだったでしょうか? 最初はちょっととっつきにくいと思いますが、変数を組み込んだSQLが扱えるようになるとできることがグッと広がります。ぜひチャレンジしてみてください!

さて、次回でこのコラムは最終回です。最後にExcelからAccessのデータベースを読み書きする方法をご紹介いたします。ご期待ください。

第1話「イベント」を使いこなそう
第2話 レイアウトで躓きやすいポイント
第3話 マクロとVBAの違い
第4話 DAOとADOの違い