TableDefs Collection

See AlsovbobjTableDefsCollectionSee              PropertiesvbobjTableDefsCollectionProp           MethodsvbobjTableDefsCollectionMth

Description

A collection of TableDef objects.  A TableDef collection can be found in a Database object.

Remarks

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

Print Data1.Database.TableDefs("Publishers").Fields("PubID").Type
Print Data1.Database("Publishers")("PubID").Type

The Name property of a TableDef object is not the same as the name of an object variableZ3R9Q5 to which it is assigned.  Unlike forms and controls, the Name property of a TableDef object is available at run time.

Professional Edition

In the Professional Edition, you can create an object variable of type TableDefs (a collection) as well as type TableDef.  For example, you could use this code to print the field data type:

Dim MyDb As Database, MyTableDef As TableDef
Set MyDb = OpenDatabase("BIBLIO.MDB")
Set MyTableDef = MyDb("Publishers")
Print MyTableDef("PubID").Type

To make changes in the structure of a table, such as adding a Field

  1. Make sure the Table is closed by all users.

  2. Create a New Field object variable and set its properties.

  3. Add the Field to the Fields collection of the TableDef with the Append method.

In cases where the data is stored in another database, the table is said to be "attached" to the database.  Attached tables are stored in external databases or are tables not in the Microsoft Access formatthey are linked to the database by way of the SourceTableName and Connect properties of the TableDef object.  Attached tables behave as if their data is stored as part of the database.

To attach a table to a database

  1. Dim a new TableDef object.

  2. Set its Connect and SourceTableName properties (and optionally, its Attributes property).

  3. Add it to the TableDefs collection of a Database with the Append method.

You cannot delete a Field from a TableDefs collection.