#722 パススルークエリのリンク先を変更するには? クエリ、VBA

SQL Serverなどと接続する”パススルークエリ”を作った場合、実行時に毎回ダイアログからデータソースを選択するか、プロパティシートで「ODBC接続文字列」プロパティを指定しておく必要があります。

パススルークエリのSQLビュー


データソースの選択ダイアログ
ODBC接続文字列プロパティ

クエリに関する属性は、VBAではQueryDefオブジェクトを参照することで取得・設定することができます。よってこのクエリの「ODBC接続文字列」プロパティもまたVBAで操作することができます。それを利用することで、VBAから複数のパススルークエリのリンク先を一括して設定することができます。

また、プログラムから動的にリンク先を切り替えることで、
  • まだ未設定のクエリのODBC接続文字列プロパティを一括設定する
  • 別のサーバーマシン上のSQL Serverに切り替える
  • 同じSQL Server上の別のデータベースに切り替える
  • Accessデータベースを配布する際にユーザーごとに異なるサーバー・SQL Serverを初期設定する
などのことができます。オリジナルのプログラムを作ればリンクマネージャ的なことを行わせることができますので、開発・テスト・運用などさまざまな場面で活用することができます。


次の例では、カレントデータベース上にあるすべてのパススルークエリをピックアップし、それらのリンク先を一括して設定変更しています。
ここではSQL Server名やデータベース名などは固定値ではありますが、これを応用してプロシージャとして作成したり、オリジナルフォームを用意してそこで入力されたSQL Server名やデータベース名、認証方法あるいはログインIDなどを使って処理させることもできます。

Dim dbs As Database
Dim qdf As QueryDef
Dim strConnectStr As String

'リンク先への接続文字列を組み立て
strConnectStr = "ODBC;DRIVER=SQL Server; SERVER=TestSever; DATABASE=TestDB; Trusted_Connection=Yes;"

Set dbs = CurrentDb

'全クエリの探索ループ
For Each qdf In dbs.QueryDefs
  With qdf
    If .Type = dbQSQLPassThrough Then
      'パススルークエリならリンク先を更新
      .Connect = strConnectStr
    End If
  End With
Next qdf

実行後のプロパティシート:


※ここではSQL Server名は「TestSever」、データベース名は「TestDB」、認証方法は「Windows認証」としています。”接続文字列”は状況に応じていろいろな記述パターンがありますので、適宜書き換えてください。
| Index | Prev | Next |

この情報は参考になりましたか?、問題は解決しましたか?、もしまだなら......
T'sWareのワンポイトテクニカルアドバイスをご利用ください。3000円/件〜でご支援します。
スタンドアロンからSQL Server対応まで、オーダーメイドのシステムを短納期・安価でお届けします


Copyright © T'sWare All rights reserved