Toggle FullScreen
More Info

外部DBを使ってAccessのReportを作る方法

Back to the blog

概要

Summary

この記事では、Oracleのような外部データを元にして、Access（MDB）でReportを作る方法について記します。

Formと違いReportでは接続していないDBのレコードセットをRecordsetプロパティにセットすることはできません。
しかし、Access(MDB)を使ってRecordSourceプロパティにIN句を含んだSQLを設定することで、実現できます。Accessプロジェクト(ADP)では、未接続のデータベースに対してIN句を含んだSQLを設定することはできません。

動作確認環境

WindowsXP
Microsoft Access 2002 SP3

Oracleに接続するために必要な情報は以下のとおり。
サーバー：myDB
ユーザー名：myDBA
パスワード：myDB

Wordの差込印刷とAccessのレポート

RDBから帳票を作る時に便利なのが、Wordの差込印刷と、AccessのReport。私は、文章（ドキュメント）を作る時はWord、表形式の帳票を作る時はReportと使い分けています。小規模ならば、AccessやExcelにデータをためておいて、そこから帳票を作る。しかし、業務システムなど大規模なものになると、DBとしてOracleなどの商用DBを使うことになります。

ここでひとつの問題となるのが、Oracleなどの外部DBにあるデータをつかって、クライアント側で帳票を作る時にどうするかということ。

ここでは、諸事情によりリンクテーブルは使わないこととします。

単にデータの内容を確認したいだけならば、ADOでレコードセットを作成して、CopyFromRecordsetメソッドでExcelのワークシートに貼り付けるのがお手軽です。
Wordの差込印刷を使うならば、以前の記事で紹介したように、SQLを設定することになります。

では、Accessのレポートは？

ReportオブジェクトのRecordsetプロパティ

MDBでは無効だと怒られる

AccessのReportオブジェクトには、Recordsetプロパティが用意されています。FormオブジェクトやComboBox、ListBoxにもRecordsetプロパティがありますので、期待がもてます。

しかし、結果は期待はずれです。

AccessのReportオブジェクトのRecordsetプロパティにADOのRecordsetをセットすると、次のような実行時エラーが表示されます。

Err_2593
「実行時エラー ‘2593’: この機能は MDB では無効です。 」

このエラーはMDBでReportにRecordsetをバインドするとでる実行時エラー。
RecordsetのCursorLocationプロパティがadUseServerでもadUseClientでも同じです。

このエラーについて、Microsoftのサポートでは、次のように説明しています。
Access データベースのレポートの Recordset プロパティを設定または取得すると、実行時エラー 2593 が発生することがある


Recordset プロパティのヘルプ トピックには、このプロパティがレポートに適用されると記述されています。これは、レポートが Microsoft Access プロジェクト (.adp ファイル) にあり、レポートを ADO レコードセットにバインドしているか、レポートから ADO レコードセットを取得している場合にのみ該当します。Recordset プロパティは Microsoft Access データベース (.mdb ファイル) のレポートには適用されません。

要は、「ヘルプには書いてないが、このプロパティはAccess プロジェクト(.adpファイル)でしか機能しないぞ」ということらしい。なんと不親切な。

ADPではレポートが開かない

では、ADPでやってみましょう。
まずは、確実に動作するFormオブジェクトで確認してから、Reportオブジェクトに適用してみます。

MDBからレコードセットを作成

Private Sub Report_Open(Cancel As Integer)

'For Mdb
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mySQL As String

    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " FROM KIHONHYO"
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"

Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.Access.OLEDB.10.0"
        .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source").Value = "C:\TEST.mdb"
    End With
    cn.Open
    
Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = cn
        .Source = mySQL
        .CursorLocation = adUseServer
        .Open
    End With

Set Me.Recordset = rs

    rs.Close
    cn.Close
    
Set rs = Nothing
Set cn = Nothing

End Sub

Oracleからレコードセットを作成。

Private Sub Report_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mySQL As String
    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " FROM KIHONHYO"
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"

Set cn = New ADODB.Connection
    With cn
        .ConnectionString = "Provider=MSDAORA.1;Password=myDB;User ID=myDBA;Data Source=myDB;Persist Security Info=True"
    End With
    cn.Open

Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = cn
        .Source = mySQL
        .CursorLocation = adUseServer
        .Open
    End With

Set Me.Recordset = rs

    rs.Close
    cn.Close

