ご訪問ありがとうございます。
VBAでパススルークエリを書き換えて、他のデータベースに「動的に条件を変えてアクセスする方法」を紹介しています。
- ACCESSのリンクテーブルでは処理速度に不満がある。
- パススルークエリで検索条件を変えて動的に処理をしたい。
このような方には参考になるかと思います。
VBAでパススルークエリを書き換える方法
まずは本題の「VBAでパススルークエリを書き換える方法」を解説します。
※コーディングサンプルだけを見たい方はこちらです。
パススルークエリを準備する
パススルークエリを事前に作成しておきます。
クエリ名は「psQuery」にしました。
※パススルークエリの作成方法は、このページの後半で解説しています。
VBAで書き換えるので、中身は（以下のように”SELECT”だけでも）何でも構いません。
ＶＢＡをコーディングする
パススルークエリを書き換えるVBAのサンプルです。
以下のサンプルでは変数「i」を1から10まで回してSQLを書き換えています。
テーブル名や項目名、検索条件などは、お使いのデータベースに合わせてください。
①クエリの書き換え命令
「db.QueryDefs(クエリ名).SQL = SQL命令」でパススルークエリを書き換えます。
動的に変えたい部分は変数を使用してください。
②データの読み込み
「SELECT * FROM クエリ名」でテーブルを開きます。
パススルークエリ書き換えの確認
VBA処理後にパススルークエリをデザインモードで開くことで、書き換えが行われたことを確認できます。
ところで、パススルークエリとは（簡単に解説）
ORACLEなどの外部データベースにSQLを発行して、サーバー側で処理する仕組みです。
サーバー側で処理するので、リンクテーブルに比べて処理速度は速いと言われています。
パススルークエリは本当に速いのか？
一般的にパススルークエリの方がリンクテーブルより速いと言われますが、実際にパススルークエリとリンクテーブルでの処理速度を比較してみました。
僕の場合は早い時もあれば遅い時もありました。
データの読み込み方法の違い、データ量、サーバーやクライアントの性能、ネットワーク環境などで違うのかもしれないですね。
感覚的ではありますが、データ量が多い場合はパススルークエリ、少ない場合はリンクテーブルを使用したほうが速く感じました。
参考までにADO接続も試しましたが、パススルークエリとADO接続は同等の処理速度でした。
パススルークエリを使うメリットは？
処理速度が違う
上述していますが一般的にはパススルークエリの方が速いと言われます。
リンクテーブルで遅いと感じた場合、パススルークエリを試してみる価値はあります。
読み取り専用でアクセスできる
自分だけで使うのであれば問題ないですが、ユーザーにて使うことを想定した場合にデータベースを直接開くことができる仕組みにしないほうが安全です。
パススルークエリは読み取り専用で開きます。
ユーザーでデータベースに直接触れることができないので安心してリリースできます。
DAOだけでコーディングできる
VBAをコーディングして外部データベースにアクセスするのであればADO接続も使用できます。
その場合、ACCESSのテーブルはDAO接続、外部ＤＢはADO接続と接続方法が複数になり、シンプルでないコーディングになってしまいます。
パススルークエリであればDAO接続だけでなので、きれいなコーディングになります。
※VBAでADO接続を使いたい方は以下の記事を参考にしてください、EXCELもACCESSも同じです。
パススルークエリの作成方法
パススルークエリの作り方を解説しておきます。
手順１：「作成」→「クエリ」を選択します。
手順２：「テーブルの表示」とダイアログが出たらそのまま「閉じる」で画面を閉じます。
手順３：「デザイン」→「パススルー」を選択します。 ※右クリックでも可
手順４：「プロパティーシート」を選択します。
手順５：「ODBC接続文字列」の右側にある「…」をクリックします。
手順６：データベースに接続するデータソースを選択します。
手順７：サービス名、ユーザー名、パスワードを入力します。
手順８：接続文字列にパスワードを含めますか？ → 「はい」
手順９：クエリが空白では終了できないので文字入力（今回は”SELECT”と入力）して終了します。
VBAから書き換えを行う前提で記入しています。
書き換えを行わない場合は正しいSQLを作成してください。
手順10：クエリ名を入力して「OK」で保存して終了です。
VBAは難しい？
ところで、VBAは難しいでしょうか？
VBAは一般的には簡単な言語だと言えますが、プログラミングの経験がない人には難しいかもしれないですね。
書籍などの独学でなかなか覚えられないのであれば、スクールで基礎から習うのも手段です。
