title: MS Access DDL: Show Foreign Key References in Relationship View
body: <p>Using an ADODB connection object, I am able to use SQL DDL with MS Access (joy!). The odd thing is that sometimes declared foreign key references show up in Access' Relationship View - this would be great for visualization, and to print off to show stakeholders - but sometimes it doesn't. For example, I create an Employees table, and a Dep_Policy table with a foreign key reference to Employees (example from Ramakrishnan book). This shows in the relationship view. I now create two new tables using the same DDL, but change the names to Cat and Dog, as a test. Only Dog shows in the Relationship view, not Cat. Here's the code:</p>
  
  <pre><code>Sub createTestSchema()
  
  Dim cnn1 As ADODB.Connection
  Dim cmd1 As ADODB.Command
  
  Set cnn1 = CurrentProject.Connection
  Set cmd1 = New ADODB.Command
  
  Dim sqlArr As ArrayList
  Set sqlArr = New ArrayList
  sqlArr.Add ("CREATE TABLE Employees(ssn integer identity(0,1), name text(100), lot     text(50), primary key (ssn))")
  sqlArr.Add ("CREATE TABLE Dep_Policy(pname text(20), age integer, cost currency, ssn integer, primary key (pname, ssn)," &amp; _
              "FOREIGN KEY (ssn) references Employees(ssn) ON DELETE CASCADE)")
  sqlArr.Add ("CREATE TABLE Cat(ssn integer identity(0,1), name text(100), lot text(50), primary key (ssn))")
  sqlArr.Add ("CREATE TABLE Dog(pname text(20), age integer, cost currency, ssn integer, primary key (pname, ssn)," &amp; _
              "FOREIGN KEY (ssn) references Cat(ssn) ON DELETE CASCADE)")
  With cmd1
      .ActiveConnection = cnn1
      .CommandType = adCmdText
      Dim i As Integer
      For i = 0 To sqlArr.size - 1
          .CommandText = sqlArr.GetItem(i)
          .Execute
      Next
  End With
  End Sub
  </code></pre>
  
  <p>How do I ensure that the foreign key references show up in the Relationship view? For this test schema, most tables appear but in my actual schema few appear. Using MS Access is a client requirement. (btw ArrayList is a custom class)</p>