VBA

VBA – DB操作クラスの作り方

 

Excel VBAを使ったデータベース操作をシンプルかつ効果的に行うためのクラス、「VBA – DB操作クラス」について解説します。

このクラスはExcelファイルをデータベースとして扱い、データの追加やテーブルの操作を簡単に行うことができます。分かりやすくコードを解説し、利点や改善点、そして実際の活用サンプルについても紹介していきます。

実装

クラスモジュールを挿入して下記を記載して下さい。

Option Explicit

'Excelファイルのデータベースコントロールのクラスです。
Private dbFileName_ As String 'データベースファイル名
Private dbTableName_ As String 'テーブル名
Private dbFieldList_() As Variant 'フィールドリスト
Private adoCn As Object
Private adoRs As Object

'初期化
Private Sub Class_Initialize()
  Set adoCn = CreateObject("ADODB.Connection")
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Properties("Extended Properties") = "Excel 12.0"
  Set adoRs = CreateObject("ADODB.Recordset")
  adoRs.CursorLocation = adUseClient
End Sub
 
'終了処理
Private Sub Class_Terminate()
  If Err.Number <> 0 Then
    adoCn.RollbackTrans
    MsgBox "データベース接続に失敗しました"
  Else
    adoCn.CommitTrans
  End If
  adoRs.Close
  adoCn.Close
End Sub

'ファイル名を設定します
Public Property Let dbFileName(ByVal fileName As String)
  Select Case True
  Case dbFileName_ <> ""
    MsgBox "ファイル名は途中で変更できません"
  Case Dir$(fileName) = ""
    MsgBox "ファイルが見つかりません"
  Case Else
    dbFileName_ = fileName
  End Select
End Property
'ファイル名を取得します
Public Property Get dbFileName() As String
  dbFileName = dbFileName_
End Property

'テーブル名を設定します
Public Property Let dbTableName(ByVal tableName As String)
  Select Case True
  Case Dir$(dbFileName_) = ""
    MsgBox "データベースファイルを指定してください"
  Case dbTableName_ <> ""
    MsgBox "テーブル名は途中で変更できません"
  Case Else
    adoCn.Open dbFileName_
    adoRs.Open "[" & tableName & "$]", adoCn, adOpenKeyset, adLockOptimistic
    dbTableName_ = tableName
    adoCn.BeginTrans
    'フィールドリストを設定します
    Dim adoField As ADODB.Field
    Dim i As Long
    ReDim dbFieldList_(adoRs.Fields.Count - 1)
    For i = 0 To UBound(dbFieldList_)
      dbFieldList_(i) = adoRs.Fields.Item(i).Name
    Next
  End Select
End Property

'テーブル名を取得します
Public Property Get dbTableName() As String
  dbTableName = dbTableName_
End Property
'フィールドリストを取得します
Public Property Get dbFieldList() As Variant()
  dbFieldList = dbFieldList_
End Property

'Excelデータベースにレコードを追加します
Public Sub AddRecord(recordData() As Variant)
  adoRs.AddNew dbFieldList_, recordData
  adoRs.Update
End Sub

解説

クラスの初期化と終了処理

Private Sub Class_Initialize()
  ' 初期化処理
  Set adoCn = CreateObject("ADODB.Connection")
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Properties("Extended Properties") = "Excel 12.0"
  Set adoRs = CreateObject("ADODB.Recordset")
  adoRs.CursorLocation = adUseClient
End Sub

Private Sub Class_Terminate()
  ' 終了処理
  If Err.Number <> 0 Then
    adoCn.RollbackTrans
    MsgBox "データベース接続に失敗しました"
  Else
    adoCn.CommitTrans
  End If
  adoRs.Close
  adoCn.Close
End Sub

初期化処理

クラスが作成される際に実行され、ADODB.ConnectionとADODB.Recordsetを初期化します。これにより、クラスを使う準備が整います。

