TableDef Object

Description

A TableDef object represents the stored definition of a base table or an attached table.

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, attached, or external table in a database.
Read or write the Connect and SourceTableName property settings for attached tables, and use the RefreshLink method to update connections to attached tables.
Read (or write, if the Updatable property is set to True (-1)) the ValidationRule and ValidationText property settings to find (or specify) validation conditions.
Use the OpenRecordset method to create a table-, dynaset-, or snapshot-type Recordset object, based on the table definition.

The default collection of a Database object is the TableDefs collection, and the default collection of a TableDef object is the Fields collection. You can simplify your code by using these defaults. For example, the following statements are identical in that they both print the number corresponding to the field data type of a Field object in a TableDef object:

Debug.Print dbsCurrent.TableDefs("Publishers").Fields("PubID").Type
Debug.Print dbsCurrent("Publishers")("PubID").Type

The Name property of a TableDef object isn't necessarily the same as the name of an object variable to which it's assigned.

For base tables, the RecordCount property contains the number of records in the specified database table. For attached tables, the RecordCount property setting is always -1.

You refer to a TableDef object that you create and append to a TableDefs collection by its Name property setting using this syntax:

TableDefs("name")

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

You can delete a Field object from a TableDefs collection if it doesn't have any indexes assigned to it, but its underlying data is lost.

Properties

Attributes Property; ConflictTable Property; Connect Property; DateCreated, LastUpdated Properties; KeepLocal Property; Name Property; RecordCount Property; Replicable Property; SourceTableName Property; Updatable Property; ValidationRule Property; ValidationText Property.

Methods

CreateField Method, CreateIndex Method, CreateProperty Method, OpenRecordset Method, RefreshLink Method.

See Also

CreateTableDef Method; Appendix, "Data Access Object Hierarchy."

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.

DatasheetFontHeight	Description
DatasheetFontItalic	FrozenColumns
DatasheetFontName	RowHeight
DatasheetFontUnderline	ShowGrid
DatasheetFontWeight

Example

This example creates a new TableDef object, adds a Field object to it, and appends the TableDef to the TableDefs collection in the current database. Then the example enumerates all the TableDef objects in the current database and all the properties of the new TableDef.

Function EnumerateTableDef () As Integer
    Dim dbsExample As Database
    Dim tdfEnum As TableDef
    Dim fldDate As Field
    Dim I As Integer
    Set dbsExample = _ 
        DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
    Set tdfEnum = dbsExample.CreateTableDef("MyTable") 
    Set fldDate = tdfEnum.CreateField("Date", dbDate)
    tdfEnum.Fields.Append fldDate
    dbsExample.TableDefs.Append tdfEnum
    ' Get database name.
    Debug.Print "Database Name: "; dbsExample.Name

    ' Enumerate all fields in tdfEnum.
    Debug.Print "TableDef: Name; Field: Name"
    For I = 0 To tdfEnum.Fields.Count - 1
        Debug.Print "  "; tdfEnum.Name;
        Debug.Print "; "; tdfEnum.Fields(I).Name
    Next I
    Debug.Print
    ' Enumerate all indexes in tdfEnum.
    Debug.Print "TableDef: Name; Index: Name"
    For I = 0 To tdfEnum.Indexes.Count - 1
        Debug.Print "  "; tdfEnum.Name;
        Debug.Print "; "; tdfEnum.Indexes(I).Name
    Next I
    Debug.Print
    ' Enumerate built-in properties of tdfEnum.
    Debug.Print "tdfEnum.Name: "; tdfEnum.Name
    Debug.Print "tdfEnum.Attributes: "; tdfEnum.Attributes
    Debug.Print "tdfEnum.Connect: "; tdfEnum.Connect
    Debug.Print "tdfEnum.DateCreated: "; tdfEnum.DateCreated
    Debug.Print "tdfEnum.LastUpdated: "; tdfEnum.LastUpdated
    Debug.Print "tdfEnum.RecordCount: "; tdfEnum.RecordCount
    Debug.Print "tdfEnum.SourceTableName: "; tdfEnum.SourceTableName
    Debug.Print "tdfEnum.Updatable: "; tdfEnum.Updatable
    Debug.Print "tdfEnum.ValidationRule: "; tdfEnum.ValidationRule
    Debug.Print "tdfEnum.ValidationText: "; tdfEnum.ValidationText
    EnumerateTableDef = True
End Function

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 Database object pointing to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.CreateTableDef("Contacts")
    Set fld = tdf.CreateField("ContactName", dbText, 30)
    tdf.Fields.Append fld
    dbs.TableDefs.Append tdf
End Sub