【Excel・Access 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など他のデータベースと接続する場合は、接続文字列だけ変更してもらえれば動作すると思います。
2021年9月追記
SQL Server に依存するコードが一部存在するため、接続先のデータベースがOracleなどの別のデータベースの場合は、以下のコードの42行目から48行目まで、及び64行目から66行目までをコメントアウトしてください。

Option Explicit
Private mCon As ADODB.Connection

'Connectionオブジェクトを生成
Public Sub connect()
    Dim Cn As String
    Dim srvName As String
    Dim dbName As String
    Dim loginName As String
    Dim loginPass As String

    'データベース接続情報を定義します。'
    srvName = "サーバホスト名 or IP"
    dbName = "データベース名"
    loginName = "ユーザー名"
    loginPass = "パスワード"
    
        Cn = _
            "Driver={SQL Server};" & _
                " server=" & srvName & "; database=" & dbName & "; uid=" & loginName & "; pwd=" & loginPass & ";"

    Set mCon = New ADODB.Connection

    mCon.CursorLocation = adUseClient
    mCon.Open Cn
End Sub

'データベースへの接続を切断します。
Public Sub disconnect()
    mCon.Close
    Set mCon = Nothing
End Sub

'引数のSQL文を実行し、ADODB.Recordsetを返します。
Public Function execute(sql As String) As ADODB.Recordset
    Dim rs As New ADODB.Recordset

    'タイムアウト設定 (30分)
    mCon.CommandTimeout = 60 * 30

    '処理された行数を示すメッセージが結果セットの一部として返されないようにする
    mCon.execute ("SET NOCOUNT ON")

    '警告メッセージが結果セットの一部として返されないようにする
    mCon.execute ("SET ANSI_WARNINGS OFF")

    'オーバーフローおよび0除算時にはNULLを返す
    mCon.execute ("SET ARITHABORT OFF")


    rs.Open sql, mCon, adOpenStatic, adLockBatchOptimistic

    Do
        'レコードの操作ができるオブジェクト若しくは次のRecordSetがとれず、コネクションが空になった場合終了
        If rs.State = adStateOpen Or rs.ActiveConnection Is Nothing Then
            Exit Do
        End If
        Set rs = rs.NextRecordset()
    Loop

    Set execute = rs

    '設定OFF
    mCon.execute ("SET NOCOUNT OFF")
    mCon.execute ("SET ANSI_WARNINGS ON")
    mCon.execute ("SET ARITHABORT ON")
End Function

'トランザクションを開始する
Public Sub BeginTransaction()
    mCon.BeginTrans
End Sub

'トランザクションをコミットする
Public Sub CommitTransaction()
    mCon.CommitTrans
End Sub

'トランザクションをロールバックする
Public Sub RollbackTransaction()
    mCon.RollbackTrans
End Sub

 

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

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

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

 

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

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

Option Explicit

'インスタンスを生成する
Public Function creater() As test_project.DataBaseAccess
    Set creater = New test_project.DataBaseAccess
End Function

 

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など他のデータベースと接続する場合は、接続文字列だけ変更してもらえれば動作すると思います。

Option Explicit
Private mCon As ADODB.Connection

'Connectionオブジェクトを生成
Public Sub connect()
    Dim Cn As String
    Dim srvName As String
    Dim dbName As String
    Dim loginName As String
    Dim loginPass As String

    'データベース接続情報を定義します。'
    srvName = "サーバホスト名 or IP"
    dbName = "データベース名"
    loginName = "ユーザー名"
    loginPass = "パスワード"
    
        Cn = _
            "Driver={SQL Server};" & _
                " server=" & srvName & "; database=" & dbName & "; uid=" & loginName & "; pwd=" & loginPass & ";"

    Set mCon = New ADODB.Connection

    mCon.CursorLocation = adUseClient
    mCon.Open Cn
End Sub

'データベースへの接続を切断します。
Public Sub disconnect()
    mCon.Close
    Set mCon = Nothing
End Sub

