データベースの処理にはクラスで対応しよう！｜エクセルVBA

f:id:yt4u:20180210095024j:plain

エクセルのシートをデータベースのテーブルとして扱えるってめちゃくちゃ便利です。

yt4u.hatenablog.com

上記エントリーの最後にも触れているとおり、データベースに関係するコードをクラスで管理する方法を考えたいと思います。

クラスの構成要素を目次で示します。

クラス化すればこんなにシンプルにデータが取れるようになる！
データベースへの接続(コネクションの確立)
SQLの実行
- レコードセットの取得(SELECT句)
- レコードの追加・更新(INSERT, UPDATE句)
オブジェクトの破棄
一度クラスを作れば使い回しが効く

Excel 最強の教科書[完全版]――すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術

作者: 藤井直弥,大山啓介
出版社/メーカー: SBクリエイティブ
発売日: 2017/01/28
メディア: 単行本
この商品を含むブログ (2件) を見る

内容は至ってシンプル。
前出エントリーに記載したコードのコピペでほとんどが完成します。

クラス化すればこんなにシンプルにデータが取れるようになる！

まずは、完成したクラスを使って、どんなコードを書けばデータを取ってこれるようになるのかを見たいと思います。

以下は、上記エントリーのデータベース関連処理をクラスに置き換えたものになっています。
前提条件等は上記エントリーをご参照ください。

また、クラスの名前は「blClsDataBase」としいています。

Public Sub getDataSample()

  Dim clsDB             As blClsDataBase
  Dim strSQL            As String
  Dim objRS             As Object
  
  
  Set clsDB = New blClsDataBase
  
  
  'クラスを生成(当エクセルに接続)
  
    With clsDB
    
      .DataBaseType = XLS
      .DataSource = ThisWorkbook.FullName
    
      .ConnectDB
    
    End With
  
  
  '抽出条件を作成
    
    strSQL = "SELECT"                                           '抽出フィールド(項目)を指定
      strSQL = strSQL & "  [名前]"
      strSQL = strSQL & ", [ふりがな]"
      strSQL = strSQL & ", [電話番号]"
      strSQL = strSQL & ", MONTH([誕生日]) AS [誕生月]"
    strSQL = strSQL & " FROM rngXDB_DataBase"                   'データテーブルを指定
    strSQL = strSQL & " WHERE 1 = 1"                            '抽出条件
      strSQL = strSQL & " AND [性別] = '男'"                    '性別=男
      strSQL = strSQL & " AND [年齢] >= 30"                     '年齢=30歳以上
      strSQL = strSQL & " AND [年齢] <  50"                     '50歳未満
      strSQL = strSQL & " AND [婚姻] = '未婚'"                  '婚姻=未婚
  

  '抽出実行
    
    If clsDB.GetRecordset(strSQL, objRS) = False Then
    
      Err.Description = clsDB.ErrNum
      
      GoTo ERR_PROC
    
    End If
    
  
  '抽出結果を出力
  
    With wsXLSDataBase
    
      With .Range("rngXDB_DataTop")
      
        
        '出力エリアにある既存データを消去
          
          .CurrentRegion.ClearContents
          
        
        'フィールド(項目)名を出力
          
          For lngF = 0 To objRS.Fields.Count - 1
          
            .Offset(, lngF).Value = objRS.Fields(lngF).Name
          
          Next lngF
          
        
        'データを出力
        
          .Offset(1).CopyFromRecordset objRS
          
    
      End With
    
    End With
  
  
  GoTo END_PROC
  
ERR_PROC:

  MsgBox clsDB.ErrDes
  
  
END_PROC:

  'DBクラスを閉じる
  
    Set clsDB = Nothing


End Sub

すごいシンプルですよね。
クラス化してしまえば、コードが非常に分かり易くなります。

データは取ってきてから色々と加工したりと複雑な処理が始まるワケで、だから尚のことデータを取ってくる部分はシンプルにしておきたいもの。

クラスを使うことで、それが実現できるわけです。
ただ、そのクラスを作るところでちょっとだけ手を動かす必要があります。

それを順に見ていきたいと思います。

データベースへの接続(コネクションの確立)

普段使うことになるデータベースはエクセルだけとは限りません。
アクセスを使うこともあるし、SQLServerを使うこともあります。

使用するデータベースが何であれ、ひとつのクラスで対応できちゃった方が断然ラク。
なので簡単に接続先を指定できるようにしたいと思います。

