キーを設定してみる(ExcelでRDBその7)

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

キーの設定

　キーというものがおぼろげながら分かってきました。今回はADOXでAccessファイルにキーを設定してみようと思います。使用するデータベースファイルを以下に置きます。

名簿.accdb - Google ドライブ

上記ファイルをダウンロードして配置した場所に、ADO,ADOXを参照設定で有効にしたExcelファイルを作成します。ADOXでキーを設定するにはKeyオブジェクトを作成して、テーブルに追加する形を取ります。以下に設定例を示しますので、以下を標準モジュールに入力して実行してみてください。データベースファイルのテーブル"Table_1"のフィールド"ID"に主キーが設定されます。

Public Sub AddKey()
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=" & ThisWorkbook.Path & "\名簿.accdb"
  catalogObject.ActiveConnection = connectString
  'テーブル選択
  Set tableObject = catalogObject.Tables("Table_1")
  'キーの設定
  keyObject.Name = "KEY_1"
  keyObject.Type = adKeyPrimary
  keyObject.Columns.Append "ID"
  'キーの追加
  tableObject.Keys.Append keyObject
End Sub

コードの解説です。Keyオブジェクトを作成したら、Keyオブジェクト.Nameでオブジェクト名を指定します。次に、Keyオブジェクト.Typeでキーの種類を指定します。キーの種類は以下の3種類です。

・adKeyPrimary
　主キーを設定します。1が割り当てられています。
・adKeyUnique
　一意のキーを設定します。2が割り当てられています。（主キーもそうですが）このキーが設定されたフィールドには異なるレコードで同じ値を設定できなくなります。
・adKeyForeign
　外部キーを設定します。3が割り当てられています。外部キーは自分以外のテーブルとの関連性（リレーションシップ）を持たせる場合に使用します。この設定を選択した場合、関連性を持たせるテーブルを指定しなければなりません。これは、別の機会に説明します。

設定が出来ているのか確認する

さて、Accessがあれば主キーが設定されているのが簡単にわかるのですが、持っておりませんので以下のプロシージャを入力して実行してみます。テーブルに設定してあるキーの設定をイミディエイトウィンドウに表示します。

Public Sub CheckKey()
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=" & ThisWorkbook.Path & "\名簿.accdb"
  catalogObject.ActiveConnection = connectString
  'テーブル選択
  Set tableObject = catalogObject.Tables("Table_1")
  'キーの確認
  For Each keyObject In tableObject.Keys
    Debug.Print keyObject.Name
    Debug.Print keyObject.Type
    Debug.Print keyObject.Columns(0).Name
  Next
End Sub

イミディエイトウインドウには、以下のように出ました。

KEY_1 ⇒　キーオブジェクトの名前
1 ⇒　キーの種類（1:adKeyPrimary　主キー）
ID ⇒　フィールド名

設定はできているようですね。ちなみに主キーを設定する前のファイルでは何もかえってきませんでした。また、同じデータが複数のレコードで登録してあるフィールドは主キー設定できません。（エラーになります）

主キーが機能しているのか確認する

で、設定した主キーは機能しているのでしょうか？主キーが機能していれば、主キーのフィールドに既に登録されているデータは追加できないはずです。以下のプロシージャでデータを追加してみましょう。データベースに登録済のデータをさらに追加登録するプロシージャです。

Public Sub AddRecord()
Dim connectString As String
Dim fieldList() As Variant
Dim recordData() As Variant
Dim adoCn As Object
Set adoCn = CreateObject("ADODB.Connection")
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")
  '追加データ
  fieldList() = Array("ID", "名前", "性別", "血液型", "生年月日", "備考")
  recordData() = Array(1, "大山洋子", "女性", "A", "2001/3/6", "カラオケ大好き")
  'データベース接続
  connectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\名簿.accdb"
  adoCn.ConnectionString = connectString
  adoCn.Open
  'テーブル指定してレコード追加
  adoRs.Open "Table_1", adoCn, adOpenKeyset, adLockOptimistic
  adoRs.AddNew fieldList, recordData
  adoRs.Update
  '終了
  adoRs.Close
  adoCn.Close
End Sub

ちなみに主キーを設定する前のファイルでは実行できましたが、主キーを設定した後ではエラーとなって登録できません。recordData() で登録してある最初のデータ（主キーの"ID"にあたる）をデータベースファイルに登録していない数値（10など）に変えると実行できました。ちゃんと機能している様です。