Microsoft Office

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

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

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

 

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

 

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

 

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

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

 

SELECT TABLE_NAME FROM USER_TABLES

 

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

 

Public Sub MAKE_PASS()

'エラー発生時の処理
On Error GoTo Err_MAKE_PASS
    
'変数の宣言
    Dim rs As DAO.Recordset
    Dim pass_Q As DAO.QueryDef
    Dim strSQL As String
    Dim strCONNECT As String
    
'ODBC接続文字列を定義
    strCONNECT = "ODBC接続文字列"
    
'テーブル一覧を取得する
  'SQLの作成
    strSQL = "SELECT TABLE_NAME FROM USER_TABLES"
 ～ 

Err_MAKE_PASS:
MsgBox "エラー発生:" & Error()
    Exit Sub

Exit_MAKE_PASS:
    MsgBox "終了しました。"
    DoCmd.DeleteObject acQuery, "Q_テーブル一覧"
    Exit Sub

End Sub

 

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

 

続いて変数の宣言では

 

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

 

を定義しました。

 

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

 

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

 

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

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

 

'テーブル一覧を取得するパススルークエリを作成
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Q_テーブル一覧"
    
    Set pass_Q = CurrentDb.CreateQueryDef("Q_テーブル一覧")
    pass_Q.Connect = strCONNECT
    pass_Q.SQL = strSQL
    pass_Q.ODBCTimeout = 10
    pass_Q.ReturnsRecords = True
    
'一覧をレコードセットに格納
    Set rs = CurrentDb.OpenRecordset("Q_テーブル一覧")
    rs.MoveFirst

 

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

 

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

 

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

 

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

 

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

 

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

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

 

Do Until rs.EOF
    Set pass_Q = CurrentDb.CreateQueryDef(rs("table_name").Value)
    pass_Q.Connect = strCONNECT
    pass_Q.SQL = "select * from " & rs("table_name").Value
    pass_Q.ODBCTimeout = 10
    pass_Q.ReturnsRecords = True
      
    pass_Q.Close
    rs.MoveNext
Loop
rs.Close

 

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

 

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

 

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

 

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

 

Public Sub MAKE_PASS()

'エラー発生時の処理
On Error GoTo Err_MAKE_PASS
    
'変数の宣言
    Dim rs As DAO.Recordset
    Dim pass_Q As DAO.QueryDef
    Dim strSQL As String
    Dim strCONNECT As String
    
'ODBC接続文字列を定義
    strCONNECT = "ODBC接続文字列"
    
'テーブル一覧を取得する
  'SQLの作成
    strSQL = "select table_name from user_tables "
  'テーブル一覧を取得するパススルークエリを作成
    On Error Resume Next
    DoCmd.DeleteObject acQuery, "Q_テーブル一覧"
    
    Set pass_Q = CurrentDb.CreateQueryDef("Q_テーブル一覧")
    pass_Q.Connect = strCONNECT
    pass_Q.SQL = strSQL
    pass_Q.ODBCTimeout = 10
    pass_Q.ReturnsRecords = True
    
'一覧をレコードセットに格納
    Set rs = CurrentDb.OpenRecordset("Q_テーブル一覧")
    rs.MoveFirst

'レコードセットに格納したテーブル一覧へのパススルーを順次作成する
    On Error Resume Next
    DoCmd.DeleteObject acQuery, rs("table_name").Value
    
    Do Until rs.EOF
        Set pass_Q = CurrentDb.CreateQueryDef(rs("table_name").Value)
        pass_Q.Connect = strCONNECT
        pass_Q.SQL = "select * from " & rs("table_name").Value
        pass_Q.ODBCTimeout = 10
        pass_Q.ReturnsRecords = True
        
        pass_Q.Close
        rs.MoveNext
    Loop
    rs.Close
    
    GoTo Exit_MAKE_PASS
    
Err_MAKE_PASS:
    MsgBox "エラー発生:" & Error()
    Exit Sub
    
Exit_MAKE_PASS:
    MsgBox "終了しました。"
    DoCmd.DeleteObject acQuery, "Q_テーブル一覧"
    Exit Sub
    
End Sub

 

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

 

まとめ

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

 

今回は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
-, ,