接続先を指定するためのプロパティを用意

クラスのプロパティを使います。
プロパティにどんな値を入れればいいのか選択肢が表示された方がコードを書きやすいと思います。
そこで列挙子を併用します。

Public Enum DBTYPE
  NONE
  XLS
  ACCESS
End Enum

Public DataBaseType               As DBTYPE

こうすることで、いざプロパティの値を入れるときに

f:id:yt4u:20180210101416j:plain

こういったカンジでオプションが出てきます。
選択肢の中から値を選ぶだけですから、コードを書くのがラクになりますよね。

この段階では、どこに接続するかを決めただけですから、実際に接続は行われていません。
接続を実行するためのメソッドを用意します。

エラーへの対応

ただその前に、データを取って来るときなどに何かしらエラーが発生することがあります。
例えば、SQL文が間違っていた、というそもそものエラーもありますよね。
それらエラーの内容を把握できるようにプロパティも用意しちゃいましょう。

Private mErrNum                   As Long
Private mErrDes                   As String


Public Property Get ErrNum() As Long

  ErrNum = mErrNum

End Property


Public Property Get ErrDes() As String

  ErrDes = mErrDes

End Property

クラス内でエラーが発生した場合は、そのエラー内容を「ErrNum」「ErrDes」で確認できるようになります。これらの具体的な使い方は、後のコード全文で確認してください。

接続の実行

準備が整ったところでいざ接続です。
データベースにエクセル、アクセスを使う際は、それらのバージョンによって接続文字列が異なるので注意。

Public Enum DBTYPE
  NONE
  XLS
  ACCESS
End Enum

Public DataBaseType               As DBTYPE

Public DataSource                 As String
Public ID                         As String
Public Password                   As String

Private mobjConnection            As Object

Private mErrNum                   As Long
Private mErrDes                   As String

Public Enum DBPROPERTY
  adStateClose = 0
  adOpenStatic = 3
End Enum


Public Function ConnectDB() As Boolean

  Dim strCNString       As String
  Dim strExtension      As String
  

  ConnectDB = False
  
  
  'データベースの種類に応じてConnectStringを用意
    
    Select Case Me.DataBaseType
    
      
      Case DBTYPE.NONE
        '指定忘れ
                
        Err.Description = "データベースタイプ選択エラー"
        
        GoTo ERR_PROC
        
        
      
      Case DBTYPE.XLS
        'エクセルの場合
        
        
        'データソース無指定はエラー
          
          If Me.DataSource = "" Then
          
            Err.Description = "DataSource指定エラー"
            
            GoTo ERR_PROC
          
          End If
      
      
        '拡張子に応じて接続文字列を分岐
          
          strExtension = LCase(Right(Me.DataSource, Len(Me.DataSource) - InStrRev(Me.DataSource, ".")))
              
                
          Select Case True
          
            Case LCase(strExtension) = "xls"
              '2003以前
            
              strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
            
            
            Case LCase(strExtension) Like "xls?"
              '2007以降
            
              strCNString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
            
          
          End Select
        
        
          strCNString = strCNString & "Data Source=" & Me.DataSource & ";"
             
      
      
      Case DBTYPE.ACCESS
        'アクセスの場合
        
        
        'データソース無指定はエラー
          
          If Me.DataSource = "" Then
          
            Err.Description = "DataSource指定エラー"
            
            GoTo ERR_PROC
          
          End If
        
        
        '拡張子に応じて接続文字列を分岐
          
          strExtension = LCase(Right(Me.DataSource, Len(Me.DataSource) - InStrRev(Me.DataSource, ".")))
              
                
          Select Case True
          
            Case LCase(strExtension) = "mdb"
              '2003以前
            
              strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;"
            
            
            Case LCase(strExtension) = "accdb"
              '2007以降
              
              strCNString = "Provider=Microsoft.ACE.OLEDB.12.0;"
            
          
          End Select
        
        
          strCNString = strCNString & "Data Source=" & Me.DataSource & ";"
          
          
          If Me.Password <> "" Then
          
            strCNString = strCNString & "JET OLEDB:DataBase Password=" & Me.Password & ";"
          
          End If
        
    
    End Select
    
    
  'コネクション確立
  
    Set mobjConnection = CreateObject("ADODB.Connection")
    
    mobjConnection.Open strCNString
    

    ConnectDB = True
    

  GoTo END_PROC
  
