Access

Accessでパススルークエリの作り方

処理速度の問題やセキュリティの問題など、パススルークエリーを作らなければならないシーンが出てきます。

今日はパススルークエリーの作り方を2通りご紹介します。

Accessパススルークエリの作り方

クエリーのプロパティに接続文字列を定義する

①クエリを新規に作成し、種類を『パススルー』に変更します。

パススルークエリはデザインビューを使えません。

SQLを書くのが苦手な方は先に該当のテーブルに外部接続(リンク)してデザインで作成した後、クエリの種類をパススルーに変更すると作りやすいです。

※AcceesとサーバーのSQL構文が異なりますので全く同じで動く保証はありません。あくまでも参考に♪

クエリのプロパティを表示し、ODBC接続文字列に定義を入力します。

ODBC接続文字列の例

【Postgre(ポスグレ)の例】

ODBC;DRIVER=PostgreSQL ANSI;APP=2007 Microsoft Office system;Trusted_Connection=No;SERVER=xx.xxx.xxx.xxx;DATABASE=dbname;UID=user;PWD=password;

【SQL Serverの例】

ODBC;DRIVER=SQL Server;APP=2007 Microsoft Office system;Trusted_Connection=Yes;SERVER=SVR01;DATABASE=dbname;

  • DRIVER:接続するDB名(ODBCドライバで確認してください)
  • APP:接続するクライアントのOfficeのバージョン
  • Trusted_Connection:Windows認証の場合はYES、ユーザー/パスワードをしているする場合はNo
  • SERVER:IPアドレス、またはサーバー名
  • DATABASE:データベース名;(ユーザー名; パスワード;)

ツール起動時にVBA経由で設定する

フォームを開いたときにクエリのプロパティを定義する方法です。

定義を可変にしたい場合などに有効です。

Private Const CONNECT = "ODBC;DRIVER=PostgreSQL ANSI;APP=2007 Microsoft Office system;Trusted_Connection=No;SERVER=xx.xxx.xxx.xxx;DATABASE=dbname;UID=user;PWD=password"

'-------------------------------------------------------------------------------
'フォームを開いた時
'-------------------------------------------------------------------------------
Private Sub Form_Open(Cancel As Integer)

    On Error GoTo ErrHandler

    Dim dbs As DAO.Database: Set dbs = CurrentDb
    
    Dim Qdf As DAO.QueryDef: Set Qdf = dbs.QueryDefs("定義済みクエリ名")
    Qdf.CONNECT = CONNECT

    Exit Sub

ErrHandler:
    MsgBox "エラー番号:" & Err.Number & vbCrLf & _
           "エラーの種類:" & Err.Description, vbExclamation

End Sub

Accessのパススルークエリを使うシーン

やはり処理速度の改善が一番のメリットです。

検索自体はSQL ServerやMy SQL、PostgreなどのDBサーバー側に任せ、結果だけをもらう形になりますので、お使いのクライアントPCで処理するよりも断然早いです。

サーバーのDBにリンクして検索している処理が重たい時はぜひパススルークエリをお試しください。

また、接続先のDBのセキュリティの関係でリンクテーブルのデータが#Deletedと表示され見えないという場合があります。

この場合も、リンクテーブルではなくパススルークエリで表示させると見えることがあります。

SQLの文法
SQL ServerのSELECT SQLで、明示的にロックしないように指定する方法先日、SQL ServerのSQLをAccessのパススルークエリを介して実行していましたが、どうも他のSQL実行と競合してしまうようで...

まとめ

Accessのパススルークエリの作り方は意外と簡単!

SQLの作成が苦手なら一旦デザインビューで作ってみてから編集するのも一つの手です。

もちろん、AccessのSQLの構文と接続先のSQLの構文は異なりますので変更する必要が出てきますが…。

できればサーバー側のSQLの構文もしっかり覚えたいですね!

ABOUT ME
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。
今のスキルのままで大丈夫?

あなたのスキル。今のままで大丈夫ですか?

時代のニーズに合った開発スキルを身につけてあなたの価値を高めましょう。

\オンラインでも教室でも学べるおすすめのスクール/

TECH::CAMP(エンジニアスクール)

まずは基礎から・・・という方はProgateUdemyどっとインストールなどがおすすめ!

スキルアップについて詳しくはこちら