'引数のSQL文を実行し、ADODB.Recordsetを返します。
Public Function execute(sql As String) As ADODB.Recordset
    Dim rs As New ADODB.Recordset

    'タイムアウト設定 (30分)
    mCon.CommandTimeout = 60 * 30

    '処理された行数を示すメッセージが結果セットの一部として返されないようにする
    mCon.execute ("SET NOCOUNT ON")

    '警告メッセージが結果セットの一部として返されないようにする
    mCon.execute ("SET ANSI_WARNINGS OFF")

    'オーバーフローおよび0除算時にはNULLを返す
    mCon.execute ("SET ARITHABORT OFF")


    rs.Open sql, mCon, adOpenStatic, adLockBatchOptimistic

    Do
        'レコードの操作ができるオブジェクト若しくは次のRecordSetがとれず、コネクションが空になった場合終了
        If rs.State = adStateOpen Or rs.ActiveConnection Is Nothing Then
            Exit Do
        End If
        Set rs = rs.NextRecordset()
    Loop

    Set execute = rs

    '設定OFF
    mCon.execute ("SET NOCOUNT OFF")
    mCon.execute ("SET ANSI_WARNINGS ON")
    mCon.execute ("SET ARITHABORT ON")
End Function

'トランザクションを開始する
Public Sub BeginTransaction()
    mCon.BeginTrans
End Sub

'トランザクションをコミットする
Public Sub CommitTransaction()
    mCon.CommitTrans
End Sub

'トランザクションをロールバックする
Public Sub RollbackTransaction()
    mCon.RollbackTrans
End Sub

 

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

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

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

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

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

Option Explicit

'インスタンスを生成する
Public Function creater() As test_project.DataBaseAccess
    Set creater = New test_project.DataBaseAccess
End Function

 

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

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

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

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

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

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

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

Sub Test()

    Dim db As test_project.DataBaseAccess
    Dim rs As ADODB.Recordset
    Dim strSQL As String

        Set db = test_project.DatabaseFactory.creater
        db.connect
        
        'SELECTを実行してレコードセットに格納します。
        strSQL = "SELECT * FROM テーブル名 WHERE 条件"
        Set rs = db.execute(strSQL)
        
        'レコードセットがEOFかを判定します。
        If rs.EOF Then
            'レコードが存在しない場合の処理
            rs.Close
            db.disconnect
            Set rs = Nothing
            Set db = Nothing
            Exit Sub
        End If
        
        'レコードセットが最後の行になるまで繰り返します。
        Do Until rs.EOF
            Debug.Print rs("カラム1").Value & " " & rs("カラム2").Value & " " & rs("カラム3").Value
            rs.MoveNext
        Loop

        'オブジェクト変数を終了、破棄します。
        rs.Close
        db.disconnect
        Set rs = Nothing
        Set db = Nothing
End Sub

 

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

Sub Test1()

    Dim db As test_project.DataBaseAccess
    Dim strSQL As String

        Set db = test_project.DatabaseFactory.creater
        db.connect
        
        'トランザクションを掛けてUPDATEを実行します。
        strSQL = "UPDATE テーブル名 SET 更新するカラム = 更新する値 WHERE 条件"
    
    'エラーを取得します。
    On Error GoTo ErrorProcess
    
        'トランザクションを開始します。
        db.BeginTransaction
        
        'SQLを実行します。
        db.execute strSQL
        
        '実行したUPDATE文の結果をコミットします。
        db.CommitTransaction
        db.disconnect
        Set db = Nothing
    
    Exit Sub

'エラー処理
ErrorProcess:
    'トランザクションをロールバックします。
    db.RollbackTransaction
    db.disconnect
    Set db = Nothing
End Sub

 

最後に

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

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

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

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

【Excel・Access VBA】ADOでストアドを実行するサンプルプログラム

今回はVBAからADO経由でデータベースサーバーのストアドプロシージャを実行する方法を紹介します。 尚、以前にVB...

www.depthbomb.net

2020.06.20