ERR_PROC:

  mErrNum = Err.Number
  mErrDes = Err.Description


END_PROC:


End Function

これで使いたいデータベースに接続することで出来るようになりました。

データベースに接続したら、あとは欲しいデータを取ってきたり、あるいはデータベースを更新したりすることになります。

それらもクラスの中で完結できるようにします。

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)

作者: 青木峰郎
出版社/メーカー: SBクリエイティブ
発売日: 2015/06/30
メディア: 単行本
この商品を含むブログ (6件) を見る

SQLの実行

ここまで来れば、あとはSQLを実行するのみです。
SQLの実行結果、つまり滞りなく実行できたかどうかTrue/Falseを返してくれる関数を作ります。

レコードセットの取得(SELECT句)

Public Function GetRecordset( _
                        strSQL As String, _
                        objRecordset As Object _
                        )


  GetRecordset = False
  
  
  On Error GoTo ERR_PROC
  
  
  Set objRecordset = CreateObject("ADODB.Recordset")
  
  
  objRecordset.Open strSQL, mobjConnection, adOpenStatic
  
  
  GetRecordset = True
  
  
  GoTo END_PROC
  
ERR_PROC:

  mErrNum = Err.Number
  mErrDes = Err.Description
  

END_PROC:


End Function

SELECT句の場合は、引数にレコードセット・オブジェクトを持ちます。
SQL文を実行した結果、得られるレコードセットをそのオブジェクトに格納します。

SQL文の実行が問題なく行われれば、関数としてはTrueを返します。
一方、エラーが発生した場合はFalseを返しつつ、エラー内容を「ErrNum」「ErrDes」に格納します。
これにより、Falseだった時にそのエラー内容を確認することが可能になります。

レコードの追加・更新(INSERT, UPDATE句)

Public Function ExecuteDB( _
                        strSQL As String _
                        )


  ExecuteDB = False
  
  
  On Error GoTo ERR_PROC
  
  
  mobjConnection.Execute strSQL
  
  
  ExecuteDB = True
  
  
  GoTo END_PROC
  
ERR_PROC:

  mErrNum = Err.Number
  mErrDes = Err.Description
  

END_PROC:


End Function

引数にレコードセット・オブジェクトを持たないこと以外は、SELECT句の場合と同じです。

オブジェクトの破棄

レコードセットやコネクションといったオブジェクトの破棄もクラスで行えるようにします。

レコードセットの破棄

Public Sub CloseRS( _
                        objRS As Object _
                        )


  If Not objRS Is Nothing Then
  
    If objRS.State <> adStateClosed Then
    
      objRS.Close
    
    End If
  
  End If
  

End Sub

コネクションの破棄

Public Sub CloseCN()


  If Not mobjConnection Is Nothing Then
  
    If mobjConnection.State <> adStateClose Then
    
      mobjConnection.Close
    
    End If
  
  End If


End Sub

クラスの破棄と同時にコネクションも破棄されるようにする

上記のメソッドを使って個々のオブジェクトを破棄してもOKですが、クラス破棄と同時にコネクションを破棄するようにすれば手間が減らせます。

Private Sub Class_Terminate()

  Call CloseCN

End Sub

一度クラスを作れば使い回しが効く

これでデータベース処理用のクラスが出来上がりました。
作り上げるまでにかかる手間は、その後ラクになることで回収できます！

コード全文を掲載しますので、最初はコピペで使ってみてから、使いやすいようにアレンジしてみてください。

「blClsDataBase」

Option Explicit

Public Enum DBTYPE
  NONE
  XLS
  ACCESS
End Enum

Public DataBaseType               As DBTYPE

Public DataSource                 As String
Public SQLServerName              As String
Public SQLDataBaseName            As String
Public ID                         As String
Public Password                   As String

Private mobjConnection            As Object

Private mErrNum                   As Long
Private mErrDes                   As String

Public Enum DBPROPERTY
  adStateClose = 0
  adOpenStatic = 3
End Enum



Private Sub Class_Terminate()


  Call CloseCN
  

End Sub


'==========================================================
'エラー処理
'==========================================================
Public Property Get ErrNum() As Long

  ErrNum = mErrNum

End Property


Public Property Get ErrDes() As String

  ErrDes = mErrDes

End Property



