TableDefs Collection (DAO)

A TableDefs collection contains all stored TableDef objects in a database (Microsoft Access workspaces only).

Remarks

You manipulate a table definition using a TableDef object and its methods and properties.

The default collection of a Database object is the TableDefs collection.

To refer to a TableDef object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

TableDefs(0)

TableDefs("name")

TableDefs![name]

Links provided by: The UtterAccess community | About the Contributors

• Re-Linker Multi-Backends

• Swap/Relink Between LIVE, TEST and LOCAL Data

Example

This example creates a new TableDef object and appends it to the TableDefs collection of the Northwind Database object. It then enumerates the TableDefs collection and the Properties collection of the new TableDef.

Sub TableDefX() 
 
   Dim dbsNorthwind As Database 
   Dim tdfNew As TableDef 
   Dim tdfLoop As TableDef 
   Dim prpLoop As Property 
 
   Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
 
   ' Create new TableDef object, append Field objects  
   ' to its Fields collection, and append TableDef  
   ' object to the TableDefs collection of the  
   ' Database object. 
   Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef") 
   tdfNew.Fields.Append tdfNew.CreateField("Date", dbDate) 
   dbsNorthwind.TableDefs.Append tdfNew 
 
   With dbsNorthwind 
      Debug.Print .TableDefs.Count & _ 
         " TableDefs in " & .Name 
 
      ' Enumerate TableDefs collection. 
      For Each tdfLoop In .TableDefs 
         Debug.Print "  " & tdfLoop.Name 
      Next tdfLoop 
 
      With tdfNew 
         Debug.Print "Properties of " & .Name 
 
         ' Enumerate Properties collection of new 
         ' TableDef object, only printing properties 
         ' with non-empty values. 
         For Each prpLoop In .Properties 
            Debug.Print "  " & prpLoop.Name & " - " & _ 
               IIf(prpLoop = "", "[empty]", prpLoop) 
         Next prpLoop 
 
      End With 
 
      ' Delete new TableDef since this is a  
      ' demonstration. 
      .TableDefs.Delete tdfNew.Name 
      .Close 
   End With 
 
End Sub 

This example creates a new TableDef object in the Northwind database.

Sub CreateTableDefX() 
 
   Dim dbsNorthwind As Database 
   Dim tdfNew As TableDef 
   Dim prpLoop As Property 
 
   Set dbsNorthwind = OpenDatabase("Northwind.mdb") 
 
   ' Create a new TableDef object. 
   Set tdfNew = dbsNorthwind.CreateTableDef("Contacts") 
 
   With tdfNew 
      ' Create fields and append them to the new TableDef  
      ' object. This must be done before appending the  
      ' TableDef object to the TableDefs collection of the  
      ' Northwind database. 
      .Fields.Append .CreateField("FirstName", dbText) 
      .Fields.Append .CreateField("LastName", dbText) 
      .Fields.Append .CreateField("Phone", dbText) 
      .Fields.Append .CreateField("Notes", dbMemo) 
 
      Debug.Print "Properties of new TableDef object " & _ 
         "before appending to collection:" 
 
      ' Enumerate Properties collection of new TableDef  
      ' object. 
      For Each prpLoop In .Properties 
         On Error Resume Next 
         If prpLoop <> "" Then Debug.Print "  " & _ 
           prpLoop.Name & " = " & prpLoop 
         On Error GoTo 0 
      Next prpLoop 
 
      ' Append the new TableDef object to the Northwind  
      ' database. 
      dbsNorthwind.TableDefs.Append tdfNew 
 
      Debug.Print "Properties of new TableDef object " & _ 
         "after appending to collection:" 
 
      ' Enumerate Properties collection of new TableDef  
      ' object. 
      For Each prpLoop In .Properties 
         On Error Resume Next 
         If prpLoop <> "" Then Debug.Print "  " & _ 
           prpLoop.Name & " = " & prpLoop 
         On Error GoTo 0 
      Next prpLoop 
 
   End With 
 
   ' Delete new TableDef object since this is a  
   ' demonstration. 
   dbsNorthwind.TableDefs.Delete "Contacts" 
 
   dbsNorthwind.Close 
 

About the Contributors

UtterAccess is the premier Microsoft Access wiki and help forum. Click here to join.