お手軽が一番、ExcelをOracleクライアントに：Excel‐Oracle連携（2）（2/3 ページ）

[宮本尚志，＠IT]

マクロを使用する方法

　Excelでは、VBAマクロのコードの中にOracleデータベースへの接続や、検索処理の命令を記述できます。コーディングが発生するため、誰でも簡単にできるわけではありませんが、その分柔軟な処理が可能です。

ミドルウェアの選択

　この方法では、VBAでOracleデータベースに接続するためのインターフェイスに何を使うかによって、開発手法も異なります。非常に多くのパターンがありますが、主要なものは、

• ActiveXData Objects（以下ADO）からODBCドライバを使用する方法
• ADOからOLE DBドライバを使用する方法
• Oracle Objectsfor OLE（以下oo4o）を使用する方法

の3つです。どれを使ってもExcelのマクロからOracleデータベースのデータを検索できますが、それぞれに特性があるので、違いを理解したうえでどれを使用するか決定することになります。以下に違いをまとめてみました。

連携パターン	メリット	デメリット
ADO＋ODBC	・汎用的なADOのAPIを使用して開発できる	・Oracleデータベース固有のデータ型やパラメータなどが使用できない ・ODBCデータソースの設定が必要 ・ODBCブリッジを介すため、ADO+OLE DBよりパフォーマンスが劣る
ADO＋OLE DB	・汎用的なADOのAPIを使用して開発できる	・Oracleデータベース固有のデータ型やパラメータなどが使用できない ・oo4oよりはパフォーマンスが劣る
oo4o	・追加的に必要なミドルウェアがないため、バージョン間での整合性などを意識する必要がない ・Oracleで実装されているすべての機能を利用可能 ・PL/SQLを記述可能 ・DAOに類似したインターフェイスを利用し、プロパティ、メソッドを用いることでプログラミングが容易 ・oo4oのパラメータを変更することで、oo4o自体のチューニングが可能、高パフォーマンス	・Oracle以外のRDBMSには接続できない ・コーディングに使用するメソッドなどはoo4o固有
表2　VBAのマクロで使用するミドルウェアごとの違い

　それぞれの開発手法での実際のマクロは以下のようになります。このマクロでは、SIDまたはDSN名がorclであるデータベースに対して接続し、「商品情報」「商品分類」という表から検索した結果をExcelシートの4行2列目に出力します。リスト1?3に掲載しているのは、そのマクロの主要部分を抜粋したものです。赤字部分はそれぞれデータベースユーザー名、パスワード、接続記述子（ADO＋ODBCではDSN名）で、環境により異なります。SQL文に日本語の列名や表名などが含まれる場合は、二重引用符「"」で囲むことに注意してください。

Private Sub CommandButton1_Click() On Error GoTo Err_Han          If oraconn.State = 0 Then         Call Conn     End If          ' データ取得メイン処理を呼び出す     getProductInfo     Exit Sub      Err_Han:     ' エラー処理     MsgBox (Err.Description) End Sub 'データベース接続処理 Private oraconn As New ADODB.Connection Private Sub Conn()     ConnectionString = "DSN=orcl;UID=scott;PWD=tiger"     oraconn.ConnectionString = ConnectionString     oraconn.Open End Sub 'データ取得メイン処理 Private Sub getProductInfo()          ' 変数定義     Dim rs As ADODB.Recordset      ' レコードセット（商品情報）          ' データ取得処理     Set rs = selectProducts          ' データが取得されなかったら何もしない     If rs Is Nothing Then         Exit Sub     End If          ' 商品情報データをシートに格納     Call setDataView(rs, theSheet.Cells(4, 2))        ' レコードセットクローズ     rs.Close End Sub 'データ取得処理 Private Function selectProducts() As ADODB.Recordset     Dim strSql As String       Dim strSql As String       strSql = " select ""商品ID"",""商品名"",""分類名"", " & _              "        ""内容量"",""内容量単位"",""原材料""," & _              "        ""保存方法"",""賞味期限"",""価格"" " & _              "   from ""商品情報"",""商品分類""　" & _              "  where ""商品情報"".""分類ID"" = ""商品分類"".""分類ID""          Set selectProducts = oraconn.Execute(strSql)      End Function

リスト1　ADO＋ODBCの場合のデータベースへの接続（表示の都合で改行されています。コードはlist01から確認できます）

'〜 ボタンクリックのプロシージャはADO＋ODBCと同一なので省略 〜 Private oraconn As New ADODB.Connection Private Sub Conn()     ' データベースに接続する     ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=scott;Password=tiger;"          oraconn.ConnectionString = ConnectionString     oraconn.Open End Sub '〜 データ取得処理はADO＋ODBCの場合と同一なので省略 〜

リスト2　ADO＋OLE DBの場合のデータベースへの接続（表示の都合で改行されています。コードはlist02から確認できます）

Private Sub CommandButton1_Click() On Error GoTo Err_Han          If OraDB Is Nothing Then         Call Conn     End If          If OraDB.ConnectionOK = False Then         Call Conn     End If          ' データ取得メイン処理を呼び出す     getProductInfo     Exit Sub      Err_Han:     ' エラー処理     MsgBox (Err.Description) End Sub Private OraSess As OraSession Private OraDB As OraDatabase  ' データベース接続処理 Private Sub Conn()     Set OraSess = CreateObject("OracleInProcServer.XOraSession")     Set OraDB = OraSess.OpenDatabase("orcl", "scott" & "/" & "tiger", 0&) End Sub 'データ取得メイン処理 Private Sub getProductInfo()          ' 変数定義     Dim oraDS As OraDynaset      ' レコードセット（商品情報）          ' データ取得処理     Set oraDS = selectProducts()          ' データが取得されなかったら何もしない     If oraDS Is Nothing Then         Exit Sub     End If          ' 商品情報データをシートに格納する     Call setDataView(oraDS, theSheet.Cells(4, 2))        ' レコードセットクローズ     oraDS.Close End Sub 'データ取得処理 Private Function selectProducts() As OraDynaset     Dim strSql As String       strSql = " select ""商品ID"",""商品名"",""分類名"", " & _              "        ""内容量"",""内容量単位"",""原材料""," & _              "        ""保存方法"",""賞味期限"",""価格"" " & _              "   from ""商品情報"",""商品分類""　" & _              "  where ""商品情報"".""分類ID"" = ""商品分類"".""分類ID""          Set selectProducts = OraDB.CreateDynaset(strSql, 0&)      End Function

リスト3　oo4oの場合のデータベースへの接続（表示の都合で改行されています。コードはlist03から確認できます）

　上記を一部編集したマクロを含むExcelシートと、表作成用のSQLスクリプトを以下からダウンロードできます。

　・サンプルのダウンロード（demo.zip）

　マクロを使用する方法では、MS Queryよりも柔軟な検索処理が可能です。あらかじめ帳票のようなExcelシートを作成しておき、その中で特定のセルにデータベースからの検索結果を出力することも可能ですし、検索のみならず更新処理を行うことも可能です。クライアントにOracleClientは必要になりますが、マクロを含んだExcelファイルを配布すれば、受け取った人はそのファイルを開いてマクロを実行するだけで、データベースからの検索を行うことが可能です。

　ただ、マクロをコーディングするのは手間がかかります。次に説明するKeySQLを使用すると、同等の処理をもっと簡単に行うことが可能です。（次ページに続く）