2017
5
01

ExcelVBAとAccessの連携 番外 クラスで処理をまとめる

数年前に書いたこのシリーズのコード。もちろん間違ってはないし、きちんと動くものではあるのですが。でも、今ならもっとスマートなコードが書けるなと思って! 自分の過去のコードの供養のためにも、リファクタリングしたものを書き残しておきます。


関連記事

この連載がもっと実用的なサンプルで書籍になりました!

はじめに

最近Excelでもクラスモジュールを使うようになってきまして。

こういう記事も書いているうちに、昔書いた、DBと連携させるコードに自分でモニョってきて書き直しました。

クラスを作ってDB関係の処理は全部そちらにおまかせ、標準モジュールに書く処理は最低限にしちゃおう! という内容です。

タスクごとにDB接続/切断する場合

DataBase クラスモジュール

DBに接続したり処理したりする部分の実働部隊。新規クラスモジュールを作って、オブジェクト名をDataBaseとしています。

ファイル名とSQLを引数として受け取る形になっているので、このクラスを作っておいてインポートしちゃえばかなり汎用的につかえるんじゃないかなーと。

4行目と11行目から始まってるのが接続・切断するプロシージャ。

17行目から始まってるのが、ファイル名、SELECT文、クリア範囲、出力基点セルを受け取ってレコードセットを出力する関数。出力方法1のほうが1行で済んで楽ですが、書式設定が消えちゃうことがあるので、それが嫌な場合は方法2のほうがおすすめ。使うときはどちらかコメントアウトしてくださいね。

69行目と90行目から始まってるのが、INSERT/UPDATE/DELETE文を処理する関数ですが、前者が「1つのSQL文(String型)」、後者は「1つ以上のSQL文リスト(Collection型)」を受け取って処理します。後者は、持ってきた複数の文の中に1つでもエラーが起こると全部なかったことになるので、よりトランザクションのありがたみがわかります。

Module1 標準モジュール

DataBaseクラスを利用する側の記述です。

宣言セクションで、ファイル名をPrivateの定数としておけば便利。

4行目から始まってるのが、SELECT文で抽出したいとき。さきほど作ったクラスのインスタンスを作って、クラスのdb.getRecordSet関数へファイル名、SELECT文、クリア範囲と出力基点のセルを投げて(16行目)、接続・実行・切断までをおまかせしちゃいます。その結果のみをTrue/Falseで受け取るので、こっちの記述は超スッキリ!!!

21行目から始まってるのが、INSERT/UPDATE/DELETE文を実行したいとき。インスタンスを作って、クラスのdb.executeSingleSQL関数へ単一のINSERT/UPDATE/DELETE文を投げています(29行目)。

34行目から始まってるのは、複数のINSERT/UPDATE/DELETE文を一度に実行したいとき。Collection型のSQL文を用意して、db.executeMultiSQL関数へ投げています(45行目)。

DB接続/切断を頻繁にやりたくない場合

更になんですが、今まで書いてきたのって、1回の読込、書込ごとにいちいち同じDBに接続、切断、ということをしてたんですよね。それを最小限にしたいなー、っていう場合。

UserFormを使って、開くときに接続して閉じるときに切断すれば、Formが開いてる間は接続が保持されるので、なんかスマートなんじゃね? って考えたら、こうなりました。

DataBase クラスモジュール

上に書いたのとだいたい一緒なんですが、下3つのプロシージャの中にあった、接続・切断の呼び出しがなくなりました。引数も減っています。

Module1 標準モジュール

宣言セクションで、ファイル名と一緒にクラス用のインスタンス変数を Private 宣言しておけば、このモジュール全体で使えます。インスタンス生成してDB接続するプロシージャと、切断のプロシージャをここで書いておきます(9~17行目)。

他はだいたい一緒ですが、下3つのプロシージャではインスタンスを生成する必要がなくなりました。

showFormプロシージャにて、Form を開くことがトリガーになります。

UserForm1 フォームモジュール

こう書いておけば、フォームが開くときに接続、閉じるときに切断してくれます。ボタンが3つあるとして、そのボタンから標準モジュールのプロシージャを呼び出せば、ボタンクリックで読込・書込ができます。

これなら接続・切断は1度きりで、いろいろ処理できるからスマートなんじゃないかなー。

以上です! いつも記事にするときには、そのときの自分の中のベストで書いているつもりなんですが、数年経つとモニョりますねw 成長できてるということかな…。。

追記:レコードセットを返す

わざわざインスタンス作らなくてもいいから! クラスじゃなくて良いし、なんなら書き込みもしないから、読み込みだけ効率よくレコードセットで欲しいんだよ!!! という状況に遭遇したのでそれも書いておきます。

DataBase 標準モジュール

シートに吐き出すんじゃなくて、返り値の型をレコードセットにしちゃう。

Module1 標準モジュール

こっちのほうが便利な場面もあるかも。

  • このエントリーをはてなブックマークに追加
  • follow us in feedly 647
  • RSSを登録

公開日:2017/05/01
更新日:2017/06/26


コメントを残す




*印は必須項目です。コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にですがスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)


back to top