関連性をどうしよう？(ExcelでRDBその8)

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

関連性を設定するには

　前回は、データベースのテーブルに主キーを設定してみました。でも、それだけではあまり役に立ちそうにありません。今回は他のテーブルと関連性を持たせてみましょう。関連性をもたせるわけですから、まずは2つのテーブルを持つファイルを作ります。テーブルの内容は、以下の様にします。

テーブル：売上明細　商品売り上げの記録をとる
　ID　　整数型　売上明細テーブルの主キーとして使用
　日時　日付型　売上が上がった日付を記録
　商品ID　整数型　売れた商品を示す外部キー。商品データテーブルの商品IDと関連する。
　数量　整数型　商品の売上数量を記録

テーブル：商品データ　商品に関するデータを登録する
　商品ID　整数型　商品データテーブルの主キーとして使用
　商品名　文字列型　商品名を登録
　単価　　整数型　商品の単価を登録

今回使用するデータベースファイルを以下に置きます。

売上.accdb - Google ドライブ

　売上明細のテーブルには毎日の売り上げデータが記録されていきます。データの中には当然どの商品が売れたかというデータが必要になるのですが、このテーブルでは商品IDを入力するのみとなっています。商品IDが示す内容の詳細は、別途商品データテーブルで管理されています。この状態を二つのテーブルは関連性を持つと呼びます。まずは、テーブルを作っただけで2つのテーブルの繋がりを見たいと思います。でも、Accessは持っていない人が多いですよね（僕も持っていません）。ところが、今どきのExcel（僕の環境だとOffice2019）だとPowerPivotを使用するとダイアグラム図を確認することができます。PowerPivotの導入については以下を参照してください。

support.microsoft.com

では、テーブルの繋がりを確認したいと思います。Excelを開いたらメニューのPowerPivotを選択してリボンの管理を選択します。新しいウィンドウが開きますので、リボンの外部データの取り込み⇒データAccessからを選択します。テーブルのインポートウィザードの画面で参照から作成したデータベースファイルを選択してください。完了したら『次へ』を2回押してテーブルビューの選択画面になります。『基になるテーブル』にチェックを入れて『完了』ボタンを押してください。閉じるを押すと完了です。リボンのダイアグラムビューを選択してみましょう。すると・・・

2つのテーブルが表示されました。でも、それだけですね。このファイルの2つのテーブルの繋がりを明確にしていきたいと思います。

Accessファイルがある場所に、ADO,ADOXを参照設定で有効にしたExcelファイルを作成して、以下を標準モジュールに入力してください。入力が完了したらMakeRelationを実行してみましょう。

’関連性を設定します
Sub MakeRelation()
Dim fileName As String

  'ファイル名
  fileName = ThisWorkbook.Path & "\売上.accdb"

  '商品データテーブルKEY設定
  AddKey fileName, "商品データ", "KEY商品", "商品ID", adKeyPrimary
  
  '売上明細テーブルKEY設定
  AddKey fileName, "売上明細", "KEY売上", "ID", adKeyPrimary
  AddKey fileName, "売上明細", "KEY商品", "商品ID", adKeyForeign, "商品データ", "商品ID"

End Sub

’キーを設定します
Sub AddKey(fileName As String, tableName As String, keyName As String, fieldName As String, keyType As Integer, _
  Optional targetTable As String, Optional targetColumn As String)
Dim connectString As String
Dim catalogObject As Object
Dim tableObject As Object
Dim keyObject As Object
Set catalogObject = CreateObject("ADOX.Catalog")
Set keyObject = New ADOX.Key
  'データベース接続
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
  catalogObject.ActiveConnection = connectString
  'テーブル選択
  Set tableObject = catalogObject.Tables(tableName)
  'キーの設定
  keyObject.Name = keyName
  keyObject.Type = keyType
  keyObject.Columns.Append fieldName
  If keyType = adKeyForeign Then
    keyObject.RelatedTable = targetTable
    keyObject.Columns(keyName).RelatedColumn = targetColumn
  End If
  'キーの追加
  tableObject.Keys.Append keyObject
End Sub

プログラムの解説です。片方のテーブルに外部キーを設定してもう一つの主キーを指定することによってする必要があります。今回は売上明細テーブルの商品IDを外部キーとします。外部キーを設定するには、Keyオブジェクト.Typeでキーの種類をadKeyForeignとして、その他に以下を指定します。

・関連先のテーブル名
　Keyオブジェクト.RelatedTableで指定します。
・関連先のテーブルの関連先フィールド名
　Keyオブジェクト.Columns(外部キーにするフィールド名).RelatedColumnで指定します。

さて、繋がりを確認してみましょう。上と同じ手順でファイルを読み込んでダイアグラムビューを選択してみると・・・