Data Definition Query No-Go with Linked Tables (1 Viewer)

Sub PrimaryX()

   Dim dbsNorthwind As Database
   Dim tdfNew As TableDef
   Dim idxNew As Index
   Dim idxLoop As Index
   Dim fldLoop As Field
   Dim prpLoop As Property

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   ' Create and append a new TableDef object to the
   ' TableDefs collection of the Northwind database.
   Set tdfNew = dbsNorthwind.CreateTableDef("NewTable")
   tdfNew.Fields.Append tdfNew.CreateField("NumField", _
      dbLong, 20)
   tdfNew.Fields.Append tdfNew.CreateField("TextField", _
      dbText, 20)
   dbsNorthwind.TableDefs.Append tdfNew

   With tdfNew
      ' Create and append a new Index object to the
      ' Indexes collection of the new TableDef object.
      Set idxNew = .CreateIndex("NumIndex")
      idxNew.Fields.Append idxNew.CreateField("NumField")
      idxNew.Primary = True
      .Indexes.Append idxNew
      Set idxNew = .CreateIndex("TextIndex")
      idxNew.Fields.Append idxNew.CreateField("TextField")
      .Indexes.Append idxNew

      Debug.Print .Indexes.Count & " Indexes in " & _
         .Name & " TableDef"

      ' Enumerate Indexes collection.
      For Each idxLoop In .Indexes

         With idxLoop
            Debug.Print "  " & .Name

            ' Enumerate Fields collection of each Index
            ' object.
            Debug.Print "    Fields"
            For Each fldLoop In .Fields
               Debug.Print "    " & fldLoop.Name
            Next fldLoop

            ' Enumerate Properties collection of each
            ' Index object.
            Debug.Print "    Properties"
            For Each prpLoop In .Properties
               Debug.Print "    " & prpLoop.Name & _
                  " = " & IIf(prpLoop = "", "[empty]", _
                  prpLoop)
            Next prpLoop
         End With

      Next idxLoop

   End With

   dbsNorthwind.TableDefs.Delete tdfNew.Name
   dbsNorthwind.Close

End Sub