今回の記事では、MicrosoftのAccessの機能の一つの「パススルークエリ」を活用し、UPDATEなどの更新系SQLの実行や接続先データベースに登録されているストアドプロシージャを呼び出す方法について紹介します。
尚、今回の記事ではパススルークエリの接続先データベースはMicrosoftのSQL Serverを使用します。
AccessのパススルークエリはSQLのSELECT文を登録して選択クエリのように使用するのが一般的ですが、実はSELECT以外のSQLも自由に実行できます。
Access上級者なら知っておいてほしいテクニックです。
是非活用してみてください。
因みに、Access VBAやExcel VBAのADOを使用して接続先データベースのストアドプロシージャを実行する実装方法は以下の記事で紹介しています。
興味があればこちらもどうぞ。
パススルークエリの基本的な仕組み
当項では、パススルークエリの基本的な仕組みについて簡単に解説します。
尚、パススルークエリについては、この仕組みも踏まえて広く解説した記事を以前に掲載しております。
以下のリンク先では、パススルークエリの仕組みやメリットデメリットや使いどころのポイントをまとめています。
良ければご一読ください。
このリンク先でも紹介していますが、パススルークエリは、接続先のデータベースに対してSQL文をそのまま透過的に流し込んで実行できます。
また、そのSQLはパススルークエリにおいてSELECT文を使用するのが一般的ですが、SELECT文以外のSQL構文でも実行することができます。
そのため、ODBCで接続する際に指定しているデータベースのログインユーザーの実行権限さえあれば、UPDATE文などの更新系のSQLや、接続先データベース内に登録されているストアドプロシージャも自由に実行可能です。
UPDATE文の実行方法
上記のパススルークエリを紹介しているリンク先でも記載していますが、リンクテーブルを経由したクエリは処理速度が遅く、それはデータを取得するSELECT系の処理だけではなく、UPDATEなどの更新系処理でも同様です。
そのため、リンクテーブルを介した更新クエリなどで処理が遅い場合は、パススルークエリを使って接続先データベースに対して直接SQLのUPDATE文を流し込むことをおススメします。
尚、UPDATE文などの更新系SQLをパススルークエリで実行する場合に、パススルークエリの作成画面で記述するSQL文に特別な記述方法は必要なく、接続先データベースの構文に合わせたSQL文を書いて登録するだけです。
ただ、一点注意が必要なのは、パススルークエリのプロパティシートに「レコードの表示」という設定項目があります。
この項目は既定では「はい」です。
この状態のままUPDATE文などの更新系SQLをパススルークエリで実行すると以下のエラーになります。
このエラーは表示されますが、接続先のデータベースではSQLが実行されてデータは更新されています。
UPDATEなどの更新系SQLの場合は処理結果をレコードで返してこないのですが、プロパティシートに「レコードの表示」が「はい」の場合はSQLの実行結果をレコードで返してきたものを受け取る設定です。
そのため、レコードを返してこないことでエラーになります、
よって、UPDATEなどの更新系SQLをパススルークエリで実行する場合は、この「レコードの表示」は「いいえ」に変更してください。
ストアドプロシージャの実行方法
パススルークエリから接続先データベースのストアドプロシージャを実行する場合の呼び出し方を紹介します。
当記事の例では、ODBCで接続したSQL Server内に作ってあるストアドプロシージャの「[uspTestStoredProcedure」にパラメーターを渡して実行し、その処理結果を受け取ります。
その場合、パススルークエリでは以下のように記述します。
DECLARE @return_value int
EXEC @return_value = [dbo].[uspTestStoredProcedure]
@arg1 = 1234,
@arg2 = 5678
SELECT 'rtnval' = @return_value
尚、前述したパススルークエリのプロパティシートの「レコードの表示」については「はい」を指定します。
上記のSQLで処理の最後にストアドプロシージャの戻り値をSELECTしているため、戻り値をSELECT結果として受け取れます。
このように、パススルークエリでは、SQL文の接続先データベースにそのまままるっと渡すことができるため、上記のように接続先データベースの変数を使用したり、処理結果をSELECTして返すといった使い方も可能です。
また、上記のようにストアドプロシージャを実行する場合でも、サーバーとAccessファイルがあるクライアント間では必要な通信は発生しません。
よって、非常に高速な処理が期待できます。
最後に
今回の記事では、Accessのパススルークエリをより便利に使用するためのSELECT以外のSQLを実行する方法を紹介しました。
尚、以前に当ブログで紹介させていただいた、パススルークエリをVBAのDAOを使用して動的に生成する処理と併せて活用することで、さらにAccessでやれることも広がります。
機会があれば積極的にチャレンジしてみてください。
今回も読んでいただきましてありがとうございました。
それでは皆さまごきげんよう！
