【Access+VBA】入力フォーム付きのレポート作成時のポイント

ここ最近、Access 2016でレポートと参照系アプリを作成する機会がありました。

今までVisual Basic 6や.NETでは作ったことがありますが、Accessでも可能なようです。

ちなみに、今回作成したアプリはフォームで抽出条件を指定し、その条件に基づいてバックエンドDBであるOracle Databaseからデータを取得して帳票出力する、というもの。

ただ、今回作成したアプリは

Accessファイルは複数人で共有せずローカルPC環境にコピーして実行させる

という方法を採りました。
共有だとファイル破損があるとネットでよく見るので。

で、今回は開発時のポイントをまとめてみました。
あと、次のことも解説しています。

ADOによるデータ取得の簡単なコーディング例
複数選択リストボックスの値取得方法
レポートのグループ化・並び順について（簡単に）

また、調べながら作成したため多少内容がごちゃごちゃしていますので、ご参考までにお願いします。

なお、Access 2013, 2016で動作確認してます。

全体像
開発中につまづいた・気づいたことなど
ADOでデータ取得
複数選択リストボックスの値を取得する
レポートのグループ化・並び順

全体像

今回できたAccessの全体像は、こちら。
VBAで動的SQLを生成してOracleに直接投げ、その結果を取得して一時テーブルに格納しています。
レポートのレコードソースには一時テーブルをセット。

一時テーブルを使った理由は、「レポートのレコードソースにパススルークエリはセットできない」と当初思っていたからですが、実はこれは誤りだと後に分かりました。

レポートのレコードソースにパススルークエリはセットできます。
ってことは、もっとシンプルな構造にできたわけですね。

つまり、これでもいけた（下の図）ようです。
（パフォーマンスにどれだけ影響が出るだろう…）

開発中につまづいた・気づいたことなど

「クエリ」は遅い

最初、リンクテーブルを使用しましたがレポート出力に30分かかり、パススルークエリか動的SQLをVBAで組むか…という検討をしました。

※ちなみに、動的SQLはパススルークエリとほぼ同じように考えてもらって良いです。バックエンドDBに送るSQLをAccessオブジェクトとして持つのがパススルークエリなので。
あと、VBAで組んだ動的SQLをパススルークエリの内容にセットすることも可能です。

パススルークエリについては、以下のMicrosoftのサポートページで説明されています。

パススルークエリを作成する - Microsoft サポート

Accessでパススルークエリを作成する方法について学習します。

↑のサイトからの引用ですが、

「ほとんどのクエリは Access ACE データベースエンジンでローカルに実行されます」とあります。
つまり、リンクテーブルはテーブルのデータ全体を一旦取得しているのでしょう（でないとAccess側でSQL実行できない）。

なので、普通のVBアプリと同様、DBサーバー（上のサイトの言葉で言えばバックエンドサーバー）にSQLを投げ、結果のデータだけ取得する方法にしないと、遅くて使い物になりません。

とうわけで、①パススルークエリを使うか、②VBAで動的SQLを組んでデータを取得する、のどちらかをしないとパフォーマンス的に良くなりません。

パススルークエリは、普通のクエリのように、抽出条件だけプロパティ指定することができません（SQL全体をセットできるのみ）。

今回は、抽出条件が複雑になるというのもあり、VBAでコーディングしました。

参考
・Microsoftサポートパススルークエリを作成する

レポートのRecordsetプロパティにはDAO.Recordsetをセットできない

バックエンドDBから取得したデータはDAOならDAO.Recordsetにセットしますが（ADOならADODB.Recordset）、このオブジェクトを直接レポートにセットできれば一時テーブルなんて要らないよね？と思い、試してみました。

というのも、このMicrosoftのドキュメントによれば、それが出来るように読めるから。

でも、実際はできませんでした。

詳しくは別記事に記載していますので、ご興味があれば参照下さい。

本家Micorosftのドキュメントなのに、情報が正確ではないようです。

クエリの抽出条件だけをVBAから動的に変更することはできないらしい

パススルークエリではなく、Accessオブジェクトのクエリのほう。

今回、複数部署を指定したりと複雑な抽出条件になったので、VBAで動的に抽出条件を変更したかったのですが、それができないようです。

複数選択のリストボックスはクエリの抽出条件に直指定できない

クエリの抽出条件に、[Forms]![フォーム名]・・・とすると画面上の値を、パラメーターとしてクエリに指定できます。