終了処理

クラスが破棄される際に実行され、データベースのトランザクションを確定させ、接続を閉じます。エラーがあればロールバックし、エラーメッセージを表示します。

プロパティとデータの設定

Public Property Let dbFileName(ByVal fileName As String)
  ' ファイル名を設定
  Select Case True
  Case dbFileName_ <> ""
    MsgBox "ファイル名は途中で変更できません"
  Case Dir$(fileName) = ""
    MsgBox "ファイルが見つかりません"
  Case Else
    dbFileName_ = fileName
  End Select
End Property

Public Property Get dbFileName() As String
  ' ファイル名を取得
  dbFileName = dbFileName_
End Property

ファイル名の設定と取得

データベースとして使うExcelファイルのファイル名を設定または取得するプロパティです。設定時には存在しないファイルや既に設定済みの場合にメッセージを表示しています。

Public Property Let dbTableName(ByVal tableName As String)
  ' テーブル名を設定
  Select Case True
  Case Dir$(dbFileName_) = ""
    MsgBox "データベースファイルを指定してください"
  Case dbTableName_ <> ""
    MsgBox "テーブル名は途中で変更できません"
  Case Else
    ' テーブルを開き、フィールドリストを設定
    adoCn.Open dbFileName_
    adoRs.Open "[" & tableName & "$]", adoCn, adOpenKeyset, adLockOptimistic
    dbTableName_ = tableName
    adoCn.BeginTrans
    Dim adoField As ADODB.Field
    Dim i As Long
    ReDim dbFieldList_(adoRs.Fields.Count - 1)
    For i = 0 To UBound(dbFieldList_)
      dbFieldList_(i) = adoRs.Fields.Item(i).Name
    Next
  End Select
End Property

Public Property Get dbTableName() As String
  ' テーブル名を取得
  dbTableName = dbTableName_
End Property

テーブル名の設定と取得

テーブル名を設定または取得するプロパティです。設定時にはデータベースファイルが指定されていない場合や既にテーブル名が設定済みの場合にメッセージを表示しています。また、テーブルを開き、フィールドリストを設定しています。

データの操作

Public Sub AddRecord(recordData() As Variant)
  ' レコードを追加
  adoRs.AddNew dbFieldList_, recordData
  adoRs.Update
End Sub

レコードの追加

指定されたデータをフィールドリストに基づいて新しいレコードとして追加します。AddNewで新しいレコードを作成し、Updateで保存します。

利点

  1. シンプルな操作: クラスを使うことで、Excelファイルをデータベースとして簡単に扱えます。
  2. 柔軟性: ファイル名やテーブル名をプロパティとして設定でき、動的な操作が可能です。
  3. トランザクション管理: 初期化と終了処理でトランザクションを確定させ、データの整合性を保ちます。

活用サンプル

以下はこのクラスを使った簡単な活用例です。例として、新しいデータを追加してみましょう。

Sub UsageExample()
  ' クラスのインスタンスを生成
  Dim myDB As New DatabaseHandler

  ' データベースファイルを指定
  myDB.dbFileName = "C:\Path\To\Your\Database
  ' テーブル名を指定
  myDB.dbTableName = "YourTableName"

  ' データを追加
  Dim newRecord As Variant
  newRecord = Array("Value1", "Value2", "Value3") ' 例: フィールド1, フィールド2, フィールド3の値

  ' レコードを追加
  myDB.AddRecord newRecord

  ' インスタンスの解放（終了処理が実行される）
  Set myDB = Nothing
End Sub

この例では、DatabaseHandlerクラスの新しいインスタンスを生成し、ファイル名とテーブル名を指定しています。その後、新しいレコードのデータを配列として用意し、AddRecordメソッドを使ってデータベースに追加しています。最後にインスタンスを解放することで終了処理が実行されます。

