CSVのデータベース(Excelデータベースその15)

VBA

ExcelVBA-Excelファイルをデータベースとして使う-その15です。 前回の記事はこちらになります。
hirocom777.hatenadiary.org

一応完成！！

　お話ししました通り、前回で『Excelファイルをデータベースとして使う』の記事は完了です。簡単な動作サンプルも作ることが出来たので、これを参考に色々挑戦してみてくださいね！！で、今回はというと『おまけ』です。
f:id:HiroCom777:20200523165553j:plain

CSVファイルのデータベース

この連載で使用してきたMicrosoft ActiveX Data Objects 2.8 LibraryなんですがExcelファイル、Accessファイル以外にも対応しているファイル形式があります。その中でCSVファイルを使用した例をご紹介したいと思います。CSV（comma-separatedvalues）は、カンマ区切りでデータを記述するテキストファイル。シンプルな分、取り扱いはExcelファイルよりも簡単になっています。CSV形式のファイルもデータベースとして使用できると便利ですよね！！ファイルも公開しますので、以下からダウンロードしてみてください。

15_CSVのデータベース110.zip - Google ドライブ

使い方

1.準備
　
15_CSVのデータベース110.zip - Google ドライブを解凍して、全てのファイルを同じフォルダに置いてください。

2.起動方法
　ファイル"15_CSVのデータベース.xlsm"をマクロを有効にした状態で開いてください。

3.使用方法
　Topシート下半分には、データ.csvの内容を表示します。"データ.csv"は、"15_CSVのデータベース.xlsm"配置場所下の名簿フォルダ内に配置します。
　・データ追加
　ID欄未記入で決定ボタンを押すと、記入内容を名簿.csvにデータを追記できます。
　名前は入力必須です。IDはオリジナルの番号が付与されます。
　・検索
　ID欄未記入で検索ボタンを押すと、記入内容をキーに名簿.csv内を検索できます。
　名前、備考については一部分だけの入力でも検索します。また、登録してあるID番号を入力すると、該当するレコードが表示されます。

　尚、CSVファイルではレコードの編集、削除はできません。

CSVファイルをデータベースとして使うときの注意

　CSVファイルをデータベースとして使用する場合は、ExcelファイルやAccessファイルと少し違うアプローチとなります。Accessファイルではテーブルという単位でデータベースを分けています。Excelファイルでは、シートやセルの範囲などをテーブルとして使用していますよね。でも、CSVファイル自体はただデータの連なったテキストファイルなのでテーブルとして使用できる情報がありません。そこでCSVファイルの場合はCSVファイルのファイル名自体をテーブルとしています。データベース名はCSVファイルのフルパスからCSVファイル名を削除した情報となります。フォルダの下にある（複数の）CSVファイルがテーブルとして機能するわけです。詳細は使用例を動かして確認してみたください。

クラスモジュール

　実際のクラスモジュールは、以下のようになりました。

Option Explicit
'CSVファイルのデータベースコントロールのクラスです

Private dbFileName_ As String 'ファイル名
Private dbDataBaseName_ As String 'データベース名
Private dbTableName_ As String 'テーブル名
Private dbFieldList_() As Variant 'フィールドリスト

Private adoCn As Object
Private adoRs As Object
Private adoSelectRs As Object

'初期化
Private Sub Class_Initialize()
  Set adoCn = CreateObject("ADODB.Connection")
  adoCn.Provider = "Microsoft.ACE.OLEDB.12.0"
  adoCn.Properties("Extended Properties") = "Text;HDR=Yes;FMT=Delimited"
  Set adoRs = CreateObject("ADODB.Recordset")
  adoRs.CursorLocation = adUseClient
  Set adoSelectRs = CreateObject("ADODB.Recordset")
  adoSelectRs.CursorLocation = adUseClient
End Sub
 
'終了処理
Private Sub Class_Terminate()
  If Err.Number <> 0 Then
    MsgBox "データベース接続に失敗しました-" & Err.Number
  End If
  On Error Resume Next 'クローズ時はエラー発生後も継続
  If adoRs.State = adStateOpen Then adoRs.Close
  If adoSelectRs.State = adStateOpen Then adoSelectRs.Close
  If adoCn.State = adStateOpen Then 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
    dbTableName_ = Dir$(fileName)
    dbDataBaseName_ = Left$(fileName, Len(fileName) - Len(dbTableName_))
    adoCn.Open dbDataBaseName_
    adoRs.Open "SELECT * FROM [" & dbTableName_ & "]", adoCn, adOpenKeyset, adLockOptimistic
    'フィールドリストを設定します
    Dim adoField As ADODB.Field
    Dim i As Long: i = 0
    ReDim dbFieldList_(adoRs.Fields.Count - 1)
    For Each adoField In adoRs.Fields
      dbFieldList_(i) = adoField.Name
      i = i + 1
    Next
  End Select
End Property

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

'テーブル名を取得します
Public Property Get dbDataBaseName() As String
  dbDataBaseName = dbDataBaseName_
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

'SQLのWHERE句指定でレコードを抽出して指定のセルに表示します
Public Function SelectRecord(cmdSql As String, Optional foundRecord As Range = Nothing) As Long
  adoSelectRs.Open "SELECT * FROM [" & dbTableName_ & "]  WHERE " & cmdSql, adoCn
  If Not foundRecord Is Nothing Then foundRecord.CopyFromRecordset adoSelectRs
  SelectRecord = adoSelectRs.recordCount
  adoSelectRs.Close
End Function

'SQLのWHERE句指定でレコードを抽出して指定フィールドのデータを取得します
Public Function GetFieldData(cmdSql As String, fieldName As String) As Variant
  adoSelectRs.Open "SELECT * FROM [" & dbTableName_ & "]  WHERE " & cmdSql, adoCn
  If adoSelectRs.recordCount <> 1 Then
    MsgBox "レコードが一つに絞られていません"
  Else
    GetFieldData = adoSelectRs.Fields(fieldName).Value
  End If
  adoSelectRs.Close
End Function

CSVファイルのフルパス（例:"C:\Data\データ.csv"）をデータベースファイル（dbFileName）として指定すると、データベース名（dbDataBaseName_）にはCSVファイルが置いてあるディレクトリ（"C:\Data\"）が、テーブル名（dbTableName ）にはファイル名（"データ.csv"）が指定されます。フィールド名リスト（dbFieldList）が自動的に取得されるのはExcelデータベースのクラスと変わりありません。メソッドについても、データ編集のメソッドを削除した以外は変わっていません。なんだかんだ言って簡単に作れちゃいました。クラスにすると、こんな利点があるんですね！！

これで『Excelファイルをデータベースとして使う』の連載は終了です。最後までお付き合いいただいてありがとうございました。
また、別の連載を計画してますのでお楽しみに！！

ExcelVBA-Excelファイルをデータベースとして使う連載はコチラからどうぞ！！