CreateTableDef Method

Applies To Database object.

Description

Creates a new TableDef object (Microsoft Jet workspaces only).

Syntax

Set tabledef = database.CreateTableDef(name, attributes, source, connect)

The CreateTableDef method syntax has these parts.

Part	Description

tabledef	An object variable that represents the TableDef object you want to create.
database	An object variable that represents the Database object you want to use to create the new TableDef object.
name	Optional. A Variant (String subtype) that uniquely names the new TableDef object. See the Name property for details on valid TableDef names.
attributes	Optional. A constant or combination of constants that indicates one or more characteristics of the new TableDef object. See the Attributes property for more information.

(continued)

Part	Description

source	Optional. A Variant (String subtype) containing the name of a table in an external database that is the original source of the data. The source string becomes the SourceTableName property setting of the new TableDef object.
connect	Optional. A Variant (String subtype) containing information about the source of an open database, a database used in a pass-through query, or a linked table. See the Connect property for more information about valid connection strings.

Remarks If you omit one or more of the optional parts when you use the CreateTableDef method, you can use an appropriate assignment statement to set or reset the corresponding property before you append the new object to a collection. After you append the object, you can alter some but not all of its properties. See the individual property topics for more details.

If name refers to an object that is already a member of the collection, or you specify an invalid property in the TableDef or Field object you're appending, a run-time error occurs when you use the Append method. Also, you can't append a TableDef object to the TableDefs collection until you define at least one Field for the TableDef object.

To remove a TableDef object from the TableDefs collection, use the Delete method on the collection.

See Also Append method, Attributes property, Connect property, Delete method, Name property, TableDef object.

Example

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

End Sub

Example (Microsoft Access)

The following example creates a new TableDef object 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
    ' Return TableDef object variable that points to new table.
    Set tdf = dbs.CreateTableDef("Contacts")
    ' Define new field in table.
    Set fld = tdf.CreateField("ContactName", dbText, 40)
    ' Append Field object to Fields collection of TableDef object.
    tdf.Fields.Append fld
    tdf.Fields.Refresh
    ' Append TableDef object to TableDefs collection of database.
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
    Set dbs = Nothing
End Sub

Example (Microsoft Excel)

This example creates a new database, Nwindex.mdb. The example attaches two tables from the C:\Program Files\Common Files\Microsoft Shared\MSquery folder to the database. (In Windows NT, the two tables are located in the C:\Windows\Msapps\Msquery folder.)

Dim nWindEx As Database, customerTable As TableDef, supplierTable _
    As TableDef
Dim dataSource As String
dataSource = _
"dBASE IV;DATABASE=C:\Program Files\Common Files\Microsoft Shared\MSquery"
appPath = Application.Path
Set nWindEx = Workspaces(0).CreateDatabase(Application.Path _
    & "\NWINDEX.MDB", dbLangGeneral)
Set customerTable = nWindEx.CreateTableDef("Customer")
customerTable.Connect = dataSource
customerTable.SourceTableName = "Customer"
nWindEx.TableDefs.Append customerTable
Set supplierTable = nWindEx.CreateTableDef("Supplier")
supplierTable.Connect = dataSource
supplierTable.SourceTableName = "Supplier"
nWindEx.TableDefs.Append supplierTable
MsgBox "The database " & nWindEx.Name & " has been created."
nWindEx.Close