Excel VBAを使ってデータベースのテーブルリレーションシップを確認する方法

Excel VBAを使ってデータベースのテーブルリレーションシップを確認する方法は「ADO OpenSchemaのadSchemaForeignKeysでPK/FK table・column・constraintを取得し、provider未対応ならDB固有catalog viewをread-onlyで使います。Jet 4.0固定やMSysRelationships直query、text fileの平文接続文字列は避けます。」と理解すると迷いません。providerがforeign key schema rowsetをsupportする場合にread-only metadataを取得し、Access固有system table直queryを避ける点が適用条件です。 確認ポイント：ADOのschema restrictionはprovider依存なので、read-only接続で返る列を確認してから絞り込みます。

providerと接続権限をmetadata取得用に限定する

外部キー関係を確認するときは、接続先データベースとスキーマを明示し、ADOのOpenSchemaが返す列名をProviderごとに先頭数行で確認します。主キー表・列、外部キー表・列、制約名を組で出力し、同名テーブルを別スキーマと混同しません。取得はメタデータ参照だけに限定します。

• DB製品、provider、bitness、schema supportを確認する
• read-only metadata権限と対象catalog/schemaを限定する
• connection secretの保管方法を確認する
• 出力sheet既存dataとcolumn headerを確認する

OpenSchemaのrowsetを列名付きで保持する

OpenSchema supportと返すcolumnはprovider依存です。relationship metadataが0件でもforeign keyなし、権限不足、別schema、provider非対応を区別します。AccessのRelationship、SQL Server catalog、PostgreSQL information_schemaでは名称が違います。結果にはcatalog/schema/table/constraint/column序数を残します。

restriction配列をprovider仕様なしで決め打ちしない

• Jet OLEDB 4.0を全DBへ使う
• OpenSchema 20を説明なしで固定する
• MSysRelationshipsへ直接依存する
• headerなしで出力する
• connection secretをconfig.txtへ置く

全外部キーと単一table抽出を使い分ける

ADO connectionをread-only modeで開く

Set cn=CreateObject("ADODB.Connection")
cn.ConnectionString=GetApprovedConnectionString()
cn.Mode=adModeRead
cn.Open

GetApprovedConnectionStringは組織のsecret storeから取得し、cell/text fileへ置きません。

adSchemaForeignKeysを取得する

Set rs=cn.OpenSchema(adSchemaForeignKeys)
Debug.Print rs.Fields.Count

20というmagic numberではなくADO constantを参照できる開発環境を推奨します。

schema列を先にRelations sheetへ出力する

Dim i As Long
For i=0 To rs.Fields.Count-1
  ThisWorkbook.Worksheets("Relations").Cells(1,i+1).Value2=rs.Fields(i).Name
Next i
ThisWorkbook.Worksheets("Relations").Range("A2").CopyFromRecordset rs

headerなしのCopyFromRecordsetを避けます。

FK table名でrestrictionを適用する

' OpenSchemaのCriteria配列はproviderごとのrestriction位置を公式schemaで確認して設定する
' 未確認の配列indexを決め打ちしない

provider差を明示します。

RecordsetとConnectionを必ず閉じる

On Error GoTo CleanFail
' query
CleanExit:
  If Not rs Is Nothing Then If rs.State<>0 Then rs.Close
  If Not cn Is Nothing Then If cn.State<>0 Then cn.Close
  Exit Sub
CleanFail:
  Debug.Print Err.Number,Err.Description
  Resume CleanExit

error時もconnectionを閉じます。

schema出力先だけを作業copyとして扱う

metadata read-only accountを使い、system tableへの直接UPDATEやDDLを行いません。connection stringをtext fileやworkbookへ平文保存しません。出力には内部table名が含まれるためaccess制御します。relationship変更はmigration、backup、transaction、rollbackを備えたDB管理作業へ分離します。

0件とmetadata取得errorを分けて照合する

DB管理toolのrelationship diagram/catalog queryとconstraint名・columnsを照合します。PK/FK複合key、cascade rule、別schema、0件、権限不足をtestし、出力件数とunique constraint数を記録します。

relationship取得の合格基準

