【Excel・Access VBA】データベース接続用クラスで時短プログラミング

スポンサーリンク
VBA

ExcelやAccessでデータベースに接続してデータ取得やデータ更新処理を実行する場合に、ADOを利用して実装することは多いかと思いますが、データベースへの接続処理を行う度に、データベースへの接続文字列を記述したり、データベースへの接続やデータ取得、更新処理の実装方法がバラバラになってしまったり、色々と面倒で手間が掛かります。

そこで今回はVBAでデータベース接続用のクラスを使った、プログラミングを時短テクニックを紹介します。

尚、今回はプログラミング初心者向けとして、VBAにおける「クラスモジュール」についてもざっくり解説しておきます。

因みに、「クラスモジュール」の解説が不要な方は下の目次から、実装手順の記述まで移動してください。

スポンサーリンク

クラスモジュールと標準モジュール

VBAでは「標準モジュール」と「クラスモジュール」があります。

通常、標準モジュールには、「Subプロシージャー」や「Functionプロシージャー」を作り、プログラム全体で繰り返し使われる特定の処理を切り出して部品化します。

この「Subプロシージャー」や「Functionプロシージャー」では、呼び出されると処理が実行され、その処理が終わると、プロシージャー内で持っていた変数も破棄され、実体は残りません。

「クラスモジュール」もプログラムを切り出して部品化することは同じですが、上記のプロシージャーと異なり、呼び出したプログラムを明示的に破棄しない限り、プログラム内部で保持していた変数の値も残り、オブジェクトとしての実体も消えません。これを「インスタンス」と呼びます。

今回のサンプルプログラムでは、クラスモジュール内に、ADOでデータベースの接続情報や接続状態をConnectionオブジェクトに格納し、それを保持します。

もしクラスモジュールを使わずにプロシージャーでデータベース関連処理の共通プログラムを作ろうとした場合は、引数や戻り値でConnectionオブジェクトを受け渡す様な仕組みで実装することになり、プログラムの手間が増えます。

プログラミング初心者の場合、上記の説明だけで理解するのは難しいかと思いますが、今回のサンプルプログラムはクラスを理解しなくても使えるので、無理に理解しようとしなくても大丈夫です。

では、ExcelのVBAで実装する場合と、AccessのVBAで実装する場合のやり方をそれぞれご紹介します。

 

データベース処理クラスの作成方法

今回はQiitaで掲載されている以下の記事のサンプルコードを一部変更して使わせて頂いています。※分かりやすく実装内容が説明してあるので、是非御一読を。

Excel VBAとSQL Serverの連携を楽にしたい -Qiita-

Excel VBAの場合

1.VBAエディタを開いて下準備

・VBAエディタの画面上のメニュー内の「参照設定」から「Microsoft ActiveX Data Object 2.x Library」を選択してチェックを入れます。※以下のスクリーンショットであれば2.xで一番値の大きい2.8で良いかと思います。

・VBAエディタの画面左側をプロパティウィンドウの一番上の項目を選択し、「オブジェクト名」を初期値から任意の名前に変更します。※初期値でもクラスは使えますが、変えておいたほうが扱いやすいので。尚、個人的には日本語名ではなくアルファベットでの名称を推奨します。
後述するサンプルプログラムではオブジェクト名を「test_project」と設定した場合の記述になっています。

 

2.プロパティウィンドウでクラスモジュールを作成

VBAエディタ画面左側の「プロパティウィンドウ」を右クリックして、「クラスモジュール」を新規に作成します。

※プロパティウィンドウが表示されていなければ、画面上部のメニュー内の「表示」から「プロパティウィンドウ」を選択して表示させてください。

新しくクラスモジュールを作成すると、初期値の名前は「Class1」と設定されるので、「DataBaseAccess」と変更してください。

3.クラスモジュールのコードにサンプルプログラムを貼り付け

作成したクラスモジュールのコードに、以下のサンプルプログラムをまるっと貼り付けます。※当サンプルではConnectionで設定する接続文字列はSQL Serverの場合の記述ですが、Oracleなど他のデータベースと接続する場合は、接続文字列だけ変更してもらえれば動作すると思います。

 