'==========================================================
'DB接続
'==========================================================
Public Function ConnectDB() As Boolean

  Dim strCNString       As String
  Dim strExtension      As String
  

  ConnectDB = False
  
  
  'データベースの種類に応じてConnectStringを用意
    
    Select Case Me.DataBaseType
    
      
      Case DBTYPE.NONE
        '指定忘れ
                
        Err.Description = "データベースタイプ選択エラー"
        
        GoTo ERR_PROC
        
        
      
      Case DBTYPE.XLS
        'エクセルの場合
        
        
        'データソース無指定はエラー
          
          If Me.DataSource = "" Then
          
            Err.Description = "DataSource指定エラー"
            
            GoTo ERR_PROC
          
          End If
      
      
        '拡張子に応じて接続文字列を分岐
          
          strExtension = LCase(Right(Me.DataSource, Len(Me.DataSource) - InStrRev(Me.DataSource, ".")))
              
                
          Select Case True
          
            Case LCase(strExtension) = "xls"
              '2003以前
            
              strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
            
            
            Case LCase(strExtension) Like "xls?"
              '2007以降
            
              strCNString = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"
            
          
          End Select
        
        
          strCNString = strCNString & "Data Source=" & Me.DataSource & ";"
             
      
      
      Case DBTYPE.ACCESS
        'アクセスの場合
        
        
        'データソース無指定はエラー
          
          If Me.DataSource = "" Then
          
            Err.Description = "DataSource指定エラー"
            
            GoTo ERR_PROC
          
          End If
        
        
        '拡張子に応じて接続文字列を分岐
          
          strExtension = LCase(Right(Me.DataSource, Len(Me.DataSource) - InStrRev(Me.DataSource, ".")))
              
                
          Select Case True
          
            Case LCase(strExtension) = "mdb"
              '2003以前
            
              strCNString = "Provider=Microsoft.Jet.OLEDB.4.0;"
            
            
            Case LCase(strExtension) = "accdb"
              '2007以降
              
              strCNString = "Provider=Microsoft.ACE.OLEDB.12.0;"
            
          
          End Select
        
        
          strCNString = strCNString & "Data Source=" & Me.DataSource & ";"
          
          
          If Me.Password <> "" Then
          
            strCNString = strCNString & "JET OLEDB:DataBase Password=" & Me.Password & ";"
          
          End If
        
      
    End Select
    
    
  'コネクション確立
  
    Set mobjConnection = CreateObject("ADODB.Connection")
    
    mobjConnection.Open strCNString
    

    ConnectDB = True
    

  GoTo END_PROC
  
ERR_PROC:

  mErrNum = Err.Number
  mErrDes = Err.Description
  

END_PROC:



End Function


'==========================================================
'SQL実行 - SELECT
'==========================================================
Public Function GetRecordset( _
                        strSQL As String, _
                        objRecordset As Object _
                        )


  GetRecordset = False
  
  
  On Error GoTo ERR_PROC
  
  
  Set objRecordset = CreateObject("ADODB.Recordset")
  
  
  objRecordset.Open strSQL, mobjConnection, adOpenStatic
  
  
  GetRecordset = True
  
  
  GoTo END_PROC
  
ERR_PROC:

  mErrNum = Err.Number
  mErrDes = Err.Description
  

END_PROC:


End Function



'==========================================================
'SQL実行 - SELECT
'==========================================================
Public Function ExecuteDB( _
                        strSQL As String _
                        )


  ExecuteDB = False
  
  
  On Error GoTo ERR_PROC
  
  
  mobjConnection.Execute strSQL
  
  
  ExecuteDB = True
  
  
  GoTo END_PROC
  
ERR_PROC:

  mErrNum = Err.Number
  mErrDes = Err.Description
  

END_PROC:


End Function


'==========================================================
'Recordsetを閉じる
'==========================================================
Public Sub CloseRS( _
                        adoRS As ADODB.Recordset _
                        )


  If Not adoRS Is Nothing Then
  
    If adoRS.State <> adStateClosed Then
    
      adoRS.Close
    
    End If
  
  End If
  

End Sub



'==========================================================
'Connectionを閉じる
'==========================================================
Public Sub CloseCN()


  If Not mobjConnection Is Nothing Then
  
    If mobjConnection.State <> adStateClose Then
    
      mobjConnection.Close
    
    End If
  
  End If


End Sub

ぼくLog

子持ち、車持ち、マンション持ちの僕の日常を綴ります。車と、ラクをしたい一心で覚えたエクセルVBAを中心になりそうです。