connection stringは引数で受け、Connection.Mode=adModeReadで開きます。adSchemaForeignKeys=27のschema rowsetを取得し、providerが対応する場合だけ6番目のrestrictionへFK table名を渡してRelations sheetへheader付きで出力します。

foreign key metadataを出力する完成コード

Option Explicit

Public Sub ExportForeignKeys(ByVal connectionString As String, Optional ByVal fkTable As String = "")
  Const adModeRead As Long = 1, adSchemaForeignKeys As Long = 27
  Dim cn As Object, rs As Object, ws As Worksheet
  Dim criteria As Variant, i As Long, rowCount As Long
  On Error GoTo CleanFail

  Set cn = CreateObject("ADODB.Connection")
  cn.Mode = adModeRead
  cn.Open connectionString
  If Len(fkTable) = 0 Then
    Set rs = cn.OpenSchema(adSchemaForeignKeys)
  Else
    criteria = Array(Empty, Empty, Empty, Empty, Empty, fkTable)
    Set rs = cn.OpenSchema(adSchemaForeignKeys, criteria)
  End If

  Set ws = ThisWorkbook.Worksheets("Relations")
  ws.Cells.ClearContents
  For i = 0 To rs.Fields.Count - 1
    ws.Cells(1, i + 1).Value2 = rs.Fields(i).Name
  Next i
  If Not rs.EOF Then
    rowCount = ws.Range("A2").CopyFromRecordset(rs)
  End If
  Debug.Print "foreignKeyRows=" & rowCount, "columns=" & rs.Fields.Count, "filter=" & fkTable

CleanExit:
  On Error Resume Next
  If Not rs Is Nothing Then If rs.State <> 0 Then rs.Close
  If Not cn Is Nothing Then If cn.State <> 0 Then cn.Close
  Set rs = Nothing: Set cn = Nothing
  Exit Sub
CleanFail:
  Dim e As Object
  Debug.Print Err.Number, Err.Description
  If Not cn Is Nothing Then For Each e In cn.Errors: Debug.Print e.Number, e.Description: Next e
  Resume CleanExit
End Sub

このcodeは画面表示だけを見るための例ではありません。通常系では「schema rowsetのcolumn名とforeign key rowsをRelationsへ出し、PK/FK table/columnを確認できる」を確認し、0件と実行errorを別の結果として保存します。

全件・絞込0件・provider errorを分類する

• 期待どおり：schema rowsetのcolumn名とforeign key rowsをRelationsへ出し、PK/FK table/columnを確認できる
• 0件・非適用：rowset 0件はNoForeignKeysとしてheaderだけを残す
• 実行error：provider unsupported、restriction不対応、auth errorはADO ErrorsとErr.Numberを記録して0件から分ける

adSchemaForeignKeysを未定義constantのまま使わずlate binding値27を明示します。providerごとのrestriction対応を確認し、配列indexを推測しません。connection stringやcatalog名のsecretをsheetへ出しません。

table名なし・既知名・存在しない名を試す

' fkTable=""で全件、既知table名でrestriction、存在しないtableで0件を実行し、
' 列schemaと件数、ADO Errors collectionを比較する。

FKあり/なし、複合key、restriction対応/非対応、auth errorをtestします。provider/version、catalog、rowset列名、件数、connection mode、error detailsを保存します。

Excel VBAを使ってデータベースのテーブルリレーションシップを確認する方法の証跡には、実行対象と取得時刻に加え、通常・0件・errorのどれへ分類したかを残します。通常系は「schema rowsetのcolumn名とforeign key rowsをRelationsへ出し、PK/FK table/columnを確認できる」、停止系は「provider unsupported、restriction不対応、auth errorはADO ErrorsとErr.Numberを記録して0件から分ける」を判断文としてそのまま作業票へ写し、担当者ごとの言い換えで意味が変わらないようにします。

relationship metadataの採取を定期化するときは、provider名とversionごとに対応するOpenSchema restrictionを記録し、非対応parameterを空結果として扱いません。複合foreign keyはconstraint名とordinalを保ったまま一組へまとめ、parent/child列の順序を崩さず出力します。authorization errorは「relationshipなし」と分け、read-only connectionを閉じたことまでrun結果へ含めます。

公式情報・参考資料

• Microsoft Learn – SchemaEnum
• Microsoft Learn – CopyFromRecordset