Append Method

Applies To

Fields collection, Groups collection, Indexes collection, Properties collection, QueryDefs collection, Relations collection, TableDefs collection, Users collection, Workspaces collection.

Description

Adds a new DAO object to a collection.

Syntax

collection.Append object

The Append method syntax has these parts.

Part	Description

collection	An object variable that represents any collection that can accept new objects (for limitations, see the table at the end of this topic).
object	An object variable that represents the object being appended, which must be of the same type as the elements of collection.

Remarks

You can use the Append method to add a new table to a database, add a field to a table, and add a field to an index.

The appended object becomes a persistent object, stored on disk, until you delete it by using the Delete method. If collection is a Workspaces collection (which is stored only in memory), the object is active until you remove it by using the Close method.

The addition of a new object occurs immediately, but you should use the Refresh method on any other collections that may be affected by changes to the database structure.

If the object you're appending isn't complete (such as when you haven't appended any Field objects to a Fields collection of an Index object before it's appended to an Indexes collection) or if the properties set in one or more subordinate objects are incorrect, using the Append method causes an error. For example, if you haven't specified a field type and then try to append the Field object to the Fields collection in a TableDef object, using the Append method triggers a run-time error.

The following table lists some limitations of the Append method. The object in the first column is an object containing the collection in the second column. The third column indicates whether you can append an object to that collection (for example, you can never append a Container object to the Containers collection of a Database object).

Object	Collection	Can you append new objects?

DBEngine	Workspaces	Yes.
DBEngine	Errors	No. New Error objects are automatically appended when they occur.
Workspace	Connections	No. Using the OpenConnection method automatically appends new objects.
Workspace	Databases	No. Using the OpenDatabase method automatically appends new objects.
Workspace	Groups	Yes.
Workspace	Users	Yes.
Connection	QueryDefs	No. Using the CreateQueryDef method automatically appends new objects.
Connection	Recordsets	No. Using the OpenRecordset method automatically appends new objects.
Database	Containers	No.
Database	QueryDefs	Only when the QueryDef object is a new, unappended object created with no name. See the CreateQueryDef method for details.
Database	Recordsets	No. Using the OpenRecordset method automatically appends new objects.
Database	Relations	Yes.
Database	TableDefs	Yes.
Group	Users	Yes.
User	Groups	Yes.
Container	Documents	No.
QueryDef	Fields	No.
QueryDef	Parameters	No.
Recordset	Fields	No.
Relation	Fields	Yes .
TableDef	Fields	Only when the Updatable property of the TableDef object is set to True, or when the TableDef object is unappended.
TableDef	Indexes	Only when the Updatable property of the TableDef is set to True, or when the TableDef object is unappended.
Index	Fields	Only when the Index object is a new, unappended object.
Database, Field, Index, QueryDef, TableDef	Properties	Only when the Database, Field, Index, QueryDef, or TableDef object is in a Microsoft Jet workspace.
DBEngine, Parameter, Recordset, Workspace	Properties	No.

See Also

Delete method, GetChunk method, Refresh method, Type property.

Example

This example uses either the Append method or the Delete method to modify the Fields collection of a TableDef. The AppendDeleteField procedure is required for this procedure to run.

Sub AppendX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim fldLoop As Field

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set tdfEmployees = dbsNorthwind.TableDefs!Employees

    ' Add three new fields.
    AppendDeleteField tdfEmployees, "APPEND", _
        "E-mail", dbText, 50
    AppendDeleteField tdfEmployees, "APPEND", _
        "Http", dbText, 80
    AppendDeleteField tdfEmployees, "APPEND", _
        "Quota", dbInteger, 5

    Debug.Print "Fields after Append"
    Debug.Print , "Type", "Size", "Name"

    ' Enumerate the Fields collection to show the new fields.
    For Each fldLoop In tdfEmployees.Fields
        Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
    Next fldLoop

    ' Delete the newly added fields.
    AppendDeleteField tdfEmployees, "DELETE", "E-mail"
    AppendDeleteField tdfEmployees, "DELETE", "Http"
    AppendDeleteField tdfEmployees, "DELETE", "Quota"

    Debug.Print "Fields after Delete"
    Debug.Print , "Type", "Size", "Name"

    ' Enumerate the Fields collection to show that the new
    ' fields have been deleted.
    For Each fldLoop In tdfEmployees.Fields
        Debug.Print , fldLoop.Type, fldLoop.Size, fldLoop.Name
    Next fldLoop

    dbsNorthwind.Close

End Sub

Sub AppendDeleteField(tdfTemp As TableDef, _
    strCommand As String, strName As String, _
    Optional varType, Optional varSize)

    With tdfTemp

        ' Check first to see if the TableDef object is
        ' updatable. If it isn't, control is passed back to
        ' the calling procedure.
        If .Updatable = False Then
            MsgBox "TableDef not Updatable! " & _
                "Unable to complete task."
            Exit Sub
        End If

        ' Depending on the passed data, append or delete a
        ' field to the Fields collection of the specified
        ' TableDef object.
        If strCommand = "APPEND" Then
            .Fields.Append .CreateField(strName, varType, varSize)
        Else
            If strCommand = "DELETE" Then .Fields.Delete _
                strName
        End If

    End With

End Sub

Example (Microsoft Access)

The following example defines a new Field object and appends it to the Fields collection of a TableDef object:

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

    ' Return reference to current database.
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!Employees
    ' Create new field in Employees table.
    Set fld = tdf.CreateField("SocialSecurity#", dbText, 11)
    ' Append field and refresh collection.
    tdf.Fields.Append fld
    tdf.Fields.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