Set rs = Nothing
Set cn = Nothing
End Sub

結果は不成功です。
MdbからADOで作成したRecordsetを使うと、RecordsetのCursorLocationプロパティがadUseClientでもadUseServerでもレポートが開かない。クラッシュしているということでしょうか。

OracleからADOで作成したRecordsetを使うと、adUseClient の時はレポートが開かない（クラッシュ）。adUseServer だと、実行時エラー。

Err_7965
「実行時エラー ‘7965’　”Recordset/レコードセット”プロパティの設定に、そのオブジェクトは使えません。」

レコードセットを扱う時は、CursorLocationをadUseClientにしておくのが原則のようなところがあるので、このエラーは予想通り。ということは、逆説的に考えると、他の３パターンは何らかの手違いでエラートラップができていないか、バグでクラッシュするかのどちらかと考えるべきなのでしょう。

ReportオブジェクトのRowsourceプロパティ

Jet SQL のIN句で外部データベースに接続

それでは、他の手段を試してみます。

JetのSQLには、独自の仕様として、Jetデータベースエンジンから外部データベースのテーブルを識別するための「IN句」があります。IN句を含んだSQLをReportオブジェクトのRecordSourceプロパティに設定してみます。

Mdb、Adp共に、フォーム、レポート、コントロールのRecordSourceプロパティ、またはRowsourceプロパティとして使用されるSQLステートメント内の文字数上限は32,750。
Microsoft Access 2010の仕様(http://office.microsoft.com/ja-jp/access-help/HP005186808.aspx)
Microsoft Access 2010 および Access 2007 データベースの仕様(http://office.microsoft.com/ja-jp/access-help/HA010341462.aspx)

ADPではエラー

これもまず、Formオブジェクトで動作を確認してから、Reportオブジェクトに設定しています。

MDBの場合

Private Sub Report_Open(Cancel As Integer)
Dim mySQL As String
'For MDB
    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " , KIHONHYO.NAME AS MYNAME"
    mySQL = mySQL & " FROM KIHONHYO IN 'C:\TEST.mdb' "
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"
Me.RecordSource = mySQL
End Sub

Oracleの場合

Private Sub Report_Open(Cancel As Integer)
Dim mySQL As String
'For Oracle
    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " , KIHONHYO.NAME AS MYNAME"
    mySQL = mySQL & " FROM KIHONHYO"
    mySQL = mySQL & " IN '' [ODBC;Driver={Microsoft ODBC for Oracle};SERVER=myDB;UID=myDBA;PWD=myDB;]"
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"
Me.RecordSource = mySQL
End Sub

MDBの場合も、Oracleの場合も、レポートを開くと下記のエラーが表示されます。

Err_sqlserver
「このプロジェクトはSQL Serverデータベースに接続されていないため、この操作を行うことができません。」

分かった、もうADPはあきらめます。期待した私がバカでした。

MDBならRecordSourceプロパティにIN句を含んだSQLが使える

最後に、AccessのReportオブジェクトのRecordSourceプロパティにIN句を含んだSQLを設定してみます。
スクリプトはADPの場合と同じ。
MDBの場合

Private Sub Report_Open(Cancel As Integer)
Dim mySQL As String
'For MDB
    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " , KIHONHYO.NAME AS MYNAME"
    mySQL = mySQL & " FROM KIHONHYO IN 'C:\TEST.mdb' "
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"
Me.RecordSource = mySQL
End Sub

Oracleの場合

Private Sub Report_Open(Cancel As Integer)
Dim mySQL As String
'For Oracle
    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " , KIHONHYO.NAME AS MYNAME"
    mySQL = mySQL & " FROM KIHONHYO"
    mySQL = mySQL & " IN '' [ODBC;Driver={Microsoft ODBC for Oracle};SERVER=myDB;UID=myDBA;PWD=myDB;]"
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"
Me.RecordSource = mySQL
End Sub

この場合は、MDBもOracleも動作します。

ただし、注意点がひとつ。
IN句でOracleへ接続する際、CASE式を使っていると、下記のエラーになります。

例えば、

mySQL = mySQL & " , CASE WHEN KIHONHYO.SYUBETU='1' THEN 'TOKUBETU' ELSE NULL END AS SYUBETU"

というCASE式を含んでいると、

Err_Enzanshi
「クエリ式’CASE WHEN xxxxxx THEN xxxxxx ELSE xxxxxx END’ の構文エラー:演算子がありません。」

とエラー表示されます。

ですので、下記のように書き換えなければなりません。

Private Sub Report_Open(Cancel As Integer)
Dim mySQL As String
    mySQL = "SELECT KIHONHYO.BANGO AS BANGO"
    mySQL = mySQL & " , KIHONHYO.NAME AS MYNAME"
    
    'Oracleに対して、SWITCH関数が作動するが、純正のCASE式はエラー。
    mySQL = mySQL & " , SWITCH(KIHONHYO.SYUBETU='1','HogeHoge') AS SYUBETU"
'    mySQL = mySQL & " , CASE WHEN KIHONHYO.SYUBETU='1' THEN 'HogeHoge' ELSE NULL END AS SYUBETU"
    
    mySQL = mySQL & " FROM KIHONHYO"
    mySQL = mySQL & " IN '' [ODBC;Driver={Microsoft ODBC for Oracle};SERVER=myDB;UID=myDBA;PWD=myDB;]"
    mySQL = mySQL & " WHERE KIHONHYO.NENDO='2012' AND KIHONHYO.SYUBETU<'3'"
Me.RecordSource = mySQL

End Sub

Oracleに対してSQLを送っているのに、Jetの文法なんて、変な感じです。いやいや、IN句自体がJetの独自仕様なんだから、全体をJetの方言で書かなければならないんだよな。
まあ、そもそもIN句を含んだJet書式のSQLを設定しているわけだから、どこかの段階でJetからOracleに書き換えているのでしょう。
「演算子がありません」の構文エラーも、動作の感覚としてクライアントサイドで発行しているっぽいし。

SQLステートメント内の文字数上限は32,750なので、十分実用可能な方法だと思います。

SWITCH関数は処理にむちゃくちゃ時間がかかる

ただ、私の使用法には適していませんでした。

今回帳票を作成するために、SQL上で行→列変換をする必要がありました。CASE式と集合関数、それにGROUP BY句を使ったのですが、ADOでレコードセットを取得するのに１秒以下のものが、上記の方法でSWITCH関数に書き換えた項目を追加すると、４０分かかるように！SWITCH関数と集合関数で記述した１項目がなければ、スムーズに開くレポートが、です。

実際には、CASE式で記述している同様の項目が５０以上あるので、却下。普通にレコードセットを取り出すだけなら数秒なんだけどな。

これだけ試行錯誤したにもかかわらず、結局は作業用の一時テーブルを作ることにしました。

参考資料

Using ADO in Microsoft Access 2002

Using ADO in Microsoft Access 2002

Access(MDB)もしくはAccess Project(ADP)のReportオブジェクトとRecordsetについて、Microsoftが説明しているものはなかなか見つかりませんでした。ようやく見つけたひとつが、「Using ADO in Microsoft Access 2002」。自動翻訳で、「を使用して Microsoft Access 2002 での ADO」なんて邦題になってます。Access2002時代の少々古いものですが、ここでレコードセットと、フォーム・コンボボックスとリストボックス・レポートの関係について説明があります。ここで注目すべき点は、フォームについてはOracleなどの外部データベースから作成したレコードセットを連結するサンプルが示されているにもかかわらず、レポートについては、ADPですでに接続されたデータベースから作成したレコードセットを連結する方法しか提示されていないことです。

Private Sub Report_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
            
   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection
   
   'Create an instance of the ADO Recordset class and open it
   Set rs = New ADODB.Recordset
   rs.Open "SELECT * FROM Customers WHERE CustomerID LIKE 'a%'", cn
   
   'Set the report's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

Microsoftのサポートでは、Microsoft Access プロジェクトのレポートならば、レコードセットをバインドできるかのように説明されていますが、それも、Microsoft Access プロジェクトとすでに接続されたSQL Server に限られるのかもしれません。

IN句

IN句については、下記のサイトで説明があります。

Access 2003(http://office.microsoft.com/ja-jp/access-help/HP001032240.aspx)
Access 2007(http://office.microsoft.com/ja-jp/access-help/HA001231484.aspx)

レコードセットが扱えない場合は、IN句を使って外部データベースに接続する手もあるってことです。
あくまでも非常時の手段でしょうか。

06 11月, 2012
0
By: 
In: 
Back to the blog
Twitter from tokidokidokin
アーカイブ
Portfolio Categories
  • カテゴリーなし
Portfolio Tags