Accessファイル確認ツール②(ExcelでRDBその14)

ExcelでRDB（リレーショナルデータベース）を操作してみようという連載のその14です。前回の記事はこちらです。
hirocom777.hatenadiary.org

カラム情報の表示

　前回はからAccessファイル確認ツールに取り組んでいます。Accessファイルを指定して開くとテーブルのリストが確認できます。さらに、選択したテーブルの内容をExcelファイルのシートに展開できます。今回は選択されたテーブルのカラム情報表示について機能追加してみようと思います。仕様は以下になります。

・選択テーブルでテーブルを選択すると、選択されたテーブルのカラムリストが表示される。
・併せて、各カラムのキー設定、データ型設定が表示される。

この内容で、以下のツールを用意してみました。
14_Accessファイル確認ツール②.zip - Google ドライブ
f:id:HiroCom777:20210104231100j:plain

機能の説明

　操作方法については基本的に前回と一緒です。Accessファイルを指定すると指定ファイルのテーブルリストが表示されます。選択テーブルのドロップダウンリストでテーブルを選択すると該当テーブルを構成するカラムリストが表示されます。カラム名以外にも以下の情報が表示されます。
・キー
　カラムにキーが設定されている場合は、キーの情報が表示されます。1が主キー、2が外部キー、3がキーとなります。
docs.microsoft.com

・データ型
　カラムに設定されているデータ型を表示されます。数値で表示されるのですが、詳細は以下を参考にしてください。
docs.microsoft.com
代表してよく出てくる値は、3 　整数型（adInteger）、7　日付型（adDate）、202　文字列型（adVarWChar）、203　長文字列型（adLongVarWChar）くらいだと思います。

コードの解説

　コードの解説です。今回は一つのツールということで結構長くなってしまいましたので要点だけ説明します。何れも今までの連載で取り上げた手法を使用しています。全体の動作は、ツールを動かしてみて確認してみてください。

・テーブルリストの表示
　テーブルリストの表示です。

Private Sub DispTableList(fileName As String)
Dim connectString As String
Dim catalogObject As Object
Dim tableObject As Object
Dim listRange As Range
Dim i As Long
  'データベースを接続します
  Set catalogObject = CreateObject("ADOX.Catalog")
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  catalogObject.ActiveConnection = connectString
  'テーブルリストを表示します
  Set listRange = Me.Range("TableList").Offset(1, 0)
  For Each tableObject In catalogObject.Tables
    If tableObject.Type = "TABLE" Then
      listRange.Offset(i, 0) = tableObject.Name
      listRange.Offset(i, -1) = i + 1
      i = i + 1
    End If
  Next
  Me.Range("TableName").Value = Me.Range("B10").Value
End Sub

　ADOXで作成したcatalogObject にAccessファイルを指定して開いたのちに、テーブルの一覧を取得して表示しています。この時表示されるのは、tableObject.Type = "TABLE"のものだけ。つまりデータベース本体として使用しているテーブルのみです。また、一緒に左側に通し番号を表示しています。最後に表示したテーブルリストの先頭を選択テーブルに表示しています。

・カラムリストの表示
　カラムリストの表示です。

Private Sub DispColumnList(fileName As String, tableName As String)
Dim connectString As String
Dim catalogObject As Object
Dim tableObject As Object
Dim columnObject As Object
Dim keyObject As Object
Dim listRange As Range
Dim i As Long

  '表示がある場合にはクリアします
  If Me.Range("I10").Value <> "" Then
    Range(Me.Range("F10"), Me.Range("I10").SpecialCells(xlLastCell)).ClearContents
    Me.Range("A10").Activate
  End If
  'データベースを接続します
  Set catalogObject = CreateObject("ADOX.Catalog")
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  catalogObject.ActiveConnection = connectString
  'カラムリストを表示します
  Set listRange = Me.Range("columnList").Offset(1, 0)
  For Each columnObject In catalogObject.Tables(tableName).Columns
    listRange.Offset(i, 0) = columnObject.Name
    listRange.Offset(i, -1) = columnObject.Type
    listRange.Offset(i, -3) = i + 1
    i = i + 1
  Next
  'キーを表示します
  Set listRange = Range(listRange, listRange.Offset(i, 0))
  For Each keyObject In catalogObject.Tables(tableName).Keys
    For Each columnObject In keyObject.Columns
      listRange.Find(columnObject.Name, , , xlWhole).Offset(0, -2).Value = keyObject.Type
    Next
  Next
End Sub

　カラムリストの表示はテーブルリストと違って、ファイルを選択した後に表示を切り替えられることがあります。そこで、先頭で既に表示がある場合に表示をクリアする処理をしています。その後、ADOXで作成したcatalogObject にAccessファイルを指定して開き、指定されたテーブルを選択してカラムリストを取り出しています。また、一緒に通し番号とデータ型を表示しています。
　キー情報については、カラムリストを表示し終わった後に選択したテーブルのkeysコレクションからカラム名をキーにして読込、設定しています。
　また、先に述べた通りこの処理は選択テーブルの表示が切り替わった際に実行されるようになっています。以下の処理で呼び出されます。

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Me.Range("TableName").Address And Target.Text <> "" Then
    DispColumnList Me.Range("FileName").Text, Me.Range("TableName").Text
  End If
End Sub