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

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

改善点がある！！

　Accessファイル確認ツールに取り組んでいます。前回は、選択したテーブルのカラム情報表示に対応しました。データ型と、キーの設定情報を表示できます。これで結構便利に使えるんじゃないですか？でも、よくよく見るとまだ改善点がありますよね。今回はそこらへんを見直してみようと思います。今回の内容を適用したツールを以下に置いておきますので興味のある方はダウンロードしてみてください。

15_Accessファイル確認ツール③.zip - Google ドライブ

カラム情報の表示を表示を見やすくしたい

　カラム情報の表示はできたのですが、データ型も、キーの設定情報も数値の表記も数値による表記だけ。データ型名やキーの種類で表記すると親切です。これは簡単にできると思います。

カラム名表示の順番が・・・

　試しに使ってみて気が付いたのですが、カラムリストの表示順番がなんだかおかしい・・・。テーブルボタンを押してExcelシートに展開すると正しく左から表示されるのに、カラムリストの表示では違った順番で表示されます。この違いはどうやらADOで処理したか、ADOXで処理したかの違いの様です。ADOでは正しい順番（？）でカラム名を取得できるのですが、ADOXではカラム名を昇順にソートして返しているようです（何故！！）。そういわれてみれば、テーブル名も昇順にソートされているようです。
　テーブルリストは（ADOXでしか取得できないので）ともかく、カラムリストの順番は何とかしたいです。

数字からキー情報やデータ型名を返す

　まず、数字からキー情報やデータ型名を返す関数を作りましょう。まずはキー情報から・・・

'番号からキーの種類を返します
Private Function GetKeyInfo(keyNumber As Long) As String
  Select Case keyNumber
  Case 0: GetKeyInfo = ""
  Case adKeyPrimary: GetKeyInfo = "主キー"
  Case adKeyForeign: GetKeyInfo = "外部キー"
  Case adKeyUnique: GetKeyInfo = "キー"
  Case Else: GetKeyInfo = "???"
  End Select
End Function

こんな感じです。簡単ですね。adKeyPrimary,GetKeyInfo,adKeyUniqueはキーの種類を表す定数です。以下で説明しています。
hirocom777.hatenadiary.org
お次はデータ型の変換です。

'番号からデータ型の種類を返します
Private Function GetDataInfo(DataNumber As Long) As String
  Select Case DataNumber
  Case adVarWChar: GetDataInfo = "テキスト"
  Case adLongVarWChar: GetDataInfo = "メモ"
  Case adSmallInt: GetDataInfo "整数型"
  Case adInteger: GetDataInfo = "長整数型"
  Case adSingle: GetDataInfo = "単精度浮動小数点"
  Case adDouble: GetDataInfo = "倍精度浮動小数点"
  Case adDate: GetDataInfo = "日付"
  Case adDBTimeStamp: GetDataInfo = "日付/時刻"
  Case Else: GetDataInfo = "???"
  End Select
End Function

　adVarWCharなどは、データ型名を表す定数です。データ型はもっと沢山の型が存在するのですが、とりあえず上記を抑えておけばいいと思います。数字で表記しているところを、これらの関数で置き換えて表記すればいいでしょう。

カラム名の表示をADOで取得する

　カラム名表示の順番を修正する件ですが、ADOを使って取得すればいいですね。そこで、カラムリストを表示するDispColumnListプロシージャを以下の様に書き換えてみました。

'カラムリストを表示します
Private Sub DispColumnList(fileName As String, tableName As String)
Dim connectString As String
Dim catalogObject As Object
Dim columnObject As Object
Dim keyObject As Object
Dim db As clsAccessDbase
Dim columnName As Variant
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
  
  'データベースに接続します(ADO)
  Set db = New clsAccessDbase
  db.dbFileName = Me.Range("FileName").Text
  db.dbTableName = Me.Range("TableName").Text
  Set listRange = Me.Range("columnList").Offset(1, 0)
  'カラム名を表示します
  For Each columnName In db.dbFieldList
    listRange.Offset(i, 0) = columnName
    listRange.Offset(i, -3) = i + 1
    i = i + 1
  Next
  Set db = Nothing
  Set listRange = Range(listRange, listRange.Offset(i, 0))
  
  'データベースを接続します(ADOX)
  Set catalogObject = CreateObject("ADOX.Catalog")
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  catalogObject.ActiveConnection = connectString
  'データ型を表示します
  For Each columnObject In catalogObject.Tables(tableName).Columns
    listRange.Find(columnObject.Name, , , xlWhole).Offset(0, -1).Value = GetDataInfo(columnObject.Type)
  Next
  
  'キー情報を表示します
  For Each keyObject In catalogObject.Tables(tableName).Keys
    For Each columnObject In keyObject.Columns
      listRange.Find(columnObject.Name, , , xlWhole).Offset(0, -2).Value = GetKeyInfo(keyObject.Type)
    Next
  Next
End Sub

まずはADOでAccessファイルに接続できるクラスを使用して、指定したテーブルのカラム名リストを取得します。お次にADOXを使用してカラム名に対応したデータ型名とキー情報を取得します。取得した値は前項で作成した関数で表記を変換して表示しています。これでカラム名表示の順番が揃うはずです。

それでは、改定したツールを実行してみると・・・
f:id:HiroCom777:20210110130926j:plain
カラム情報の表示ばっちりです。カラム名表示の順番もちゃんと修正されています。