今回の記事では、MS Accessの機能のなかでも、初心者の人には機能や使いどころがわかり辛い「パススルークエリ」に焦点をあてて、パススルークエリの仕組みや、メリットデメリット、効果的な使いどころのポイントなどを解説していきます。
パススルークエリを効果的に活用できれば、立派なAccess上級者です。
もしこれまで使う機会がなく使い方をご存知なければ、この記事を参考にして上手く活用してもらえれば幸いです。
尚、今回の記事では、過去に当ブログで紹介したAccess関連の記事や、パススルークエリを扱った記事に関するいくつかのリンクも併せて掲載しています。
リンク先の記事も参照しながら読み進めていただくと良いかと思います。
パススルークエリとは
「パススルークエリ」について理解するには、まず、そもそも「パススルークエリとは何か？」について解説が必要です。
まずはこの機能の名称から大まかな機能内容を捉えます。
「パススルー」は「pass through」であり、「素通りする」、「通り抜ける」とか「通過する」といった意味です。
ITの用語や名称では、「パススルー〇〇」といったものは多くあり、今回のAccessの機能である「パススルークエリ」以外にも、ネットワーク系の機能なんかでもよく使われています。
「パススルークエリ」の名前から、「通り抜けるクエリ」なんだろうと推測は容易にできて、それがこの機能の本質です。
では、次項では「通り抜けるクエリ」について詳しく説明していきます。
パススルークエリの機能と仕組み
パススルークエリの仕組みを一言で説明すると、ODBCを介して接続するデータベースに対して直接SQLを渡して実行できる機能です。
ODBCでデータベースに接続する同様の仕組みとしては「リンクテーブル」があります。
「リンクテーブル」では、外部のデータベースのテーブルに対してネットワーク越しに接続して、疑似的にAccess内のローカルテーブルのように開いたり、クエリを使ってデータを抽出したり更新することができる大変便利な機能です。
Accessが高度なデータベースの知識を必要とせずに、複雑なデータ抽出が行えたり、データベースを参照する業務アプリケーションを容易に作成できるのは、このリンクテーブルの機能によるところが大きいと言えます。
ただ、この便利な「リンクテーブル」ですが、実は「リンクテーブルを参照するクエリ」が遅いというネガティブな特徴もあります。
外部データベースの主キーを条件に指定した選択クエリであれば、この遅さを感じることは少ないですが、複雑なクエリや主キーを適切に条件に指定できていないクエリを実行した場合に、対象となるデータ量次第では非常に遅くなります。
これらの原因については過去に当ブログの記事で紹介しています。
リンクテーブルを介したクエリが遅い原因については、以下の記事もご参照ください。
リンク先の記事で紹介した内容をまとめると以下です。
- リンクテーブルを介したクエリではクライアント側での重い処理が動く。
- クエリ内容によっては対象テーブルの全行が返される場合がある。
尚、一般的にデータベースからテーブルを指定してデータを抽出したり、特定のデータを更新するといった操作には「SQL」を書いて実行する必要があります。
Accessの便利なところとしては、このSQLを知らなくても「クエリデザイン」画面からフィールドをドラッグして内部結合や外部結合が作れたり、抽出条件を指定することができるところです。
ただ、このクエリも内部的にはSQLを自動的に生成しており、データベース間の仕様やデータの受け口の違いを吸収して仲介する役割のODBCに、その生成されたSQLを渡してデータベースにクエリで指示した処理を実行させています。
このクエリからSQLを自動的に生成する機能については、過去に別の記事で紹介しています。
この記事でも書いていますが、自動的に生成されるSQL文はお世辞にもきれいなSQL文とは呼べない、無駄の多い冗長な記述で生成されます。
このような記述が冗長なSQLが生成されて実行されているのも、クエリが遅い原因の一つかも知れません。
パススルークエリは処理が高速
リンクテーブルを介した処理が遅い場合の打開策として有効なのが、今回紹介する「パススルークエリ」です。
当ブログでも過去に、クエリが遅い場合の改善策としてパススルークエリをオススメする記事を公開しています。
上記の記事でも書いていますが、リンクテーブル経由でのクエリでボトルネックになる要素は、パススルークエリでは全て省かれることになります。
パススルークエリでは、Access側で特別な処理は一切せずに、ODBCを介して接続先のデータベースまでSQLを文字通り素通りさせます。
接続先データベースではそのSQLを受け取り、その処理結果を返して、Access側ではそれをそのまま受け取るだけです。
よって、Access側で負荷の掛かる処理は発生せず、リンクテーブル経由のクエリで発生する不必要なデータ転送も起こりません。
その為、パススルークエリはリンクテーブル経由のクエリと比較すると、複雑なクエリであればあるほど、効率よく処理を実行できます。
上記のリンクテーブルを介したクエリの実行と、パススルークエリを利用して実行したクエリの動作の違いのイメージが以下になります。
このように、リンクテーブル経由でのクエリ実行と、パススルークエリとでは、内部的に大きな違いがあります。
これらを効果的に使い分けることがAccess上級者には必須のスキルだと言えます。
パススルークエリの作り方
当項では、Accessにおける「パススルークエリの作り方」を紹介していきます。
ただ、このパススルークエリの作り方は過去の記事で簡単に手順を紹介しています。
よって、基本的には上記の記事を参考にしてください(古いAccessで画面のスクリーンショットを撮影しているため、UIが古くて申し訳ありません・・・)。
パススルークエリを作成する際のODBC接続文字列設定方法
上記の記事ではパススルークエリのODBCで接続する際に使用する接続文字列設定に関する紹介が無いため、補足として当項でその設定方法も紹介しておきます。
パススルークエリのプロパティシートから「ODBC接続文字列」を作成
パススルークエリ作成画面のプロパティシートを表示して、「ODBC接続文字列」の設定箇所右側の「・・・」をクリックします。
データソースを選択
リンクテーブル作成時にも表示される「データソースの選択」画面が表示されます。
接続先のデータベースが登録されているデータソース名を選択して下部のOKをクリックします。
データベースへのログイン認証
選択したデータソース名で登録されているデータベースへのログイン認証をします。
用意されているユーザー名とパスワードで認証します。
画面の例ではSQL Serverへ接続しています。
接続文字列内にパスワードを含めるかを選択
パススルークエリのプロパティで保存されるODBC接続文字列内に対象のユーザーのパスワードを含めるかを選択します。
尚、パスワードを接続文字列に含めた場合は、パススルークエリを登録後に、そのパススルークエリを開く際にもパスワードを聞かれることは無くなります。
接続文字列にパスワードを含めない場合は、Accessファイルを開いて、登録されているパススルークエリを開く初回だけ、毎回パスワードの入力が求められます。
一度パスワード入力を行い認証を済ませれば、それ以降はそのAccessファイルを閉じるまではパスワードは聞かれません。
そのパススルークエリの用途によって、パスワードをODBC接続文字列に含めるか否かを選択してください。
これでAccess内のクエリの一覧に作成したパススルークエリが登録されます。
状況次第ではセキュリティ上の大きなリスクになるため、十分に注意をしてパスワードを接続文字列に埋め込むかを検討してください。
パススルークエリのメリットとデメリット
当項では、Accessにおけるパススルークエリのメリットとデメリットを紹介していきます。
尚、メリットは上項で紹介した内容に併せて、パススルークエリの別の便利な特徴も紹介します。
また、デメリットでは、パススルークエリの使い勝手の悪い部分を紹介していきます。
パススルークエリのメリット
バススルークエリのメリットはこれまでお伝えした通り、リンクテーブルを介したクエリと比較した場合に、ボトルネックになる処理が排除され、非常に処理が高速化されるところです。
更に、以下の特徴もあります。
- 接続先データベースのSQL構文がそのまま実行できる。
- SELECT文に限らずその他のSQL構文も実行できる。
実はここも結構重要です。
Accessのリンクテーブルを介したクエリでデータを扱う場合は、接続先のデータベースが何であれAccessが持っている組み込み関数しかつかえないですし、Accessが持っている機能以上のことはできません。
パススルークエリの場合は、接続先のデータベース側にSQL文をそのまま渡し、データベース側で処理を行います。
そのため、接続先のデータベースで持っている組み込み関数や自作関数などもそのまま実行されます。
Accessには持っていない機能でも実行することができます。
また、Accessでパススルークエリを使う際に一般的なのは、SQLのSELECT文をパススルークエリで登録して、選択クエリと同じように疑似的なローカルテーブルのように扱うような使い方ですが、実はSELECT文だけではなく、UPDATEやINSERT、テーブルやインデックスの作成などを実行するCREATE文などの構文もSQLでそのまま渡すことができます。
また、データベース内で作られたストアドプロシージャなどのプログラムも呼び出して実行することができます。
ストアドプロシージャに戻り値も設定されているなら、それもAccess側で受け取ることもできます。
パススルークエリではSQL文をそのまま接続先のデータベースに投げて実行できるメリットは、実は多岐に渡ります。
ここは、パススルークエリのあまり知られていない大きなメリットです。
もし興味があれば以下のリンク先で詳しい実装方法を紹介しているため、こちらも併せてご一読ください。
パススルークエリのデメリット
これまでは、パススルークエリのメリットや便利な特徴を色々とお伝えしてきましたが、パススルークエリにもデメリットがあります。
それはパススルークエリに登録できるSQLに動的なパラメーターを指定できない仕様の部分です。
例えば、Accessの選択クエリでは、データの抽出条件として特定のフォームのテキストボックスの値を指定することができます。
Accessで業務アプリを作成する際には、誰もが必ず通る実装方法です。
フォーム内の特定のボタンを押下した際に実行する選択クエリの抽出条件として、そのフォーム内のテキストボックスのコントロール名をクエリ作成時に指定しておくことで、そのクエリはフォームのテキストボックスの値を参照して選択クエリを実行してくれます。
このようにAccessの通常のクエリでは動的な抽出条件を指定することができますが、パススルークエリでは、パススルークエリを作成する際に登録したSQL文を接続先のデータベースにそのまま流すことしかできません。
SQLにおけるデータの抽出条件を指定する「WHERE句」以降にAccess内に作成したフォームのコントロール名を記述してもエラーになります。
このように、パススルークエリでは静的な固定値のSQL文しか登録することができません。
そのため、せっかくパススルークエリであれば高速にデータを取得できるのですが、Accessで実行させたいクエリは、得てしてデータの取得条件を都度変えて実行させるケースが多く、データの取得条件を変えずに繰り返し実行するような使い方のクエリはあまり利用されません。
これまでお伝えした多くのメリットがある機能である以上、上手く活用しないともったいないと言えます。
そこで、次項ではそのデメリットを解消してパススルークエリを活用するためのテクニックを紹介します。
DAOで動的にパススルークエリを作成する
パススルークエリが静的な固定値でのSQL文しか登録できないのであれば、パススルークエリを実行する直前に、都度動的にパススルークエリ内のSQL文を書き変えて作成し直して実行すれば良いのです。
AccessではDAO(Data Access Objects)と呼ばれるAccessのデータベースの様々な操作をする機能を提供するコンポーネントが使えます。
今のAccessのファイルで使われている拡張子はaccdbですが、Access2003まではmdbという拡張子が使われている、その頃のAccessではJetと呼ばれるデータベースエンジンが使われ、DAOはそのJetに最適化されています。
このようにDAOの歴史は非常に古く、昔から実装されている機能です。
VBEの参照設定でも「Microsoft Office XX.X Access Database Engine Object Library」は既定でチェックが入っています。
また、従来の「Microsoft DAO 3.6 Object Library」も選択できますが、その場合は「Access Database Engine Object Library」は共存できないため、旧DAOを使う場合は参照から外します。
ただ、本来は「Access Database Engine Object Library」はDAOの後継として同じコードで扱えるため、accdb形式のAccessでは「Access Database Engine Object Library」をDAOとして同じものとして扱って問題は無いかと思います。
このDAOで持っている機能のなかには、Accessファイル内のクエリを削除したり、新規クエリを作成することができます。
この機能を使って、以下の様な処理をVBAで実装することで、疑似的に動的なパススルークエリを都度実行させることが可能になります。
- 特定の名前のクエリがAccessファイル内に登録されていれば削除する。
- 必要な条件のSQL文を生成して、そのSQLでパススルークエリを特定の名前で作成する。
- 作成したクエリを実行する。
上記のようにDAOを使用して動的にパススルークエリを作成するサンプルプログラムは過去の記事でも紹介しています。
具体的なVBAでの実装方法は以下の記事を参考にしてください。
パススルークエリの効果的な使いどころ
これまでの説明で、パススルークエリの仕組みや特徴はある程度理解できたかと思います。
当項では、どのようなケースでパススルークエリを使っていけば良いのかを簡単に紹介していきます。
リンクテーブルを介したクエリの速度を改善したい場合
これまで何度か記載した通り、パススルークエリの特徴としてはリンクテーブルを介したクエリよりも処理が速いという部分があります。
よって、Accessで作成したアプリケーションにおいて、リンクテーブルを介したクエリをフォームと連結させていたりしてフォームの読み込みが遅い場合などは、そのクエリの名前や、クエリ内で使用しているフィールド名を同じにしてパススルークエリで作り直すことで、フォームの読み込み速度が劇的に改善し、さらにフォーム側では連結先のクエリ名やフィールド名が同じであれば、フォームへの変更を加えることなくクエリを置き換えることができます。
更に必要であれば、DAOでパススルークエリも動的に作成して動的な抽出条件の変更にも対応させます。
このように、Accessでの作成した業務アプリケーションの速度改善対応の手段としては非常に効果的な使いどころと言えます。
広い範囲の固定条件のパススルークエリに対して通常のクエリでデータを絞り込む
パススルークエリのデメリットである動的な条件の指定ができない部分を許容し、本来の取得したいデータ範囲を包括する更に広い範囲が対象になる固定条件を設定したパススルークエリを作成します。
そのパススルークエリをテーブルに見立てて、通常の選択クエリを使ってパススルークエリで取得してきたデータを動的に絞り込むといった使い方も可能です。
パススルークエリを元データとして通常の選択クエリを実行する場合は、パススルークエリが実行された際に、対象のデータがローカル側に取得できた状態であり、そのデータに対して選択クエリを実行するのであれば、ネットワークを介することなくローカルのメモリー内のデータを使って通常のクエリで目的のデータを絞り込むことになります。
最初のパススルークエリでどれだけ抽出範囲を絞れるかによりますが、効果的に使用すれば、DAOで動的にパススルークエリを作成する手法を取らなくても十分実用に耐える実装することができます。
接続先データベースへSQLを流す特殊な操作
前述したように、パススルークエリでは、SQLをそのまま接続先データベースに渡すことができるため、Accessだけでは実行できない特殊な処理をデータベースに実行させることができます。
おススメしたいのがデータベース側で実装されている「ストアドプロシージャ」の呼び出しです。
ストアドプロシージャとは、データベース側で条件分岐やループ処理を使った複雑なデータ更新処理などを実装するための、SQLで書かれたプログラムです。
このストアドプロシージャはデータベースのなかだけで処理が完結し、サーバーのメモリー上で実行されることで、非常に高速に処理が行えます。
このストアドプロシージャはVBAにおけるADO(ActiveX Data Objects)と呼ばれるデータベース接続用のライブラリーを介しても呼び出すことはできますが、パススルークエリで呼び出す方法の方がはるかに手軽に実装できます。
以下のリンク先で実装方法を紹介しております。良かったらこちらもご参照ください。
パススルークエリを使用してストアドプロシージャを実行する実装方法
最後に
今回はMS Accessのなかでも、いまいち活用度が低い「パススルークエリ」にスポットをあてて、知っておいてほしい知識を紹介いたしました。
当記事を読めばわかりますが、パススルークエリを上手く活用することで、貴方が作成しているAccessの業務アプリはもっと便利に、もっと使い勝手が良くなるかも知れません。
機会があれば、積極的に使ってみてください。
今回の記事も読んでいただきましてありがとうございました。
それでは皆さまごきげんよう！
