TableDef Object

Description

A TableDef object represents the stored definition of a base table or a linked table (Microsoft Jet workspaces only).

Remarks

You manipulate a table definition using a TableDef object and its methods and properties. For example, you can:

  • Examine the field and index structure of any local, linked, or external table in a database.
  • Use the Connect and SourceTableName properties to set or return information about linked tables, and use the RefreshLink method to update connections to linked tables.
  • Use the ValidationRule and ValidationText properties to set or return validation conditions.
  • Use the OpenRecordset method to create a table-, dynaset-, dynamic-, snapshot-, or forward-only-type Recordset object, based on the table definition.
For base tables, the RecordCount property contains the number of records in the specified database table. For linked tables, the RecordCount property setting is always – 1.

To create a new TableDef object, use the CreateTableDef method.

To add a field to a table

  1. Make sure any Recordset objects based on the table are all closed.
  2. Use the CreateField method to create a Field object variable and set its properties.
  3. Use the Append method to add the Field object to the Fields collection of the TableDef object.
You can delete a Field object from a TableDefs collection if it doesn't have any indexes assigned to it, but you will lose the field's data.

To create a table that is ready for new records in a database

  1. Use the CreateTableDef method to create a TableDef object.
  2. Set its properties.
  3. For each field in the table, use the CreateField method to create a Field object variable and set its properties.
  4. Use the Append method to add the fields to the Fields collection of the TableDef object.
  5. Use the Append method to add the new TableDef object to the TableDefs collection of the Database object.
A linked table is connected to the database by the SourceTableName and Connect properties of the TableDef object.

To link a table to a database

  1. Use the CreateTableDef method to create a TableDef object.
  2. Set its Connect and SourceTableName properties (and optionally, its Attributes property).
  3. Use the Append method to add it to the TableDefs collection of a Database.
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]

Properties

Attributes property, ConflictTable property, Connect property, DateCreated, LastUpdated properties, KeepLocal property, Name property, RecordCount property, Replicable property, ReplicaFilter property, SourceTableName property, Updatable property, ValidationRule property, ValidationText property.

Methods

CreateField method, CreateIndex method, CreateProperty method, OpenRecordset method, RefreshLink method.

See Also   CreateTableDef method.

Specifics (Microsoft Access)

In addition to the properties defined by the Microsoft Jet database engine, a TableDef object may also contain the following Microsoft Access application–defined properties. For details on reading and setting these properties, see the individual properties and the Property object.

  • DatasheetFontHeight
  • DatasheetFontItalic
  • DatasheetFontName
  • DatasheetFontUnderline
  • DatasheetFontWeight
  • Description
  • FrozenColumns
  • RowHeight
  • ShowGrid

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
Example (Microsoft Access)

The following example creates a new TableDef object, defines a field within it, and appends it to the TableDefs collection of the current database:

Sub NewTable()
    Dim dbs As Database, tdf As TableDef, fld As Field

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new TableDef object.
    Set tdf = dbs.CreateTableDef("Contacts")
    ' Create new Field object.
    Set fld = tdf.CreateField("ContactName", dbText, 30)
    ' Append new objects.
    tdf.Fields.Append fld
    dbs.TableDefs.Append tdf
    Set dbs = Nothing
End Sub