テキストボックスやコンボボックスなら問題ないのですが、複数選択を可能にしているリストボックスの場合は抽出条件に指定できません。

VBAで動的にSQLを組む必要があります。

Microsoftの↓のドキュメントも、動的にSQLを組んじゃっていますね。
やはり、それしかないようです。

検索フォームの値を使用して動的クエリを作成する - Office

検索フォームを作成する方法と、それを使用してBuildCriteriaメソッドと検索フォームの値を使用して適切なSQL文字列を動的に構築する方法について説明します。

↓のページもヒントがありそうに思いましたが、ポップアップを表示させる方法でした。

クエリの実行時にパラメーターを使って入力を求める - Microsoft サポート

Accessデスクトップデータベースクエリにパラメーターを追加して、結果をフィルター処理するための抽出条件の入力を求めるようにします。

↓のページでは、フォームの項目をクエリの抽出条件に指定する方法が解説されています。

クエリ、フォーム、レポートでパラメーターを使用する - Microsoft サポート

この記事では、クエリ、フォーム、およびレポートでのパラメーターの使用を強化するためのAccessでのフォームの使用方法について説明します。

参考サイト
・Accessで検索フォームの値を使用して動的クエリを作成する方法（Micorosoft)
・クエリの実行時にパラメーターを使って入力を求める（Microsoft）
・クエリ、フォーム、レポートでパラメーターを使用する（Microsoft）
・クエリの抽出条件の例（Micorosft）

ADOでデータ取得

今回、VBAでかなりの量のコーディングをしました。

データベースへのアクセスは標準ではDAOが使用できますが、今回はADOを使用しています。

DAOやADOが何かについて以下のページを参照下さい（もしくはググって…）。

DAOよりADOのほうが良さげ

DAOは追加の参照設定なしで使用できますが、後継と位置付けれれているADOがパフォーマンス面では有利とMicrosoftの記事にも書かれています（但し厳密な検証はしていません）。
ただ、ODBCを経由する場合は（今回はODBCを経由している）、OLE DBからODBCを経由すると思うので、実際のパフォーマンスは何とも言えなさそうです。

ちなみに今回、DAOとADOのどちらも試していますが、体感的な速度に違いはありませんでした。

ADOによるデータを取得の方法（SELECT文の結果取得方法）

①参照設定の追加

DAOと違って参照設定を追加する必要があります。

別記事の「ADOの参照設定方法」を参考にして下さい。

② コーディング例

最小限のコードを紹介します。
※実際作ったものはもう少し複雑ですが、ネットには公開できません…。

今回の場合だと、結果を取得する箇所で一時テーブルにデータをINSERTしています。


Private con As ADODB.Connection
Private rs As ADODB.Recordset

Private Sub GetData()
  Dim strSQL As String

  Set con = New ADODB.Connection
  Set rs = New ADODB.Recordset

  'DBオープン
  con.Open "DSN=orcl;UID=userid;PWD=passwd;"

  'SQL作成
  strSQL = "select dummy from dual"

  'SQL実行・結果取得
  rs.CursorType = adOpenStatic
  rs.Open strSQL, con

  '結果を取得
  Do Until rs.EOF
    'ここで一時テーブルへデータをセットしていく

    rs.MoveNext
  Loop

  con.Close
  Set rs = Nothing
  Set con = Nothing

End Sub

DB接続の状態は、ConnectionオブジェクトのStateプロパティで取得可能です。

ちなみに、「結果を取得」の箇所では、”Nz(rs!列名)”のように書きます。
Nzを使用したのは、値がNullだとエラーで落ちるから。

参考サイト
・Nz 関数
・Microsoft ActiveX データオブジェクトリファレンス（ADOに関するMSのリファレンス）

複数選択リストボックスの値を取得する

これもVBAで実装する必要があります。
クエリの抽出条件に指定しても、複数選択の場合は機能してくれません。

以下、コーディング例です。
listbox1というリストボックスがあるとして、そのクリックイベント。
listbox1がクリックされる度にlistbox1の選択値を取得する…という例。


Private Sub listbox1_Click()
    Dim varItem As Variant
  
    With Me.listbox1
        If (.ItemsSelected.Count > 0) Then
            Set varItem = Nothing
            '選択項目をループ
            For Each varItem In .ItemsSelected
                
            Next
        End If
    End With

End Sub

これで、SQLのWhere句が作れそうです。