Linuxサーバより愛を込めて。

Linux(Ubuntu)サーバとダーツを愛する中年サラリーマンの日記。

Microsoft Office

Oracle内のテーブル一覧からパススルークエリを生成する

2015/04/01

テーブル一覧と、そこに接続するパススルークエリを一括で作る

以前、Oracleのテーブル一覧を取得する方法について書きました。

 

Oracleデータベースよりテーブル一覧を取得する

 

今回はVBAと組み合わせることでAccess内にパススルークエリを一括生成してみます。

 

まずはテーブル一覧を取得するSQLをおさらい

Oracleのテーブル名一覧を一覧するSQLは

 

 

でした。これを基にしてVBA内でコードを組んでみると・・・

 

 

まずはエラー時の処理として「On Error ~」の文が入ってます。何かしらトラブルが発生したらErr_MAKE_PASS:行まで飛んでエラーメッセージを返してくれます。

 

続いて変数の宣言では

 

  • レコードセットとして使用する変数「rs」
  • パススルークエリ作成時に使用する「pass_Q」
  • SQL文を格納する文字列「strSQL」
  • ODBC接続文字列を格納する「strCONNECT」

 

を定義しました。

 

データベースに接続してテーブル一覧を得るためにはODBC接続文字列が必要なので(この辺りは改めてまとめてみます)、最初に格納してしまいます。ここはお使いの環境によって変わると思われるので適した値を入れてください。

 

つづいてstrSQLにSQL文を代入します。上でおさらいしたそのまんまですね。

 

テーブル一覧の結果をレコードセットに格納

SQLが出来上がったのでまずは一覧を取得してレコードセットに突っ込みます。ここで一旦パススルークエリを作成する必要があるので

 

 

という具合にしてみました。

 

最初のOn Error ~は「ここでエラーが発生した場合は無視してそのまま次の行へ進むよ」という意味です。

 

そのエラーが発生するかもしれない次の行は「"Q_テーブル一覧"というクエリを削除します」という処理。もともと該当する名称のクエリが存在しなければ削除時にエラーとなるためこんな風にしました。

 

その後は「createquerydef」メソッドで新しいクエリを作成すると共にパススルークエリとして動くよう設定を行っています。

 

最後に「Set rs = ~」でクエリの結果をレコードセットに格納した後、レコードセットの先頭に移動して続きの作業に備えています。

 

レコードセットの値を移動させつつパススルークエリを生成する

先ほど作成したレコードセットを使ってパススルークエリを作成していきます。こんな感じ↓になりました。

 

 

クエリを作って、それをパススルーになるように設定して、SQLは「レコードセットの値:table_nameから全てのデータを引っ張る」ためのSQL文。

 

パススルーを作成したら次のレコードセットへ移動。

 

これをrsが空っぽになるまで繰り返します。

 

全部つなげるとこんな感じ。

 

 

試しに動かしてみたら無事全てのテーブルに接続するパススルークエリが出来上がりましたとさ。

 

まとめ

一度使えば当分、または二度と使わないかもしれない機能かも・・・と思いながら作ってみたモジュールです。

 

今回はOracleを対象にして動かしたんですが、基本的には他のデータベースソフトでも使えるはず。変えるとしたら「テーブル一覧を取得する」ところのSQLを直すぐらいで、Access側の処理は変えなくても動くと思っています。違ったらスミマセン。

 

では今回のまとめを。

 

  • SQL文:「SELECT TABLE_NAME FROM USER_TABLES」でテーブルの一覧を取得する
  • クエリを作成するには「Set pass_Q = CurrentDB.CreateQueryDef("クエリ名")」
  • 取得した結果をレコードセットに格納するのは「Set rs = CurrentDB.OpenRecordset("クエリ名")」
  • Do Loop構文を使ってrsが空っぽになるまで処理を繰り返してパススルーを作成する

 

といった感じでしょうか。会社で使っているシステムが変わった影響で今まで動かしていたmdbの修正がなかなか終わらず苦労しています。

 

これらを作った前任者はとても偉大だなぁ・・・なんて思いながらメンテを続ける毎日です。

-Microsoft Office
-, ,