改善点

  1. エラーハンドリングの強化: 現在はエラーメッセージが表示されるのみで、具体的なエラー情報が不足しています。エラーハンドリングを強化し、開発者に具体的な問題を理解しやすくすることができます。
  2. データ型の柔軟性: 現在、クラスではフィールドのデータ型を特定していません。将来的には、異なるデータ型にも対応できるような柔軟性を追加することが考えられます。
  3. データの検索・更新・削除: 現在のクラスは新しいレコードの追加に焦点を当てていますが、データの検索、更新、削除などの操作も考慮するとさらに汎用性が向上します。

例えば「データの検索・更新・削除」は次の様にクラスに実装すると良いでしょう。

' データの検索
Public Function FindRecord(fieldName As String, value As Variant) As Boolean
    Dim criteria As String
    criteria = "[" & fieldName & "] = " & FormatValue(value)
    adoRs.Find criteria
    FindRecord = Not adoRs.NoMatch
End Function

' データの更新
Public Sub UpdateRecord(fieldName As String, value As Variant, newData() As Variant)
    Dim criteria As String
    criteria = "[" & fieldName & "] = " & FormatValue(value)
    adoRs.Find criteria
    If Not adoRs.NoMatch Then
        adoRs.Fields(dbFieldList_).Value = newData
        adoRs.Update
    End If
End Sub

' データの削除
Public Sub DeleteRecord(fieldName As String, value As Variant)
    Dim criteria As String
    criteria = "[" & fieldName & "] = " & FormatValue(value)
    adoRs.Find criteria
    If Not adoRs.NoMatch Then
        adoRs.Delete
        adoRs.Update
    End If
End Sub

' 値を適切な形式でフォーマット
Private Function FormatValue(value As Variant) As String
    If IsNumeric(value) Then
        FormatValue = value
    Else
        FormatValue = "'" & value & "'"
    End If
End Function

これで、FindRecordメソッドを使って特定の条件でデータを検索し、UpdateRecordメソッドとDeleteRecordメソッドを使ってデータの更新と削除ができるようになります。

これを利用したサンプルは以下の通りです。

Sub UsageExample()
  ' クラスのインスタンスを生成
  Dim myDB As New DatabaseHandler

  ' データベースファイルを指定
  myDB.dbFileName = "C:\Path\To\Your\Database.xlsx"

  ' テーブル名を指定
  myDB.dbTableName = "YourTableName"

  ' データを追加
  Dim newRecord As Variant
  newRecord = Array("Value1", "Value2", "Value3") ' 例: フィールド1, フィールド2, フィールド3の値
  myDB.AddRecord newRecord

  ' データを検索
  If myDB.FindRecord("FieldName", "SearchValue") Then
    ' 見つかった場合の処理
    Debug.Print "Record Found!"
  Else
    ' 見つからなかった場合の処理
    Debug.Print "Record Not Found!"
  End If

  ' データを更新
  Dim newData As Variant
  newData = Array("UpdatedValue1", "UpdatedValue2", "UpdatedValue3")
  myDB.UpdateRecord "FieldName", "SearchValue", newData

  ' データを削除
  myDB.DeleteRecord "FieldName", "SearchValue"

  ' インスタンスの解放（終了処理が実行される）
  Set myDB = Nothing
End Sub

このサンプルでは、まずFindRecordで特定の条件でデータを検索し、その後UpdateRecordでデータを更新し、最後にDeleteRecordでデータを削除しています。

まとめ

VBA – DB操作クラスはExcel VBAを利用して、Excelファイルをデータベースとして利用する際に便利なクラスです。シンプルな操作でデータベースの初期化、ファイル名やテーブル名の設定、新しいレコードの追加ができます。利点としてはシンプルな操作と柔軟性が挙げられます。

これをベースに、より多くのデータベース操作をサポートする拡張やエラーハンドリングの改善を行うことで、より強力で汎用性のあるクラスに進化させることができます。

EXCEL VBA
こちらもおすすめ！！