4.プロパティウィンドウで標準モジュールを作成

VBAエディタ画面左側の「プロパティウィンドウ」を右クリックして、「標準モジュール」を新規に作成します。

新しく標準モジュールを作成すると、初期値の名前は「Module1」と設定されるので、「DatabaseFactory」と変更してください。

5.標準モジュールのコードにサンプルプログラムを貼り付け

作成した標準モジュールのコードに、以下のサンプルプログラムをまるっと貼り付けます。

 

6.すべて保存して作成完了

ここまで作成したら保存して、データベース関連処理の共通クラスが完成です。※保存時に「次の機能はマクロなしブックに保存できません」とメッセージが出る場合は、

メッセージに記載されいる通り、「いいえ」を選択して次に表示される保存先を指定するダイアログ内で、ファイル形式を「マクロ有効ファイル(xlsm)」を選択して保存してください。

 

Access VBAの場合

1.VBAエディタを開いて下準備

・VBAエディタの画面上のメニュー内の「参照設定」から「Microsoft ActiveX Data Object 2.x Library」を選択してチェックを入れます。

・VBAエディタの画面左側をプロパティウィンドウの一番上の項目を選択し、「オブジェクト名」を初期値から任意の名前に変更します。※初期値でもクラスは使えますが、変えておいたほうが扱いやすいので。尚、個人的には日本語名ではなくアルファベットでの名称を推奨します。
後述するサンプルプログラムではオブジェクト名を「test_project」と設定した場合の記述になっています。

 

2.プロパティウィンドウでクラスモジュールを作成

VBAエディタ画面左側の「プロパティウィンドウ」を右クリックして、「クラスモジュール」を新規に作成します。

※プロパティウィンドウが表示されていなければ、画面上部のメニュー内の「表示」から「プロパティウィンドウ」を選択して表示させてください。

新しくクラスモジュールを作成すると、初期値の名前は「Class1」と設定されるので、「DataBaseAccess」と変更してください。

3.クラスモジュールのコードにサンプルプログラムを貼り付け

作成したクラスモジュールのコードに、以下のサンプルプログラムをまるっと貼り付けます。※当サンプルではConnectionで設定する接続文字列はSQL Serverの場合の記述ですが、Oracleなど他のデータベースと接続する場合は、接続文字列だけ変更してもらえれば動作すると思います。

 

4.プロパティウィンドウで標準モジュールを作成

VBAエディタ画面左側の「プロパティウィンドウ」を右クリックして、「標準モジュール」を新規に作成します。

新しく標準モジュールを作成すると、初期値の名前は「Module1」と設定されるので、「DatabaseFactory」と変更してください。

5.標準モジュールのコードにサンプルプログラムを貼り付け

作成した標準モジュールのコードに、以下のサンプルプログラムをまるっと貼り付けます。

 

6.すべて保存して作成完了

ここまで作成したら保存して、データベース関連処理の共通クラスが完成です。

データベース接続クラスの使い方

今回のクラスでは、ADOを使用して以下の操作が行えます。

  • データベースへの接続
  • SQLの実行
  • トランザクション管理

使い方のサンプルプログラムを以下に記載します。

SELECT文を実行してレコードセットを取得する場合

 

UPDATE文を実行し、トランザクション管理も併せて実施する場合

 

最後に

今回のクラスは、私自身業務で使用しており、Excelでデータ自動取得するような集計表や、Accessでサブシステムなんかを新規に作る場合に、まずは上記の手順でデータベース接続クラスを作ってから、メインの処理を作ります。

使い方を覚えてもらえれば、VBAのプログラミングの効率が格段に向上するため、是非活用して頂ければと思います。

それでは、今回も閲覧して頂きましてありがとうございました。

ストアドプロシージャの実行にも対応しました。以下の記事で掲載しています。是非こちらも御一読ください。

【Excel・Access VBA】ADOでストアドを実行するサンプルプログラム
今回はVBAからADO経由でデータベースサーバーのストアドプロシージャを実行する方法を紹介します。 尚、以前にVBAのADOを使用したデータベースの接続関連のクラスを紹介させていただきましたが、今回はそのクラスをベースに、ストアド実行...